ORACLE 收縮表空間的數(shù)據(jù)文件

evan_china

在實(shí)際的應(yīng)用中經(jīng)常會(huì)遇到TRUNCATE或者DELETE表中的數(shù)據(jù)后發(fā)現(xiàn)表空間并沒有將空間進(jìn)行釋放，磁盤空間被告占用感覺空間白白被浪費(fèi)掉了。

提供一個(gè)回收表空間的簡單方法供參考：

通過下面的SQL語句查看表空間總大小及實(shí)用大小，然后拼出來一個(gè)SQL語句將表空間的數(shù)據(jù)文件重新設(shè)定大小

select 'alter database datafile ''' || a.file_name || ''' resize ' || round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;', a.filesize || 'M' as "數(shù)據(jù)文件的總大小", c.hwmsize || 'M' as "數(shù)據(jù)文件的實(shí)用大小" 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;

上面的那個(gè)SQL語句運(yùn)行可能是有點(diǎn)慢下面的語句更快：

注意：對于此SQL語句由于dba_free_space這個(gè)視圖在統(tǒng)計(jì)空閑空間時(shí)沒有考慮表空間中的數(shù)據(jù)文件自動(dòng)擴(kuò)展時(shí)產(chǎn)生的可使用空間。同時(shí)，對于分配給行的空間，在刪除行以后，仍可繼續(xù)用于表的插入操作，但不將其作為可用于其他數(shù)據(jù)庫對象的空間算入下面SQL查詢結(jié)果中，但是對于截取表時(shí)，該空間就可用于其他的數(shù)據(jù)庫對象。如果不考慮數(shù)據(jù)文件擴(kuò)展的情況下用此SQL語句基本上可以滿足要求了，如果要很精確的話可以考慮上面SQL語句就是太慢了點(diǎn)。

select a.tablespace_name, a.file_name, a.totalsize, b.freesize, 'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' || round((a.totalsize - b.freesize) + 200) || 'M;' as "alter datafile" from (select a.file_name, a.file_id, a.tablespace_name, a.bytes / 1024 / 1024 as totalsize from dba_data_files a) a, (select b.tablespace_name, b.file_id, sum(b.bytes / 1024 / 1024) as freesize from dba_free_space b group by b.tablespace_name, b.file_id) b where a.file_id = b.file_id and b.freesize > 100 and a.tablespace_name not like 'UNDO%'

從網(wǎng)上查了一個(gè)關(guān)于回收表空間的語句：

alter tablespace TABLESPACENAME coalesce

此語句是整合表空間的碎片增加表空間的連續(xù)性，但是他不會(huì)收縮一個(gè)文件的大小的。

回收某個(gè)表使用空間的步驟：

（1）、選擇某個(gè)表空間中超過N個(gè)blocks的segments，通過此語句可以看出那個(gè)表占用的空間大。

select segment_name,segment_type,blocks from dba_segments

where tablespace_name='TABLESPACENAME'

and blocks > N

order by blocks;

（2）、分析表，得知表的一些信息

analyze table TABLENAME estimate statistics;

執(zhí)行完后再執(zhí)行

select initial_extent,next_extent,min_extents,blocks,empty_blocks from dba_tables

where table_name='HISHOLDSINFO' and owner='hs_his';

（3）、使用alter table ... deallocate unused 命令回收表的空間

例如： alter table hs_his.HISHOLDSINFO' deallocate unused keep 1k;