Oracle 回收站清理

Oracle 回收站清理

前几天，看到落落调优了一条由于回收站过大引起的SQL问题，作为一个DBA提供一些清理回收站的SQL

 

--回收站大小

select sum(mb) from (

select * from (

select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,b.bytes/1024/1024 MB

 from dba_recyclebin a,dba_segments b where a.object_name=b.segment_name

order by MB desc) );

 

--回收站中对象的大小

select * from (

select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,b.bytes/1024/1024 MB

 from dba_recyclebin a,dba_segments b where a.object_name=b.segment_name

order by MB desc) where rownum <11;

 

--生成清理回收站SQL

select * from  (select 'purge table '||a.owner||'.'||ORIGINAL_NAME||' ;'

from dba_segments b,dba_recyclebin a where a.object_name=b.segment_name  order by b.bytes desc)

where rownum<11;

 

--直接清理整个回收站

purge dba_recyclebin;

 

--参考文献

落落调优SQL

http://blog.csdn.net/robinson1988/article/details/9312913#comments

eygle Oracle10g的回收站(recyclebin)和自由空间管理

http://www.eygle.com/archives/2006/08/recyclebin_and_free_space.html

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

转载于:http://blog.itpub.net/26442936/viewspace-769097/