释放LOB对象空间

处理 LOB 大对象的解决方法

 

数据库版本:oracle10.2.0.4.0

操作系统:Red Hat Enterprise Linux AS release 4

表空间: CENTERDBT

 

巡检中发现数据库占用了系统160G的空间，但是实际上并没有存储那么多的数据量，可以根据下面的步骤，来判断，是什么占用那么多的磁盘空间。

CENTERDBT表空间占用系统160G，使用下面语句来查看表空间占用大小.

SELECT F.TABLESPACE_NAME,

       (T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",

       F.FREE_SPACE "FREE (MB)",

       T.TOTAL_SPACE "TOTAL (MB)",

       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) ||  '% ' PER_FREE

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BLOCKS *

                         (SELECT VALUE / 1024

                            FROM V$PARAMETER

                           WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE

          FROM DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) F,

       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) T

 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

 

 

查看表空间下面表的大小，可以看到NWS_NEWS表是占用数据块最多的，可以用块数*8来计算占用了多少的字节，但是这个表占用的只是2G多，很显然不是这张表。

SELECT TABLE_NAME,TABLESPACE_NAME,AVG_SPACE,BLOCKS FROM ALL_TABLES WHERE TABLESPACE_NAME='CENTERDBT' ORDER BY BLOCKS DESC;

 

 

 

 

 

有可能是因为数据库使用了LOB,CLOB大对象来存储，占用了大量的数据块。使用下面语句来查看数据库中所有的对象

select segment_name ,bytes/1024/1024 as "size(M)" from  dba_segments where tablespace_name='XXXX' and segment_type='LOBSEGMEN'

 

 

很显然SYS_LOB0000092717C00005$$这两个个对象占用了大量的数据空间，使用下面语句来查看这个对象是属于那张表的。

select * from ALL_LOBS where segment_name=’ SYS_LOB0000092717C00005$$’

 

 

经过查询，已经差不多知道这个占用数据库最大数据块的表是RPT_REPORTANNEX。

明显看到这张表有BLOB,CLOB字段的定义。

 

 

我们把RPT_REPORTANNEX的表数据清空，但是发现SYS_LOB0000092717C00005$$并没有释放，经过查询，发现释放LOB对象的方法只有exp导出表空间并删除数据文件，再用imp导入。或者使用把表空间转移到别的表空间上，来达到从新使数据文件从新统计的效果，使用下面语句。ANNEXCONTENT,ANNEXABSTRACT是表里LOB字段的名字.

ALTER TABLE RPT_REPORTANNEX MOVE TABLESPACE CENTERDBT lob(ANNEXCONTENT,ANNEXABSTRACT) store as( tablespace CENTERDBT)

 

这时候再去查看系统表空间占用率的时候，就会发现空闲释放了很多。

SELECT F.TABLESPACE_NAME,

       (T.TOTAL_SPACE - F.FREE_SPACE) "USED (MB)",

       F.FREE_SPACE "FREE (MB)",

       T.TOTAL_SPACE "TOTAL (MB)",

       (ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) ||  '% ' PER_FREE

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BLOCKS *

                         (SELECT VALUE / 1024

                            FROM V$PARAMETER

                           WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE

          FROM DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) F,

       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) T

 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

 

但是数据文件还是很大，因为虽然数据文件里面数据被释放，但是数据文件实际占用磁盘的大小并没有被释放，如果想缩小数据文件，只能使用alter tables XX resize 来修改当前数据文件的大小，因为resize释放的是系统中联系的数据块，如果中间有碎片的话，数据文件是不能够被正常resize的，这是只能使用下面的方法来查找碎片，并把碎片转移走。这期间是不会影响数据库正常运作的。

 

查看（对象索引）所对应的表名,但是要分清楚类型SYS_IL是索引对象，SYS_LOB是表的对象

Selecttable_name,index_namefromdba_lob where index_name='SYS_IL000XXX$$'

 

查看（对象）所对应的表名

select * from ALL_LOBS where segment_name='SYS_LOB000XXX$$'

 

如果查到LOB，或LT类型的表或索引，就把他们用move这个表到别的空间，

alter table XXX move …

 

或者回缩索引来解决碎片问题。

alter index index_name shrink space

 

当然了，首先是数据库要开启行迁移功能

alter table table_name enable row movement ;

 

整理好碎片之后记得要收集数据字典对象的统计信息和收集数据库中所有对象的统计信息和重建表的索引

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; 

EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;

 

查找表空间下的索引索引

select owner,segment_name,sum(bytes) from dba_segmentswhere tablespace_name

='ECONSOLE'and segment_type='INDEX'group by owner,segment_name

 

重建索引

alter index 索引名rebuildtablespace 索引表空间名storage(initial 初始值 next 扩展值)
nologging

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21907916/viewspace-712795/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21907916/viewspace-712795/