IT运维笔记


ORACLE 收缩表空间的数据文件

方法一：

在实际的应用中经常会遇到TRUNCATE或者DELETE表中的数据后发现表空间并没有将空间进行释放，磁盘空间被告占用感觉空间白白被浪费掉了。 通过下面的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;

方法二：

oracle database用一段时间以后，硬盘空间会不够，更甚者硬盘会used 100%。（oracle表空间设置为自动扩展）。此时oracle会出现各种莫名其妙的错误，为保持oracle数据库的稳定，我们需要实时查看硬盘空间。 那么怎样缩小一些不用的表空间，以释放硬盘空间？ oracle常用的此类命令Alter database datafile（tempfile），当然也可从toad等UI工具去操作。 
ALTER DATABASE TEMPFILE '/opt/oracle/oradata/gctwp101/temp02.dbf' RESIZE 1229M;
如果盲目去操作，oracle会报ora-03297错误。表示在所定义的空间之后有数据存在，不能收缩。我们不得不查出最后的数据所处的位置， 然后表空间设置到稍比这位置大一点的大小。 1. 查该数据文件中数据处在最大位置 
select max(block_id) from dba_extents where file_id=15;
max(block_id) 383497 查询file_id,也可在toad工具tablespace直接看到。 
select file#,name from v$datafile;
  1. 查出最大块位置
select 383497*8/1024 from dual;
2996.0703125 这说明该文件中最大使用块位于2996M与3000M之间， 3. 修改表空间（也可用toad工具界面修改） 
ALTER DATABASE TEMPFILE '/opt/oracle/oradata/gctwp101/temp02.dbf' RESIZE 3000M;
京ICP备16019936号