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 ;
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
---------- ---------- ---------- ---------- ---------- ----------
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;
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
...
---------- ---------- ---------- ------------------------------ ----------
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]
1271178.1]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24756186/viewspace-757945/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
aa
请登录后发表评论
登录
全部评论
