use of sysaux tablespace

SQL> SELECT D.TABLESPACE_NAME,
  2  SPACE || 'M' "SUM_SPACE(M)",
  3  BLOCKS "SUM_BLOCKS",
  4  SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
  5  ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
  6  "USED_RATE(%)",
  7  FREE_SPACE || 'M' "FREE_SPACE(M)"
  8  FROM (  SELECT TABLESPACE_NAME,
  9  ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
 10  SUM (BLOCKS) BLOCKS
 11  FROM DBA_DATA_FILES
 12  GROUP BY TABLESPACE_NAME) D,
 13  (SELECT TABLESPACE_NAME,
 14  ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
 15  FROM DBA_FREE_SPACE
 16  GROUP BY TABLESPACE_NAME) F
 17  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.TABLESPACE_NAME='SYSAUX'
 18  ;

TABLESPACE SUM_SPACE( SUM_BLOCKS USED_SPACE USED_RATE( FREE_SPACE
---------- ---------- ---------- ---------- ---------- ----------
SYSAUX     380M            48640 331.56M    87.25%★     48.44M

 

 

Run the following SQL to list which Segments are using the more space in the SYSAUX TableSpace

 

SQL> SELECT  
  2    ROUND(SUM(ds.bytes)/1024/1024,0) as "SgmntSize",
  3    ds.TableSpace_name as "TSname",
  4    ds.owner as "SgmntOwner",
  5    ds.segment_name as "SgmntName",
  6    ds.segment_type as "SgmntType"
  7  FROM dba_segments ds
  8  WHERE ds.segment_type IN ('TABLE','INDEX')
  9  AND TableSpace_name = 'SYSAUX'
 10  and ds.bytes>1024*1024
 11  GROUP BY
 12    ds.TableSpace_name,
 13    ds.owner,
 14    ds.segment_name,
 15    ds.segment_type
 16  ORDER BY "SgmntSize" DESC;

 SgmntSize TSname     SgmntOwner SgmntName                      SgmntType
---------- ---------- ---------- ------------------------------ ----------
         9 SYSAUX     SYS        WRH$_SYSMETRIC_HISTORY         TABLE
         6 SYSAUX     SYS        WRH$_SQL_PLAN                  TABLE
         5 SYSAUX     SYSMAN     MGMT_METRICS_RAW_PK            INDEX
         5 SYSAUX     SYS        WRH$_SYSMETRIC_HISTORY_INDEX   INDEX ★
         5 SYSAUX     SYS        I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX ★
         4 SYSAUX     SYSMAN     MGMT_SEVERITY                  TABLE
         4 SYSAUX     SYS        WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE ★
         4 SYSAUX     SYS        WRH$_SYSMETRIC_SUMMARY         TABLE
         3 SYSAUX     MDSYS      SDO_CS_SRS                     TABLE
         3 SYSAUX     SYS        WRH$_WAITCLASSMETRIC_HISTORY   TABLE
         3 SYSAUX     SYS        I_WRI$_OPTSTAT_H_ST            INDEX
...

 

Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes [ID
1271178.1]

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756186/viewspace-757945/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756186/viewspace-757945/