SELECT *
FROM (SELECT /*+ ordered use_hash(a,b,c) */
a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize
FROM (SELECT file_id,
file_name,
round(bytes / 1024 / 1024) filesize
FROM dba_data_files) a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize
FROM dba_free_space dfs
GROUP BY file_id) b,
(SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize
FROM dba_extents
GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC)
WHERE file_id IN (SELECT file_id
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM')
ORDER BY file_id;
alter database datafile '/oracleDB/DATA/hebsjb/system08.dbf' resize 1G;
FROM (SELECT /*+ ordered use_hash(a,b,c) */
a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize
FROM (SELECT file_id,
file_name,
round(bytes / 1024 / 1024) filesize
FROM dba_data_files) a,
(SELECT file_id, round(SUM(dfs.bytes) / 1024 / 1024) freesize
FROM dba_free_space dfs
GROUP BY file_id) b,
(SELECT file_id, round(MAX(block_id) * 8 / 1024) HWMsize
FROM dba_extents
GROUP BY file_id) c
WHERE a.file_id = b.file_id
AND a.file_id = c.file_id
ORDER BY unsedsize_belowhwm DESC)
WHERE file_id IN (SELECT file_id
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM')
ORDER BY file_id;
alter database datafile '/oracleDB/DATA/hebsjb/system08.dbf' resize 1G;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29812751/viewspace-1811527/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29812751/viewspace-1811527/