点击(此处)折叠或打开
-
COL TBS_NAME FORMAT A15
-
COL USAGE_RATE FORMAT A10
-
SELECT Upper(F.TABLESPACE_NAME) "TBS_NAME",
-
D.TOT_GROOTTE_MB "TBS_MB",
-
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "USAGE_MB",
-
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
-
|| '%' "USAGE_RATE",
-
F.TOTAL_BYTES "FREE_SPACE_MB",
-
F.MAX_BYTES "MAX_BLOCK_SIZE_MB"
-
FROM (SELECT TABLESPACE_NAME,
-
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
-
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
-
FROM SYS.DBA_FREE_SPACE
-
GROUP BY TABLESPACE_NAME) F,
-
(SELECT DD.TABLESPACE_NAME,
-
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
-
FROM SYS.DBA_DATA_FILES DD
-
GROUP BY DD.TABLESPACE_NAME) D
-
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
- ORDER BY 4 DESC;
TBS_NAME TBS_MB USAGE_MB USAGE_RATE FREE_SPACE_MB MAX_BLOCK_SIZE_MB
--------------- ---------- ---------- ---------- ------------- -----------------
SYSTEM 740 738.19 99.76% 1.81 1.81
SYSAUX 540 512.94 94.99% 27.06 27.06
USERS 5 1.31 26.20% 3.69 3.69
UNDOTBS1 90 15.31 17.01% 74.69 53
点击(此处)折叠或打开
-
COL CMD FORMAT A80
-
-
select 'alter database datafile ''' || a.file_name || ''' resize ' ||
-
round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;' AS CMD,
-
a.filesize || 'M' as "TOTAL_SIZE",
-
c.hwmsize || 'M' as "HW"
-
from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
-
from dba_data_files) a,
-
(select file_id, round(max(block_id) * 8 / 1024) as HWMsize
-
from dba_extents
-
group by file_id) c
-
where a.file_id = c.file_id
- and a.filesize - c.hwmsize > 100;
CMD TOTAL_SIZE HW
alter database datafile '/KBACH1T/data/logdata01.dbf' resize 9427M; 30000M 4184M
HW表示实际有效利用的空间
接下来执行CMD命令即可。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22621861/viewspace-1590434/ ，如需转载，请注明出处，否则将追究法律责任。
