表空间类常用SQL

1.  表空间使用情况

set linesize 200
set pagesize 2000
set time on
set timing on
col tablespace_name for a25
SELECT DF.TABLESPACE_NAME,
       COUNT(*) DATAFILE_COUNT,
       ROUND(SUM(DF.BYTES) / 1048576 / 1024, 2) SIZE_GB,
       ROUND(SUM(FREE.BYTES) / 1048576 / 1024, 2) FREE_GB,
       ROUND(SUM(DF.BYTES) / 1048576 / 1024 -
             SUM(FREE.BYTES) / 1048576 / 1024,
             2) USED_GB,
       ROUND(MAX(FREE.MAXBYTES) / 1048576 / 1024, 2) MAXFREE,
       100 - ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_USED,
       ROUND(100.0 * SUM(FREE.BYTES) / SUM(DF.BYTES), 2) PCT_FREE
  FROM DBA_DATA_FILES DF,
       (SELECT TABLESPACE_NAME,
               FILE_ID,
               SUM(BYTES) BYTES,
               MAX(BYTES) MAXBYTES
          FROM DBA_FREE_SPACE
         WHERE BYTES > 1024 * 1024
         GROUP BY TABLESPACE_NAME, FILE_ID) FREE
 WHERE DF.TABLESPACE_NAME = FREE.TABLESPACE_NAME(+)
   AND DF.FILE_ID = FREE.FILE_ID(+)
 GROUP BY DF.TABLESPACE_NAME
 ORDER BY 8;

select a.tablespace_name,
       round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) SIZE_GB,
       round(a.used_space * b.value / 1024 / 1024 / 1024, 2) USED_GB,
       round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) -
       round(a.used_space * b.value / 1024 / 1024 / 1024, 2) FREE_GB,
       ROUND(A.used_percent, 1)|| '%' used_percent
  from dba_tablespace_usage_metrics a, v$parameter b
 where b.NAME = 'db_block_size'
 order by used_percent DESC;

每天使用量 ---( 不是很准确 , 实际情况大于查询结果 )

select trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'dd') m_date,
       b.name,
       round(sum(tablespace_usedsize) / 1024 / 1024) used_mb
  from dba_hist_tbspc_space_usage a, v$tablespace b
 where a.tablespace_id = b.ts#
   and b.name = 'GW_DATA'
 group by trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'dd'), b.name
 order by 1;

##############表空间增长情况

select 
a.time ,a.free_gb,a.used_gb,   a.used_gb-lag(a.used_gb,1,a.used_gb) over(order by a.time) used_diff
   --a.value-lag(a.value,1,a.value) over(order by a.snap_id) snap_parse_count_hard_diff
  from mon_tablespace_usage a
 where db_name LIKE '%zjcrmb%'
   and tablespace_name = 'YY_INX'
   AND TIME > SYSDATE - 60
   order by time

2.  表空间中占用空间大的段

select owner,segment_name,sum(bytes)/1024/1024/1024 gbytes
  from dba_segments
 where tablespace_name='PRECISE_PW_TAB_MEDIUM'
 group by  owner,segment_name
 having sum(bytes)/1024/1024/1024>1
 order by 3 desc;

3.  undo 表空间使用情况

（如果 used_rag>60% 需要查具体是哪个进程）

set linesize 200
col used_pct format a8
select b.tablespace_name,
       nvl(used_undo,0) "USED_UNDO(M)",
       total_undo "Total_undo(M)",
       trunc(nvl(used_undo,0) / total_undo * 100, 2) || '%' used_PCT
  from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
          from dba_undo_extents
         where status = 'ACTIVE'
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes / 1024 / 1024) total_undo
          from dba_data_files
         where tablespace_name in
               (select value
                  from v$spparameter
                 where name = 'undo_tablespace'
                   and (sid = (select instance_name from v$instance) or
                       sid = '*'))
         group by tablespace_name) b
 where a.tablespace_name (+)= b.tablespace_name
/

-- 注 : 包含 UNEXPIRED 类型 （主要是用这个）

select b.tablespace_name,
       nvl(used_undo,0) "USED_UNDO(M)",
       total_undo "Total_undo(M)",
       trunc(nvl(used_undo,0) / total_undo * 100, 2) || '%' used_PCT
  from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
          from dba_undo_extents
         where status in ( 'ACTIVE','UNEXPIRED')
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes / 1024 / 1024) total_undo
          from dba_data_files
         where tablespace_name in
               (select value
                  from v$spparameter
                 where name = 'undo_tablespace'
                   and (sid = (select instance_name from v$instance) or
                       sid = '*'))
         group by tablespace_name) b
 where a.tablespace_name (+)= b.tablespace_name
/

select tablespace_name,status,sum(bytes)/1024/1024 MB from dba_undo_extents 
where tablespace_name like 'UNDOTBS%'
group by tablespace_name,status
order by 1
/

4.  各个 session 使用的 undo

     SELECT r.name rbs,
       t.used_ublk * TO_NUMBER(x.value) / 1024 / 1024 as undo_mb ,
       nvl(s.username, 'None') oracle_user,
       s.osuser client_user,
       p.username unix_user,
       S.module,
       s.sid,
       s.serial#,
       p.spid unix_pid,
       TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,
       TO_CHAR(sysdate - (s.last_call_et) / 86400, 'mm/dd/yy hh24:mi:ss') as last_txn
 FROM v$process      p,
       v$rollname    r,
       v$session     s,
       v$transaction t,
       v$parameter   x 
 WHERE s.taddr = t.addr 
   AND s.paddr = p.addr 
   AND r.usn = t.xidusn(+) 
   AND x.name = 'db_block_size' 
 ORDER by undo_mb desc;

5.  undo 历史情况

select a.begin_time, 
          a.end_time, 
          a.snap_id, 
          a.instance_number, 
          a.undoblks, 
          a.activeblks, 
          a.unexpiredblks, 
          a.expiredblks, 
          a.tuned_undoretention 
     from dba_hist_undostat a 
     where a.instance_number=1 
     order by a.begin_time;

6.  TEMP 表空间使用情况 - --- RAC

SELECT inst_id, 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 v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by inst_id,A.tablespace_name, D.mb_total;

---- 单节点

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 v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$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;

7.  各个 session 使用的 TEMP 段 ----a.

SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       S.osuser,
       P.spid,
       S.module,
       S.program,
       SUM(T.blocks) * TBS.block_size / 1024/1024  mb_used,
       T.tablespace,
       COUNT(*) sort_ops
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
   AND S.paddr = P.addr
   AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid,
          S.serial#,
          S.username,
          S.osuser,
          P.spid,
          S.module,
          S.program,
          TBS.block_size,
          T.tablespace
 ORDER BY sid_serial;

----b.

SELECT S.sid || ',' || S.serial# sid_serial,
       S.username,
       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$sqlarea 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;