oracle dba常用脚本2

11、表空间实有、现有、使用情况查询对比
SELECT TABLESPACE_NAME 表空间,
       TO_CHAR(ROUND(BYTES / 1024, 2), '99990.00') || '' 实有,
       TO_CHAR(ROUND(FREE / 1024, 2), '99990.00') || 'G' 现有,
       TO_CHAR(ROUND((BYTES - FREE) / 1024, 2), '99990.00') || 'G' 使用,
       TO_CHAR(ROUND(10000 * USED / BYTES) / 100, '99990.00') || '%' 比例
  FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
               FLOOR(A.BYTES / (1024 * 1024)) BYTES,
               FLOOR(B.FREE / (1024 * 1024)) FREE,
               FLOOR((A.BYTES - B.FREE) / (1024 * 1024)) USED
          FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME, SUM(BYTES) BYTES
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME, SUM(BYTES) FREE
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) B
         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)
 ORDER BY FLOOR(10000 * USED / BYTES) DESC;

12、数据文件自动扩展
ALTER DATABASE DATAFILE '/oradata/zghx/TS_XMGL_DATA1.dbf' AUTOEXTEND ON NEXT  100M MAXSIZE  30000M;

13、查看回滚
select t.STATE,
       t.UNDOBLOCKSDONE 已回滚,
       t.UNDOBLOCKSTOTAL - t.UNDOBLOCKSDONE 剩余,
       t.UNDOBLOCKSTOTAL 回滚总量
  from v$fast_start_transactions t
 where state = 'RECOVERING';

14、查看session 回滚大小
select a.SID,
       a.USERNAME,
       b.XIDUSN,
       b.USED_UREC,
       b.USED_UBLK,
       r.RSSIZE / 1024 / 1024
  from v$transaction b, v$session a, v$rollstat r
 where a.SADDR = b.SES_ADDR
   and b.XIDUSN = r.USN
   and a.sid = 'xxx'

15、用户操作权限
SELECT *
  FROM dba_TAB_PRIVS t
 where t.owner not in ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'PERFSTAT',
        'MGMT_VIEW', 'CTXSYS', 'MDSYS', 'DMSYS', 'OLAPSYS',
        'ORDSYS', 'XDB', 'WMSYS', 'SYSMAN', 'ORDPLUGINS')
 order by t.owner;

在这里插入代码片

16、检查一下系统当前的锁
SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
  FROM V$LOCK
 WHERE SID IN (SELECT SID FROM V$SESSION WHERE USERNAME IS NOT NULL);

17、查找前十条磁盘读多(性能差)的sql
SELECT SID, TYPE, ID1, ID2, LMODE, CTIME
  FROM V$LOCK
 WHERE ADDR IN
       (SELECT ADDR FROM V$TRANSACTION WHERE START_DATE < TRUNC(SYSDATE));

18、查看占io较大的正在运行的session
 SELECT se.sid,
        se.serial#,
        se.sql_address,
        se.username,
        se.status,
        se.terminal,
        se.program,
        se.MODULE,
        pr.SPID,
        st.event,
        st.p1text,
        si.physical_reads,
        si.block_changes
   FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
  WHERE st.sid = se.sid
    AND st.sid = si.sid
    AND se.PADDR = pr.ADDR
    AND se.sid > 6
    AND st.wait_time = 0
    AND st.event NOT LIKE 'SQL%'
  ORDER BY physical_reads DESC;

19、查看当前有哪些用户正在使用数据
select a.osuser,
       a.username,
       a.machine,
       b.cpu_time / b.executions / 1000000 || 's',
       b.sql_text 　　
  from v$session a, v$sqlarea b 　　
 where a.sql_address = b.address 　　
 order by cpu_time / executions desc;

20、查看锁
col object_name for a35;
col machine     for a30;
select object_name,
         object_type,
         machine,
         s.sid,
         s.serial#,
         s.username,
         s.program,
         s.module,
         s.status
    from v$locked_object l, dba_objects o, v$session s
   where l.object_id = o.object_id(+)
     and l.session_id = s.sid;

21、查各session的逻辑读及物理读
col machine for a15
col module for a25
select s.sid,s.serial#,s.process,s.machine,s.module,si.block_gets,si.physical_reads
  from v$session s,v$session_wait sw,v$sess_io si 
where s.status='ACTIVE'
  and s.sid=sw.sid(+) 
  and s.sid=si.sid(+)
  and s.module is not null;

22、查长事务
SELECT SID,
       SERIAL#,
       CONTEXT,
       SOFAR,
       TOTALWORK,
       to_char(start_time, 'yyyymmddhh24miss') strtime,
       ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"
  FROM V$SESSION_LONGOPS
 WHERE TOTALWORK != 0
   AND SOFAR <> TOTALWORK;

23、查长事务相关的SQL
select s.sid, s.serial#, q.sql_text
  from v$session s, v$sql q
 where s.sid in (SELECT SID
                   FROM V$SESSION_LONGOPS
                  WHERE TOTALWORK != 0
                    AND SOFAR <> TOTALWORK)
   and s.sql_address = q.address;

24、查大量的Enqueue Wait时查哪些会话闲着	
select sysdate day,
       sid,
       serial#,
       machine,
       nvl(sql_hash_value, prev_hash_value) hash_value,
       last_call_et
  from v$session
 where sid in (select sid
                 from v$lock
                where block = 1
               UNION ALL
               select sid
                 from v$lock
                where type = 'TX'
                  and lmode > 1)
   and last_call_et > 60
   and type = 'USER'
   and status <> 'ACTIVE';

25、查语句使用的临时空间
SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       S.program,
       　　 T.blocks * TBS.block_size / 1024 / 1024 mb_used,
       T.tablespace,
       　　 T.sqladdr address,
       Q.hash_value,
       Q.sql_text 　　
  FROM v$sort_usage T, v$session S, v$sql Q, dba_tablespaces TBS 　　
 WHERE T.session_addr = S.saddr 　　
   AND T.sqladdr = Q.address(+) 　　
   AND T.tablespace = TBS.tablespace_name 　　
 ORDER BY S.sid;

26、查看每个会话的UGA内存使用
select username, value || 'bytes' "Current UGA memory"
  from v$session sess, v$sesstat stat, v$statname name
 where sess.sid = stat.sid
   and stat.statistic# = name.statistic#
   and name.name = 'session uga memory';

27、查各session消耗CPU的问题
select ss.sid,
       se.machine,
       ss.value CPU,
       se.username,
       se.program,
       se.process,
       se.module
  from v$sesstat ss, v$session se
 where ss.statistic# in
       (select statistic#
          from v$statname
         where name = 'CPU used by this session')
   and se.sid = ss.sid
   and ss.sid > 6
   and se.module is not null
   and se.status = 'ACTIVE'
 order by ss.value;

28、查非并行等待语句
select s.sid, s.serial#, q.sql_text
  from v$session s, v$sql q
 where s.sid in (select sid
                   from v$session_wait
                  where event not like 'PX%'
                    and state = 'WAITING')
   and s.sql_address = q.address;

29、查看各session的buffer cache hit ratio
select substr(a.username, 1, 12) "User",
       a.sid "sid",
       a.SERIAL#,
       a.PROGRAM,
       b.consistent_gets "ConsGets",
       b.block_gets "BlockGets",
       b.physical_reads "PhysReads",
       100 * round((b.consistent_gets + b.block_gets - b.physical_reads) /
                   (b.consistent_gets + b.block_gets),
                   3) HitRatio
  from v$session a, v$sess_io b
 where a.sid = b.sid
   and (b.consistent_gets + b.block_gets) > 0
   and a.username is not null
 order by HitRatio asc;

30、查询最近1个小时内的跑得时间最长的SQL
SELECT a.sql_id,
       a.parsing_schema_name || '.' uname,
       round(a.buffer_gets / decode(a.executions, 0, 1, a.executions)) per_bu,
       TRUNC(((a.ELAPSED_TIME / decode(a.executions, 0, 1, a.executions)) /
             1000000),
             2) etime,
       round(buffer_gets / decode(a.ROWS_PROCESSED, 0, 1, a.ROWS_PROCESSED)) buf_row,
       a.executions,
       a.sql_fulltext,
       a.sql_text,
       a.module
  FROM v$sqlarea a,
       (SELECT DISTINCT sql_id
          FROM v$active_session_history b
         WHERE b.SAMPLE_TIME BETWEEN SYSDATE - 120 / 1440 AND SYSDATE) b
 WHERE a.sql_id = b.sql_id
   AND a.MODULE NOT IN ('PL/SQL Developer', 'plsqldev.exe', 'ORACLE.EXE')
   AND TRUNC(((a.ELAPSED_TIME / decode(a.executions, 0, 1, a.executions)) /
             1000000),
             2) > 100
 ORDER BY 3 DESC;

31、Oracle一个大事务的SQL往往不知道运行到了哪里,可以使用如下SQL查看执行进度。
SELECT se.sid,
       opname,
       TRUNC(sofar / totalwork * 100, 2) pct_work,
       elapsed_seconds elapsed,
       ROUND(elapsed_seconds * (totalwork - sofar) / sofar) remain_time,
       sql_text
  FROM v$session_longops sl, v$sqlarea sa, v$session se
 WHERE sl.sql_hash_value = sa.hash_value
   AND sl.sid = se.sid
   AND sofar != totalwork
 ORDER BY start_time;

32、查询表空间的剩余量
SELECT D.TABLESPACE_NAME "表空间名",
       T.EXTENT_MANAGEMENT,
       T.SEGMENT_SPACE_MANAGEMENT,
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       ROUND(F.TOTAL_BYTES / 1024) "空闲大小G",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') "使用比"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D,
       DBA_TABLESPACES T
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
   AND D.TABLESPACE_NAME = T.TABLESPACE_NAME
--and D.TABLESPACE_NAME not in('UNDOTBS1','UNDOTBS2','UNDOTBS3','UNDOTBS4')
 ORDER BY 7 DESC;

33、临时表空间
SELECT A.TABLESPACE_NAME TABLESPACE,
       D.MB_TOTAL,
       SUM(A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_USED,
       D.MB_TOTAL - SUM(A.USED_BLOCKS * D.BLOCK_SIZE) / 1024 / 1024 MB_FREE
  FROM GV$SORT_SEGMENT A,
       (SELECT B.NAME, C.BLOCK_SIZE, SUM(C.BYTES) / 1024 / 1024 MB_TOTAL
          FROM GV$TABLESPACE B, GV$TEMPFILE C
         WHERE B.TS# = C.TS#
         GROUP BY B.NAME, C.BLOCK_SIZE) D
 WHERE A.TABLESPACE_NAME = D.NAME
 GROUP BY A.TABLESPACE_NAME, D.MB_TOTAL;

34、查询表空间的剩余量
SELECT D.TABLESPACE_NAME "表空间名",
       T.EXTENT_MANAGEMENT,
       T.SEGMENT_SPACE_MANAGEMENT,
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       ROUND(F.TOTAL_BYTES / 1024) "空闲大小G",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') "使用比"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D,
       DBA_TABLESPACES T
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
   AND D.TABLESPACE_NAME = T.TABLESPACE_NAME
   and D.TABLESPACE_NAME not in
       ('UNDOTBS1', 'UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4')
 ORDER BY 7 DESC;

35、失效索引检查
SELECT OWNER INDEX_OWNER,
       INDEX_NAME,
       INDEX_TYPE,
       'N/A' PARTITION_NAME,
       STATUS,
       TABLE_NAME,
       TABLESPACE_NAME
  FROM DBA_INDEXES
 WHERE STATUS = 'UNUSABLE'
UNION ALL
SELECT A.INDEX_OWNER,
       A.INDEX_NAME,
       B.INDEX_TYPE,
       A.PARTITION_NAME,
       A.STATUS,
       B.TABLE_NAME,
       A.TABLESPACE_NAME
  FROM DBA_IND_PARTITIONS A, DBA_INDEXES B
 WHERE A.INDEX_NAME = B.INDEX_NAME
   AND A.INDEX_OWNER = B.OWNER
   AND A.STATUS = 'UNUSABLE';

36、表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小
select t.table_name,
       t.last_analyzed,
       blocks, --表中数据所占的数据块数
       empty_blocks, --表中的空块数
       avg_space, --数据块中平均的使用空间
       chain_cnt, --表中行连接和行迁移的数量
       avg_row_len, --每条记录的平均长度
       t.num_rows --行数
  from dba_tables t
 where t.table_name = upper('emp')
   and t.owner = upper('scott');