查询表空间使用情况

SQL语句版

set timing on
set time on
set lines 168 pages 100
set COLSEP '|'
col tbsname for a24      heading 'TablespaceName'
col blks for 999999      heading 'Block|Size(kb)'
col initext for 999999   heading 'INITIAL|EXTENT(kb)'
col nextext for 999999   heading 'NEXT|EXTENT(kb)'
col ctnt for a4
col status for a9
col EXT_MGMT for a12
col BIGFILE for a8
col usegb for 99999.9
col MAXGB for 99999
col FREEGB for 99999.9
col FREEPCT for 999.9
SELECT /*+ gather_plan_statistics */
   d.tablespace_name tbsname,d.BLOCK_SIZE/1024 blks,d.INITIAL_EXTENT/1024 initext,d.NEXT_EXTENT/1024 nextext
  , substr(d.contents,1,4) ctnt
  , status
  , EXTENT_MANAGEMENT || '/' || SEGMENT_SPACE_MANAGEMENT ext_mgmt
  , BIGFILE
  , NVL(round(a.bytes/1073741824,1),0) usegb
  , NVL(round(a.mbytes/1073741824,1),0) maxgb
  , NVL(round(decode(contents,'TEMPORARY',a.bytes - NVL(f.bytes,0),f.bytes)/1073741824,1),0) freegb
  , NVL(round(decode(a.mbytes,0,0,(a.mbytes - a.bytes + decode(contents,'TEMPORARY',a.bytes - NVL(f.bytes,0),NVL(f.bytes,0)))/a.mbytes*100),1),0) freepct
  from
   dba_tablespaces d,
   (select tablespace_name,sum(bytes) bytes,sum(mbytes) mbytes from
      (select tablespace_name,bytes,decode(maxbytes,0,bytes,'',bytes,maxbytes) mbytes from dba_data_files union all
       select tablespace_name,bytes,decode(maxbytes,0,bytes,'',bytes,maxbytes) mbytes from dba_temp_files)
    group by tablespace_name) a,
   (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name union all
      select tablespace,sum(su.blocks * (select value from v$parameter where name ='db_block_size')) bytes
      from sys.gv_$sort_usage su group by tablespace) f
   where
        d.tablespace_name = a.tablespace_name(+)
    and d.tablespace_name = f.tablespace_name(+)
/

SHELL脚本版

#1 表空间查询慢的解决办法
#1.1 对exec DBMS_STATS.GATHER_TABLE_STATS ('SYS', 'X$KTFBUE');
#1.2 EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
#2 查询表空间时使用HINT进程查询  /*+ gather_plan_statistics */
sqlplus -S  / as sysdba << EOF
set timing on
set time on
set lines 168 pages 100
set COLSEP '|'
col tbsname for a24      heading 'TablespaceName'
col blks for 999999      heading 'Block|Size(kb)'
col initext for 999999   heading 'INITIAL|EXTENT(kb)'
col nextext for 999999   heading 'NEXT|EXTENT(kb)'
col ctnt for a4
col status for a9
col EXT_MGMT for a12
col BIGFILE for a8
col usegb for 99999.9
col MAXGB for 99999
col FREEGB for 99999.9
col FREEPCT for 999.9
SELECT /*+ gather_plan_statistics */
   d.tablespace_name tbsname,d.BLOCK_SIZE/1024 blks,d.INITIAL_EXTENT/1024 initext,d.NEXT_EXTENT/1024 nextext
  , substr(d.contents,1,4) ctnt
  , status
  , EXTENT_MANAGEMENT || '/' || SEGMENT_SPACE_MANAGEMENT ext_mgmt
  , BIGFILE
  , NVL(round(a.bytes/1073741824,1),0) usegb
  , NVL(round(a.mbytes/1073741824,1),0) maxgb
  , NVL(round(decode(contents,'TEMPORARY',a.bytes - NVL(f.bytes,0),f.bytes)/1073741824,1),0) freegb
  , NVL(round(decode(a.mbytes,0,0,(a.mbytes - a.bytes + decode(contents,'TEMPORARY',a.bytes - NVL(f.bytes,0),NVL(f.bytes,0)))/a.mbytes*100),1),0) freepct
  from
   dba_tablespaces d,
   (select tablespace_name,sum(bytes) bytes,sum(mbytes) mbytes from
      (select tablespace_name,bytes,decode(maxbytes,0,bytes,'',bytes,maxbytes) mbytes from dba_data_files union all
       select tablespace_name,bytes,decode(maxbytes,0,bytes,'',bytes,maxbytes) mbytes from dba_temp_files)
    group by tablespace_name) a,
   (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name union all
      select tablespace,sum(su.blocks * (select value from v\$parameter where name ='db_block_size')) bytes
      from sys.gv_\$sort_usage su group by tablespace) f
   where
        d.tablespace_name = a.tablespace_name(+)
    and d.tablespace_name = f.tablespace_name(+)
/    
EOF