--查看表空间
SELECT UPPER(D.TABLESPACE_NAME) TS_NAME,
D.TOT_GROOTTE_MB TS_SIZE,
D.TOT_GROOTTE_MB - NVL(F.TOTAL_BYTES, 0) USED_SPACE,
NVL(F.TOTAL_BYTES, 0) FREE_SPACE,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - NVL(F.TOTAL_BYTES, 0)) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99') USED_PCT
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024)) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024)) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024)) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
--and F.TABLESPACE_NAME='TBS_CRM_DUSR4'
ORDER BY 5 DESC
SYSAUX 5020 4774 246 95.10
USERS 1228 614 614 50.00
SYSTEM 3000 1035 1965 34.50
TBS_DBA 326848 48471 278377 14.83
TBS_DM 317200 15784 301416 4.98
UNDOTBS1 815 33 782 4.05
TBS_DWA 306900 3833 303067 1.25
TBS_ODS 102300 61 102239 0.06
TBS_STAGE 102300 54 102246 0.05
TBS_DIM 102300 8 102292 0.01
TBS_MID01 10240 1 10239 0.01
TBS_FACE 10240 1 10239 0.01
--查看sysaux表空间端
SELECT owner,segment_name,round(SUM(bytes)/1024/1024) seg_MB
FROM dba_extents
WHERE tablespace_name='SYSAUX'
GROUP BY owner,segment_name
ORDER BY 3 DESC
SYS WRH$_LATCH 336
SYS WRH$_ACTIVE_SESSION_HISTORY 328
SYS WRH$_EVENT_HISTOGRAM_PK 320
SYS WRH$_EVENT_HISTOGRAM 304
SYS WRH$_SQLSTAT 240
SYS WRH$_SYSSTAT_PK 232
SYS WRH$_SYSSTAT 216
SYS WRH$_PARAMETER_PK 208
SYS WRH$_LATCH_PK 200
SYS WRH$_PARAMETER 168
SYS WRH$_LATCH_MISSES_SUMMARY 152
SYS WRH$_LATCH_MISSES_SUMMARY_PK 144
SYS WRH$_SEG_STAT 128
SYS WRH$_SERVICE_STAT_PK 88
SYS WRH$_SQLSTAT_PK 80
SYS WRI$_ADV_SQLT_PLANS 72
SYS WRH$_SYSTEM_EVENT 72
XDB SYS_LOB0000069708C00025$$ 54
SYS WRH$_SEG_STAT_PK 53
--查看段详细信息
SQL> SELECT OWNER, SEGMENT_TYPE,SEGMENT_NAME,PARTITION_NAME, SUM(BYTES) / 1024 / 1024
2 FROM DBA_EXTENTS
3 WHERE OWNER = 'SYS' AND SEGMENT_NAME = 'WRH$_LATCH'
4 GROUP BY OWNER, SEGMENT_TYPE, SEGMENT_NAME, PARTITION_NAME
5 ORDER BY 1, 2, 3, 4 ;
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME SUM(BYTES)/1024/1024
SYS TABLE PARTITION WRH$_LATCH WRH$_LATCH_3284877932_0 336
SYS PARTITION_NAME WRH$_LATCH WRH$_LATCH_MXDB_MXSN .0625
--truncate 相应表
SQL> alter table WRH$_LATCH truncate partition WRH$_LATCH_3284877932_0;
Table truncated.
SELECT UPPER(D.TABLESPACE_NAME) TS_NAME,
D.TOT_GROOTTE_MB TS_SIZE,
D.TOT_GROOTTE_MB - NVL(F.TOTAL_BYTES, 0) USED_SPACE,
NVL(F.TOTAL_BYTES, 0) FREE_SPACE,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - NVL(F.TOTAL_BYTES, 0)) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99') USED_PCT
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024)) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024)) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024)) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
--and F.TABLESPACE_NAME='TBS_CRM_DUSR4'
ORDER BY 5 DESC
SYSAUX 5020 4774 246 95.10
USERS 1228 614 614 50.00
SYSTEM 3000 1035 1965 34.50
TBS_DBA 326848 48471 278377 14.83
TBS_DM 317200 15784 301416 4.98
UNDOTBS1 815 33 782 4.05
TBS_DWA 306900 3833 303067 1.25
TBS_ODS 102300 61 102239 0.06
TBS_STAGE 102300 54 102246 0.05
TBS_DIM 102300 8 102292 0.01
TBS_MID01 10240 1 10239 0.01
TBS_FACE 10240 1 10239 0.01
--查看sysaux表空间端
SELECT owner,segment_name,round(SUM(bytes)/1024/1024) seg_MB
FROM dba_extents
WHERE tablespace_name='SYSAUX'
GROUP BY owner,segment_name
ORDER BY 3 DESC
SYS WRH$_LATCH 336
SYS WRH$_ACTIVE_SESSION_HISTORY 328
SYS WRH$_EVENT_HISTOGRAM_PK 320
SYS WRH$_EVENT_HISTOGRAM 304
SYS WRH$_SQLSTAT 240
SYS WRH$_SYSSTAT_PK 232
SYS WRH$_SYSSTAT 216
SYS WRH$_PARAMETER_PK 208
SYS WRH$_LATCH_PK 200
SYS WRH$_PARAMETER 168
SYS WRH$_LATCH_MISSES_SUMMARY 152
SYS WRH$_LATCH_MISSES_SUMMARY_PK 144
SYS WRH$_SEG_STAT 128
SYS WRH$_SERVICE_STAT_PK 88
SYS WRH$_SQLSTAT_PK 80
SYS WRI$_ADV_SQLT_PLANS 72
SYS WRH$_SYSTEM_EVENT 72
XDB SYS_LOB0000069708C00025$$ 54
SYS WRH$_SEG_STAT_PK 53
--查看段详细信息
SQL> SELECT OWNER, SEGMENT_TYPE,SEGMENT_NAME,PARTITION_NAME, SUM(BYTES) / 1024 / 1024
2 FROM DBA_EXTENTS
3 WHERE OWNER = 'SYS' AND SEGMENT_NAME = 'WRH$_LATCH'
4 GROUP BY OWNER, SEGMENT_TYPE, SEGMENT_NAME, PARTITION_NAME
5 ORDER BY 1, 2, 3, 4 ;
OWNER SEGMENT_TYPE SEGMENT_NAME PARTITION_NAME SUM(BYTES)/1024/1024
SYS TABLE PARTITION WRH$_LATCH WRH$_LATCH_3284877932_0 336
SYS PARTITION_NAME WRH$_LATCH WRH$_LATCH_MXDB_MXSN .0625
--truncate 相应表
SQL> alter table WRH$_LATCH truncate partition WRH$_LATCH_3284877932_0;
Table truncated.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26179376/viewspace-1984231/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26179376/viewspace-1984231/