专注于数据库技术研究和实践,目前就职于互联网金融企业,提供Oracle数据库技术支持和维护。 联系电话:18616803656
分类: Oracle
2011-09-08 12:37:58
提供一个回收表空间的简单方法供参考:
通过下面的SQL语句查看表空间总大小及实用大小,然后拼出来一个SQL语句将表空间的数据文件重新设定大小
select 'alter database datafile ''' || a.file_name || ''' resize ' || round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;', a.filesize || 'M' as "数据文件的总大小", c.hwmsize || 'M' as "数据文件的实用大小" 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; |
上面的那个SQL语句运行可能是有点慢下面的语句更快:
注意:对于此SQL语句由于dba_free_space这个视图在统计空闲空间时没有考虑表空间中的数据文件自动扩展时产生的可使用空间。同时,对于分配给行的空间,在删除行以后,仍可继续用于表的插入操作,但不将其作为可用于其他数据库对象的空间算入下面SQL查询结果中,但是对于截取表时,该空间就可用于其他的数据库对象。如果不考虑数据文件扩展的情况下用此SQL语句基本上可以满足要求了,如果要很精确的话可以考虑上面SQL语句就是太慢了点。
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%' |
从网上查了一个关于回收表空间的语句:
alter tablespace TABLESPACENAME coalesce
此语句是整合表空间的碎片增加表空间的连续性,但是他不会收缩一个文件的大小的。
回收某个表使用空间的步骤:
(1)、选择某个表空间中超过N个blocks的segments,通过此语句可以看出那个表占用的空间大。
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;
执行完后再执行
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;