5数据库对象
5.1没有主键的非系统表
Select owner, table_namefrom dba_tableswhere owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\') minusSelect owner, table_namefrom dba_constraintswhere constraint_type = \'P\' and owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\');
5.2没有索引的外键
SELECT acc.owner,acc.table_name,acc.constraint_name,acc.column_nameFROM all_cons_columns acc, all_constraints acWHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = \'R\' and acc.owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\') AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN (SELECT acc.owner, acc.table_name, acc.column_name, acc.position FROM all_cons_columns acc, all_constraints ac WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = \'R\' MINUS SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns)ORDER BY acc.owner,acc.table_name, acc.constraint_name,acc.column_name;
5.3建有6个以上索引的非系统表
Select table_owner, table_name, count(*) index_countfrom dba_indexeswhere table_owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')having count(*) > 6group by table_owner, table_nameorder by 1,3 desc;
5.4指向对象不存在的Public同义词
Select s.synonym_name, s.table_owner, s.table_namefrom sys.DBA_synonyms swhere not exists (Select \'x\' from sys.DBA_objects o where o.owner = s.table_owner and o.object_name = s.table_name)and db_link is null and s.owner = \'PUBLIC\'and s.table_owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')order by 1;
5.5指向对象不存在的非Public同义词
Select s.owner, s.synonym_name, s.table_owner, s.table_namefrom sys.DBA_synonyms swhere not exists (Select \'x\' from sys.DBA_objects o where o.owner = s.table_owner and o.object_name = s.table_name) and db_link is null and s.owner \'PUBLIC\'and s.owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')order by 1;
5.6没有授予给任何角色和用户的角色
Select rolefrom dba_roles rwhere role not in ( \'CONNECT\',\'RESOURCE\',\'DBA\',\'SELECT_CATALOG_ROLE\', \'EXECUTE_CATALOG_ROLE\',\'DELETE_CATALOG_ROLE\', \'EXP_FULL_DATABASE\',\'WM_ADMIN_ROLE\',\'IMP_FULL_DATABASE\', \'RECOVERY_CATALOG_OWNER\',\'AQ_ADMINISTRATOR_ROLE\', \'AQ_USER_ROLE\',\'GLOBAL_AQ_USER_ROLE\',\'OEM_MONITOR\',\'HS_ADMIN_ROLE\') and not exists (Select 1 from dba_role_privs p where p.granted_role = r.role);
5.7将System表空间作为临时表空间的用户(除Sys外)
Select usernamefrom dba_userswhere temporary_tablespace = \'SYSTEM\';
5.8将System表空间作为默认表空间的用户(除Sys外)
Select usernamefrom dba_userswhere default_tablespace = \'SYSTEM\' and username \'SYS\' ;
5.9没有授予给任何用户的profiles
Select distinct profilefrom dba_profiles minusSelect distinct profilefrom dba_users;5.10没有和Package相关联的Package BodySelect pb.owner, pb.object_namefrom dba_objects pbwhere pb.object_type = \'PACKAGE BODY\' and not exists (Select 1 from dba_objects p where p.object_type = \'PACKAGE\' and p.owner = pb.owner and p.object_name = pb.object_name)and pb.owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')order by 1,2;
5.11被Disabled的约束
Select owner, table_name, constraint_name, CONSTRAINT_TYPEfrom dba_constraintswhere status = \'DISABLED\'and owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')ORDER BY 1,2,3;
5.12被Disabled的触发器
Select owner, nvl(table_name, \'\') table_name, trigger_namefrom dba_triggerswhere status = \'DISABLED\'and owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')ORDER BY 1,2,3;
5.13Invalid Objects
Select OWNER, OBJECT_NAME, OBJECT_TYPEfrom dba_objectswhere status = \'INVALID\'and owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')ORDER BY 1,2,3;
5.14执行失败或中断的Jobs
select job, to_char(last_date,\'yyyy-mm-dd hh24:mi:ss\') \"Last Date\", to_char(this_date,\'yyyy-mm-dd hh24:mi:ss\') \"This Date\", broken,failures, schema_user, whatfrom dba_jobs where broken=\'Y\' or failures>0;
5.15当前未执行且下一执行日期已经过去的Jobs
select job, to_char(last_date,\'yyyy-mm-dd hh24:mi:ss\') \"Last Date\", to_char(this_date,\'yyyy-mm-dd hh24:mi:ss\') \"This Date\", broken,failures, schema_user, whatfrom dba_jobswhere job not in (select job from dba_jobs_running) and broken=\'N\' and next_date<sysdate;
5.16含有未分析的非系统表的Schemas
Select distinct owner \"Schema\"from DBA_tableswhere num_rows is null and owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')order by 1;
5.17含有未分析的非系统分区表的Schemas
Select distinct table_owner \"Schema\"from DBA_tab_partitionswhere num_rows is null and table_owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')order by 1;
5.18含有未分析的非系统索引的Schemas
Select distinct owner \"Schema\" from DBA_indexeswhere leaf_blocks is null and owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')order by 1;
5.19含有未分析的非系统分区索引的Schemas
Select distinct index_owner \"Schema\"from DBA_ind_partitionswhere leaf_blocks is null and index_owner not in (\'SYS\', \'SYSTEM\', \'OUTLN\', \'DBSNMP\', \'ORDSYS\', \'ORDPLUGINS\', \'MDSYS\', \'CTXSYS\', \'AURORA$ORB$UNAUTHENTICATED\', \'XDB\',\'WMSYS\',\'OLAPSYS\',\'WKSYS\')order by 1;
5.20回滚段空间配置
select r.segment_name segment_name, r.owner owner, r.tablespace_name tablespace_name, r.status status, round(r.initial_extent / 1024 / 1024) initial_extent, round(r.next_extent / 1024 / 1024) next_extent, s.extents, 0 extents, ROUND(s.rssize / 1024 / 1024) rssize, s.xacts active_trans from dba_rollback_segs r, v$rollname n, v$rollstat s where r.segment_name = n.name and n.usn = s.usn;
5.21用户角色查询
select username, ACCOUNT_STATUS, default_tablespace, temporary_tablespace, granted_role from dba_users u, dba_role_privs r where u.username = r.grantee order by username;
5.22表和索引在同一表空间（不包含USERS,SYSAUX,SYSMAN,SYSTEM,TEMP 表空间）
select a.owner, a.tablespace_name tbsname, a.table_name tname, b.index_name iname from dba_tables a, dba_indexes b where a.tablespace_name = b.tablespace_name and b.table_name = a.table_name and a.owner = b.owner and b.owner NOT in (\'SYS\', \'SYSTEM\') and a.tablespace_name not in (\'USERS\', \'SYSAUX\', \'SYSMAN\', \'SYSTEM\', \'TEMP\') order by owner;
5.23单个用户大小估算
select nvl(t.owner, \'total:\') owner, case when (to_char(sum(bytes) / 1024 / 10241)) < 1 then \'0\' || to_char(round(sum(bytes) / 1024 / 10241, 2)) else to_char(round(sum(bytes) / 1024 / 10241, 2)) end \"大小/Mb\" from dba_segments t group by rollup(t.owner);
5.24具有DBA角色的用户
select grantee,granted_role from dba_role_privs where granted_role=\'DBA\';
5.25具有SYSDBA权限的用户
SELECT * FROM v$pwfile_users;
5.26系统表空间中非SYS对象
select OWNER, SEGMENT_NAME, SEGMENT_TYPE, decode(segment_type, \'TABLE\', \'alter table \' || OWNER || \'.\' || SEGMENT_NAME || \' MOVE TABLESPACE &\' || \'TABLESPACE;\', \'INDEX\', \'alter index \' || OWNER || \'.\' || SEGMENT_NAME || \' REBUILD TABLESPACE &\' || \'TABLESPACE NOLOGGING;\', null) SCRIPT from dba_segments t where t.tablespace_name = \'SYSTEM\' AND OWNER NOT IN (\'SYS\', \'OUTLN\', \'SYSTEM\', \'WMSYS\');
5.27检测SYSTEM表空间里的用户对象
select owner, segment_type, segment_name from dba_segments where owner not in (\'SYS\', \'SYSTEM\') and tablespace_name = \'SYSTEM\' order by 1;
5.28未建索引的表（不包含表空间为’SYSTEM’, ‘SYSAUX’, ‘SYSMAN’, ‘USERS’, \'TEMP’下的用户）
SELECT owner, segment_name, segment_type, tablespace_name, TRUNC(BYTES / 1024 / 1024, 1) size_mb FROM dba_segments t WHERE NOT EXISTS (SELECT \'x\' FROM dba_indexes i WHERE t.owner = i.table_owner AND t.segment_name = i.table_name) AND t.segment_type IN (\'TABLE\', \'TABLE PARTITION\') AND t.owner IN (select username from dba_users d where d.default_tablespace not in (\'SYSTEM\', \'SYSAUX\', \'SYSMAN\', \'USERS\', \'TEMP\')and d.account_status = \'OPEN\') ORDER BY 5 DESC;
5.29sort_segment检查
select tablespace_name,extent_size db_blocks_per_extent,total_extents, used_extents,free_extents from v$sort_segment;
5.30超过2g的segment（单个表超过2g建议使用分区表）
select * from (Select segment_name, bytes / 1024 / 1024 size_M, segment_type, tablespace_name from dba_segments where bytes > 2 * 1024 * 1024 * 1024 order by bytes desc);
5.31定时任务（JOB）
SELECT JOB, LOG_USER, PRIV_USER, SCHEMA_USER, LAST_DATE, THIS_DATE, NEXT_DATE, TOTAL_TIME, DECODE(BROKEN, \'Y\', \'YES\', \'N\', \'NO\') \"JOB_BROKEN\", INTERVAL, FAILURES, TRANSLATE(WHAT, chr(10), \' \') WHAT FROM DBA_JOBS ORDER BY JOB;
5.32Rollback信息
select substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) \"ID#\", substr(sys.dba_segments.OWNER,1,8) \"Owner\", substr(sys.dba_segments.TABLESPACE_NAME,1,17) \"Tablespace Name\", substr(sys.dba_segments.SEGMENT_NAME,1,12) \"Rollback Name\", substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) \"INI_Extent\", substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) \"Next Exts\", substr(sys.dba_segments.MIN_EXTENTS,1,5) \"MinEx\", substr(sys.dba_segments.MAX_EXTENTS,1,5) \"MaxEx\", substr(sys.dba_segments.PCT_INCREASE,1,5) \"%Incr\", substr(sys.dba_segments.BYTES,1,15) \"Size (Bytes)\", substr(sys.dba_segments.EXTENTS,1,6) \"Extent#\", substr(sys.dba_rollback_segs.STATUS,1,10) \"Status\"from sys.dba_segments, sys.dba_rollback_segswhere sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and sys.dba_segments.segment_type = \'ROLLBACK\'order by sys.dba_rollback_segs.segment_id;
5.33查看表分区的信息
select t.table_name, kc.column_name, t.partitioning_type from dba_part_key_columns kc, dba_part_tables t where kc.owner = t.owner and kc.name = t.table_name;
5.34查看超过16G的索引对像
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES FROM DBA_SEGMENTS WHERE SEGMENT_TYPE=\'INDEX\' AND BYTES>=17179869184;ALTER INDEX index_name REBUILD [ONLINE];ALTER INDEX REBUILD ALTER INDEX REBUILD ONLINE
6性能
6.1锁等待检测
SELECT substr(lpad(\'--->\',DECODE(request,0,0,4))||sid,1,20) \"SESSID\", id1, id2, lmode, request, typeFROM V$LOCK WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)ORDER BY id1,request;
6.2死锁检测
SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID,vss.SERIAL vss.action Action,vss.osuser OSUSER, vss.process AP_Process_ID,VPS.SPID DB_Process_ID from v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS where lo.OBJECT_ID = dob.OBJECT_ID and lo.SESSION_ID = vss.SID AND VSS.paddr = VPS.addr order by 2,3,DOB.object_name;
6.3锁信息
select s.sid sid, s.username username, s.machine machine, l.type type, o.object_name object_name, DECODE(l.lmode, 0,\'None\', 1,\'Null\', 2,\'Row Share\', 3,\'Row Exlusive\', 4,\'Share\', 5,\'Sh/Row Exlusive\', 6,\'Exclusive\') lmode, DECODE(l.request, 0,\'None\', 1,\'Null\', 2,\'Row Share\', 3,\'Row Exlusive\', 4,\'Share\', 5,\'Sh/Row Exlusive\', 6,\'Exclusive\') request, l.block block from v$lock l, v$session s, dba_objects owhere l.sid = s.sid and username != \'SYSTEM\' and o.object_id(+) = l.id1;
6.4用户会话情况
select max_proc, sess_cnt, round((sess_cnt*100)/max_proc,2) \"USED%\"from(select to_number(value) max_proc from v$parameter where lower(name)=\'processes\') a,(select count(*) sess_cnt from v$session) b;
6.5Top I/O Wait
SELECT event,segment_type,segment_name,file_id,block_id,blocks FROM dba_extents, gv$session_wait WHERE p1text=\'file#\' AND p2text=\'block#\' AND p1=file_id and p2 between block_id AND block_id+blocks ORDER BY segment_type,segment_name;
6.6Top 10 Wait
select * from ( select event,sum(decode(wait_Time,0,0,1)) \"Prev\", sum(decode(wait_Time,0,1,0)) \"Curr\",count(*) \"Total\" from v$session_Wait group by event order by 4 desc ) where rownum<=10;
6.7Top 10 bad SQL
SELECT *FROM (SELECT parsing_user_id executions, sorts, command_type, disk_reads, sql_text FROM v$sqlarea ORDER BY disk_reads DESC)WHERE rownum < 10;
6.8Top most expensive SQL (Buffer Gets by Executions)
select buffer_gets, executions, buffer_gets/ decode(executions,0,1, executions) gets_per_exec, hash_value, sql_textfrom v$sqlareawhere buffer_gets > 50000order by buffer_gets desc;
6.9Top most expensive SQL (Physical Reads by Executions)
select disk_reads, executions, disk_reads / decode(executions,0,1, executions) reads_per_exec, hash_value, sql_textfrom v$sqlareawhere disk_reads > 10000order by disk_reads desc;
6.10Top most expensive SQL (Rows Processed by Executions)
select rows_processed, executions, rows_processed / decode(executions,0,1, executions) rows_per_exec, hash_value, sql_textfrom v$sqlareawhere rows_processed > 10000order by rows_processed desc;
6.11Top most expensive SQL (Buffer Gets vs Rows Processed)
select buffer_gets, lpad(rows_processed || decode(users_opening + users_executing, 0, \' \',\'*\'),20) \"rows_processed\", executions, loads, (decode(rows_processed,0,1,1))*buffer_gets/ decode(rows_processed,0,1,rows_processed) avg_cost, sql_textfrom v$sqlareaWhere decode(rows_processed,0,1,1) * buffer_gets/decode(rows_processed,0,1,rows_processed)>10000order by 5 desc;
6.12Top 10 等待事件
select EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED, AVERAGE_WAIT from ( select * from v$system_event where event not like \'%rdbms%\' and event not like \'%message%\' and event not like \'SQL*Net%\' order by total_waits desc ) WHERE ROWNUM <= 10;
6.13数据库长事务（执行超过6S）
select s.username, q.sql_text, s.elapsed_seconds, opname from v$session_longops s, v$sqlarea q where s.sql_hash_value = q.hash_value order by s.ELAPSED_SECONDS desc;
6.14产生大量物理读的进程
select st.sid, st.value, sn.name, s.username from v$sesstat st, v$statname sn, v$session s where st.sid = s.sid AND st.statistic and st.value > 100000 and s.username is not null and sn.name like \'%physical read%\' order by 2 desc;
6.15产生归档日志过快的进程
select sysdate, se.username, se.sid, se.serial se.SQL_HASH_VALUE, se.status, se.machine, se.osuser, round(st.value / 1024 / 1024) redosize, sa.sql_text from v$session se, v$sesstat st, v$sqlarea sa where se.sid = st.sid and st.STATISTIC (select STATISTIC and se.username is not null and st.value > 10 * 1024 * 1024 and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE order by redosize;
6.16等待事件对应的SQL语句
select b.sql_text text, a.sid sid, a.serial a.username \"user\", a.machine machine from v$session a, v$sqltext b, v$session_wait c where a.sid = c.sid and b.address = a.sql_address and b.hash_value = a.sql_hash_value order by a.sid, a.serial
6.17占用大量temp表空间的session和sql监控
select su.extents, su.segtype, su.sqlhash, se.sid, se.serial from v$sort_usage su, v$session se ,v$sqlarea sawhere su.session_addr=se.saddr and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and su.extents>10;
6.18回滚段争用情况
select name ,waits ,gets ,waits/gets \"Ratio\" from v$rollstat a ,v$rollname b where a.usn=b.usn;
6.19Session等待事件
select sid, event, p1, p1text from v$session_wait s;
6.20Listing Memory Used By All Sessions
select se.sid, n.name, max(se.value) maxmem from v$sesstat se, v$statname n where n.statistic and n.name in (\'session pga memory\', \'session pga memory max\', \'session uga memory\', \'session uga memory max\') group by n.name, se.sid order by 1,3;
