Oracle 常用脚本

--查看所有表空间文件信息

select * FROM dba_data_files;

--查看指定表空间的所有

select table_name from all_tables where TABLESPACE_NAME='USERS'

--创建DBLink

USING '(DESCRIPTION =  
     (ADDRESS_LIST =  
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.19.4.200)(PORT = 1521))  
     )  
     (CONNECT_DATA =  
(SERVICE_NAME = ORCL)  
     )  
)' 

--查看已有的DBLink

select * from dba_db_links;

--查看当前已有的工作空间的物理存放路径

select file_name , tablespace_name from dba_data_files;

--建立日志工作空间

create tablespace MATERIALIZED_LOG
logging
datafile 'C:\ORACLE\ORADATA\ORCL\MATERIALIZED_LOG.DBF'
size 100M
autoextend on
next 50M maxsize 5120M
extent management local;

--创建物化视图的日志

create materialized view log on CNTR_INFO
tablespace MATERIALIZED_LOG -- 日志空间
with primary key;

-- 授予用户对该表空间的UNLIMITED配额
ALTER USER GSPOS_SHJJHY QUOTA UNLIMITED ON MATERIALIZED_LOG;

--删除物化视图的日志
drop materialized view log on CNTR_INFO

--查看物化视图的日志

SELECT MASTER, LOG_TABLE FROM USER_MVIEW_LOGS;

-- 查看系统注册的所有物化视图详细信息

select * from USER_REGISTERED_MVIEWS where name='CNTR_STATUS';

-- 查看指定数据库表被依赖的物化视图，用于检查视图日志是否自动删除

select * from DBA_BASE_TABLE_MVIEWS where master='CNTR_STATUS';

--删除指定表的主键

alter table SHIP_BILL_CNTR_RALLY drop constraint primary key

--删除索引

drop index PK_SHIP_BILL_CNTR_RALLY

-- 查看所有用户和密码

select * from DBA_USERS for update

alter user LS identified by "123456";

alter user LS identified by values '8D46DA8D6C9C83AC';

-- 创建表空间

create tablespace LS_CRM
logging
datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LS_CRM.DBF'
size 50M
autoextend on
next 10m maxsize 5120M
extent management local;

--创建临时表空间

create temporary tablespace TEMP
tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP.DBF'
size 50m
autoextend on
next 10m maxsize 5120M
extent management local;

-- 创建用户名授权

create user LS_CRM identified by ok
default tablespace LS_CRM
temporary tablespace TEMP;

grant dba to LS_CRM;

-- 查看数据库版本

select * from v$version;

--删除用户以及用户所有的对象

drop user LS cascade;
-- 删除表空间

drop tablespace LS  including contents and datafiles cascade constraints;
--including contents 删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉，所以习惯性的加此参数
--including datafiles 删除表空间中的数据文件
--cascade constraints 同时删除tablespace中表的外键参照

-- 增加一列：
alter table MAIL_CONFIG add CONFIG_ID varchar2(10);

-- 修改一列：
alter table MAIL_CONFIG modify CONFIG_ID varchar2(20);

-- 删除一列：
alter table MAIL_CONFIG drop column CONFIG_ID;

-- 批量修改索引所在表空间

select 'alter index '|| index_name ||' rebuild tablespace LS_UNISAOS50;' from user_indexes;

-- 创建公共同义词

create public synonym CNTR_STATUS for CNTR_STATUS@GSPOS_SHJJHY_LINK;

-- 查看同义词
select * from dba_synonyms where owner = 'LS'

-- 删除公共同义词

drop public synonym CNTR_STATUS;

-- 查看当前用户的回收站

select * from user_recyclebin;

-- 查看全部的回收站

select * from recyclebin;

-- 使用对象原来名称，从回收站中清除表EMP。

purge table EMP;

-- 使用系统生成名称，从回收站中清除表EMP

purge table BIN$6kHa6WY7cZzgQAB/AQAWRg==$0;

-- 清除整个回收站

purge recyclebin;

-- 清除指定表空间

purge tablespace users;

-- 清除表空间中指定用户的空间

purge tablespace users user scott;

-- 查看在运行的导入导出的JOB

select * from dba_datapump_jobs;

-- 查看日志组

select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

-- 1.查找到无效对象
select 'Alter '||object_type||' '||object_name||' compile;' from user_objects where status = 'INVALID';

-- 2.重新编译存储过程 pro_backup_call 执行下面脚本即可
alter procedure pro_backup_call compile;

-- 查看所有表空间使用情况

select b.file_id 文件ID号,
       b.tablespace_name 表空间名,
       b.bytes / 1024 / 1024 || 'M' 字节数,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 || 'M' 已使用,
       sum(nvl(a.bytes, 0)) / 1024 / 1024 || 'M' 剩余空间,
       100 - sum(nvl(a.bytes, 0)) / (b.bytes) * 100 占用百分比
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
 group by b.tablespace_name, b.file_id, b.bytes
 order by b.file_id;

-- 查看临时表文件大小和已使用空间

select t1."Tablespace" "Tablespace",
       t1."Total (G)" "Total (G)",
       nvl(t2."Used (G)", 0) "Used(G)",
       t1."Total (G)" - nvl(t2."Used (G)", 0) "Free (G)"
  from (select tablespace_name "Tablespace",
               to_char((sum(bytes / 1024 / 1024 / 1024)), '99,999,990.900') "Total (G)"
          from dba_temp_files
         group by tablespace_name
        union
        select tablespace_name "Tablespace",
               to_char((sum(bytes / 1024 / 1024 / 1024)), '99,999,990.900') "Total (G)"
          from dba_data_files
         where tablespace_name like 'TEMP%'
         group by tablespace_name) t1,
       (select tablespace, round(sum(blocks) * 8 / 1024) "Used (G)"
          from v$sort_usage
         group by tablespace) t2
 where t1."Tablespace" = t2.tablespace(+)

--查看当前临死表使用空间大小与正在占用临时表空间的sql语句

select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;
select 'the ' || name || ' temp tablespaces ' || tablespace_name ||
       ' idle ' ||
       round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
       '% at ' || to_char(sysdate, 'yyyymmddhh24miss')
  from (select d.tablespace_name tablespace_name,
               nvl(sum(used_blocks), 0) tot_used_blocks,
               sum(blocks) total_blocks
          from v$sort_segment v, dba_temp_files d
         where d.tablespace_name = v.tablespace_name(+)
         group by d.tablespace_name) s,
       v$database;

Oracle用户解锁

alter user system identified by manager;

SELECT username,PROFILE FROM dba_users

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

alter user LS identified by ok account unlock;

输入dba的用户名和密码后进行.

SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'

查询密码的有效期设置，LIMIT字段是密码有效天数。
在密码将要过期或已经过期时可通过如下语句进行修改密码，密码修改后该用户可正常连接数据库。

ALTER USER 用户名 IDENTIFIED BY 密码 ;

如果想去除180天的密码生存周期的限制可通过如下SQL语句将其关闭

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

如上SQL语句将口令有效期默认值180天修改成了“无限制”。但是出于数据库安全性考虑，不建议将PASSWORD_LIFE_TIME值设置成UNLIMITED，建议大家定期修改数据库用户口令。