ORACLE数据库巡检用SQL
sqlplus /nolog
conn /as sysdba
spool d:\oracle.txt
--数据信息
show parameter database;
show parameter instance;
show parameter sga;
show parameter db_cache_size;
show parameter pool_size;
show parameter pga;
show parameter opt;
show parameter open;
show parameter process;
show parameter session;
select * from v$version;
--检查控制文件状态
select * from v$controlfile;
--检查联机日志文件状态
select * from v$logfile;
--检查数据文件状态
select * from v$datafile;
--检查回滚段使用情况
SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;
--检查用户状态
select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;
--是否存在失效对象
select owner,object_name,object_type from dba_objects where status = 'INVALID';
--检测连接数情况
SELECT status,count(*) "count" FROM v$session GROUP BY status;
--检查统计分析情况
select tt.table_name,tt.tablespace_name,tt.num_rows,tt.last_analyzed from user_tables tt order by tt.table_name;
select t.index_name,t.index_type,t.table_name,t.tablespace_name,t.last_analyzed,t.num_rows,t.status
from user_indexes t order by t.status;
--检查NC版本情况
select * from sm_product_version;
--检查表的大小
select 'TOTAL' as table_name,'' as type,'' as tablespace,Sum(bytes)/1024/1024 as MB from User_Extents
union
Select e.Segment_Name,e.segment_type,e.tablespace_name,Sum(e.bytes)/1024/1024 as MB From User_Extents e Group By e.Segment_Name,e.segment_type,e.tablespace_name order by 4 desc ;
--检查大字段的存储占用
select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024 MB
from dba_lobs a, dba_segments b
where a.segment_name = b.segment_name
and a.owner = b.owner and a.owner='NC'
order by b.bytes desc;

--检查表空间的使用状况
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)",
ROUND((NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "FREE_RATE(%)" ,autoextensible
FROM (SELECT TABLESPACE_NAME,max(autoextensible) autoextensible,
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(+) order by d.TABLESPACE_NAME;
--检查临时表空间的使用状况
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
NVL(FREE_SPACE, 0) "FREE_SPACE(M