Oracle常用巡检脚本

检查Oracle实例状态

select instance_name,host_name,startup_time,status,database_status from v$instance; 

检查Oracle表空间的状态

select tablespace_name,status from dba_tablespaces; 

检查Oracle所有数据文件状态

select name,status from v$datafile;

检查无效对象

select object_type as 对象类型,count(1) as 编译失败对象个数 from user_objects 
where  status !='VALID'  and object_type in ('SYNONYM','TRIGGER','SEQUENCE','DATABASE LINK','MATERIALIZED VIEW','TYPE BODY','TYPE','PACKAGE BODY','PACKAGE','PROCEDURE','FUNCTION','VIEW') 
group by object_type

检查表空间使用情况

SELECT
D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", 
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0)
/ SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)"  FROM 
(SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, 
      (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
      FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, 
USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%'
"USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 
 FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, 
      (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
      ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER 
      GROUP BY TABLESPACE_NAME) F 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
ORDER BY 1;

检查数据库连接情况

select sid,serial#,username,program,machine,status from v$session;

检查数据库的等待事件

select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
 from v$session_wait
where event not like 'SQL%'
  and event not like 'rdbms%';

磁盘读IO最高前5条SQL语句

SELECT SQL_TEXT
FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM <= 5;

检查消耗CPU最高前5个进程

SELECT P.PID PID,
      S.SID SID,
      S.STATUS,
      RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQL FROM V$PROCESS P,
      V$SESSION S,
      V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND ROWNUM<=5

表内主键外键约束索引统计个数

select o.object_name as 表名,  
(select count(1) from user_constraints where  CONSTRAINT_TYPE='R'and  table_name=o.object_name) as 外键个数,
(select count(1) from user_constraints where  CONSTRAINT_TYPE='P'and  table_name=o.object_name) as 主键个数,
(select count(1) from user_constraints where  CONSTRAINT_TYPE='C'and  table_name=o.object_name) as 约束个数,
(select count(1)  from  user_indexes where  index_type <> 'LOB' and table_name=o.object_name) as 索引个数,
(select count(1) from (select a.TABLE_NAME,a.constraint_name  from user_constraints a join user_cons_columns b  on a.constraint_name = b.constraint_name 
join user_tab_columns  c on b.column_name = c.COLUMN_NAME and b.table_name = c.TABLE_NAME 
where a.constraint_type = 'U' group by a.TABLE_NAME,a.constraint_name ) ttt where  TABLE_NAME =o.object_name 
) as 唯一键个数
from user_objects o left join user_nested_tables nt on o.OBJECT_NAME = nt.TABLE_NAME
where nt.TABLE_NAME is null and o.object_type='TABLE' 
and o.object_name not in (select MVIEW_NAME from user_mviews where BUILD_MODE != 'PREBUILT')
and o.object_name not in (select log_table from user_mview_logs) 
and o.object_name not in (select 'RUPD$_'||master from user_mview_logs where primary_key='YES')  
order by o.object_name

统计用户数据对象

select '表格数', count(distinct o.object_name) as table_name from 
user_objects o left join user_nested_tables nt on o.OBJECT_NAME = nt.TABLE_NAME
where nt.TABLE_NAME is null and o.object_type='TABLE' 
and o.object_name not in (select MVIEW_NAME from user_mviews where BUILD_MODE != 'PREBUILT')
and o.object_name not in (select log_table from user_mview_logs) 
and o.object_name not in (select 'RUPD$_'||master from user_mview_logs where primary_key='YES') 
union all
select '视图数',count(1) from user_objects where object_type='VIEW'
union all
select '存储过程数',count(1) from user_objects where object_type='PROCEDURE'
union all
select '函数数',count(1) from user_objects where object_type='FUNCTION'
union all 
select '触发器数',count(1) from user_objects where object_type='TRIGGER'
union all 
select '序列数',count(1) from user_objects where object_type='SEQUENCE'
union all 
select '同义词数',count(1) from user_objects where object_type='SYNONYM'
union all 
select 'DBLINK数',count(1) from user_objects where object_type='DATABASE LINK'
union all
select '物化视图数',count(1) from user_objects where object_type='MATERIALIZED VIEW'
union all
select '类型体数',count(1) from user_objects where object_type='TYPE BODY'
union all
select '类型数',count(1) from user_objects where object_type='TYPE'
union all
select '包体数',count(1) from user_objects where object_type='PACKAGE BODY'
union all
select '包数',count(1) from user_objects where object_type='PACKAGE'
union all
select 'JOB数',count(1) from (
select  cast(job as varchar(100)) as object_name from user_jobs 
union all
select  job_name from user_scheduler_jobs  
)  

检查不起作用主键

SELECT constraint_name, table_name, constraint_type, status
  FROM user_constraints
 WHERE status = 'DISABLE'
   and constraint_type = 'P';

检查失效索引

select index_name, table_name, tablespace_name, status
 From user_indexes
Where   status <> 'VALID';