Oracle数据文件收缩(二)
ablespace containing the extent
EXTENT_ID
NUMBER
Extent number in the segment
FILE_ID
NUMBER
File identifier number of the file containing the extent
BLOCK_ID
NUMBER
Starting block number of the extent
BYTES
NUMBER
Size of the extent in bytes
BLOCKS
NUMBER
Size of the extent in Oracle blocks
RELATIVE_FNO
NUMBER
Relative file number of the first extent block
[sql]
SELECT 'alter database datafile '
||chr(39)
||file_name
||chr(39)
|| ' resize '
|| ceil(hwmsize*1.2)
||'M;'
FROM
(SELECT 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
)
[sql]
alter database datafile 'F:\ORACLE\ORADATA\PURPLE\USERS_NEW' resize 5755M;
alter database datafile 'F:\ORACLE\ORADATA\PURPLE\SYSTEM01.DBF' resize 4574M;
alter database datafile 'F:\ORACLE\ORADATA\PURPLE\INDEX_NEW' resize 825M;
alter database datafile 'F:\ORACLE\ORADATA\PURPLE\USERS01.DBF' resize 17980M;
alter database datafile 'F:\ORACLE\ORADATA\PURPLE\SYSAUX01.DBF' resize 670M;
alter database datafile 'F:\ORACLE\ORADATA\PURPLE\EXAMPLE01.DBF' resize 90M;
alter database datafile 'F:\ORACLE\ORADATA\PURPLE\UNDOTBS01.DBF' resize 72M;
总共释放出7G的空间出来。