DBA常用数据库查询语句（2）

5 数据库对象

5.1 没有主键的非系统表

Select owner, table_name
from dba_tables
where owner not in ('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 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_name
FROM all_cons_columns acc, all_constraints ac
WHERE 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_count
from dba_indexes
where table_owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'ORDSYS',
        'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'AURORA$ORB$UNAUTHENTICATED', 'XDB','WMSYS','OLAPSYS','WKSYS')
having count(*) > 6
group by table_owner, table_name
order by 1,3 desc;

5.4 指向对象不存在的Public同义词

Select s.synonym_name, s.table_owner, s.table_name
from  sys.DBA_synonyms s
where 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_name
from  sys.DBA_synonyms s
where 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 role
from dba_roles r
where
  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 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

Select distinct profile
from dba_profiles
  minus
Select distinct 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'
     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_TYPE
from dba_constraints
where 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, '<system trigger>') table_name, trigger_name
from dba_triggers
where 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.13 Invalid Objects

Select OWNER, OBJECT_NAME, OBJECT_TYPE
from dba_objects
where 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, 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 not in (select job from dba_jobs_running)
      and broken='N' and next_date<sysdate;

5.16 含有未分析的非系统表的Schemas

Select distinct owner "Schema"
from DBA_tables
where 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_partitions
where 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_indexes
where 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_partitions
where 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.29 sort_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.32 Rollback信息

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_segs
where 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];
## REBUILD和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, type
FROM 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 o
where
  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.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  
ORDER BY segment_type,segment_name;

6.6 Top 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.7 Top 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.8 Top most expensive SQL (Buffer Gets by Executions)

select buffer_gets, 
       executions,
       buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
       hash_value,
       sql_text
from  v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc;

6.9 Top most expensive SQL (Physical Reads by Executions)

select disk_reads, 
       executions,
       disk_reads / decode(executions,0,1, executions) reads_per_exec,
       hash_value,
       sql_text
from  v$sqlarea
where disk_reads > 10000
order by disk_reads desc;

6.10 Top most expensive SQL (Rows Processed by Executions)

select rows_processed, 
       executions,
       rows_processed / decode(executions,0,1, executions) rows_per_exec,
       hash_value,
       sql_text
from   v$sqlarea
where  rows_processed > 10000
order by rows_processed desc;

6.11 Top 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_text
from   v$sqlarea
Where decode(rows_processed,0,1,1) * buffer_gets/
decode(rows_processed,0,1,rows_processed)>10000
order by 5 desc;

6.12 Top 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# = sn.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# from v$statname where NAME = 'redo size')
   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#  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#, b.piece;

6.17 占用大量temp表空间的session和sql监控

select su.extents, su.segtype, su.sqlhash, se.sid, se.serial#, se.last_call_et, se.username, se.machine ,sa.sql_text
 from v$sort_usage su, v$session se ,v$sqlarea sa
where 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.19 Session等待事件

select sid, event, p1, p1text from v$session_wait s;

6.20 Listing Memory Used By All Sessions

select se.sid, n.name, max(se.value) maxmem
  from v$sesstat se, v$statname n
 where n.statistic# = se.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;