Select owner, table_name
from dba_tables
where owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')minus
Select owner, table_name
from dba_constraints
where constraint_type ='P'and owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS');
Select role
from dba_roles r
whererole notin('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')andnotexists(Select1from dba_role_privs pwhere p.granted_role = r.role);
5.7 将System表空间作为临时表空间的用户(除Sys外)
Select username
from dba_users
where temporary_tablespace ='SYSTEM';
5.8 将System表空间作为默认表空间的用户(除Sys外)
Select username
from dba_users
where default_tablespace ='SYSTEM'and username <>'SYS';
5.9 没有授予给任何用户的profiles
Selectdistinct profile
from dba_profilesminus
Selectdistinct profile
from dba_users;5.10 没有和Package相关联的Package Body
Select pb.owner, pb.object_name
from dba_objects pb
where pb.object_type ='PACKAGE BODY'andnotexists(Select1from dba_objects pwhere p.object_type ='PACKAGE'and p.owner = pb.ownerand p.object_name = pb.object_name)and pb.owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')orderby1,2;
5.11 被Disabled的约束
Select owner, table_name, constraint_name, CONSTRAINT_TYPE
from dba_constraints
wherestatus='DISABLED'and owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')ORDERBY1,2,3;
Select OWNER, OBJECT_NAME, OBJECT_TYPE
from dba_objects
wherestatus='INVALID'and owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')ORDERBY1,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, what
from 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, what
from dba_jobs
where job notin(select job from dba_jobs_running)and broken='N'and next_date<sysdate;
5.16 含有未分析的非系统表的Schemas
Selectdistinct owner "Schema"from DBA_tables
where num_rows isnulland owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')orderby1;
5.17 含有未分析的非系统分区表的Schemas
Selectdistinct table_owner "Schema"from DBA_tab_partitions
where num_rows isnulland table_owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')orderby1;
5.18 含有未分析的非系统索引的Schemas
Selectdistinct owner "Schema"from DBA_indexes
where leaf_blocks isnulland owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')orderby1;
5.19 含有未分析的非系统分区索引的Schemas
Selectdistinct index_owner "Schema"from DBA_ind_partitions
where leaf_blocks isnulland index_owner notin('SYS','SYSTEM','OUTLN','DBSNMP','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','AURORA$ORB$UNAUTHENTICATED','XDB','WMSYS','OLAPSYS','WKSYS')orderby1;
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,(selectcount(*) sess_cnt from v$session) b;
6.5 Top I/O Wait
SELECT/*+ rule */ 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
ORDERBY segment_type,segment_name;