1、查询表空间时间长

在查询表空间剩余空间时，常遇到执行时间过长，因为有一个DBA_FREE_SPACE 空闲空间需要统计回收站recyclebin$的信息，如下：

查看catspace.sql脚本，有一段DBA_FREE_SPACE的脚本：

[oracle@oracle11g admin]$ more $ORACLE_HOME/rdbms/admin/catspace.sql

create or replace view DBA_FREE_SPACE

(TABLESPACE_NAME,FILE_ID,BLOCK_ID,

BYTES,BLOCKS,RELATIVE_FNO)

as

select ts.name,fi.file#,f.block#,

f.length * ts.blocksize,f.length,f.file#

from sys.ts$ ts,sys.fet$ f,sys.file$ fi

where ts.ts# = f.ts#

andf.ts# = fi.ts#

andf.file# = fi.relfile#

andts.bitmapped = 0

union all

select

ts.name,f.ktfbfebno,

f.ktfbfeblks * ts.blocksize,f.ktfbfeblks,f.ktfbfefno

from sys.ts$ ts,sys.x$ktfbfe f,sys.file$fi

where ts.ts# = f.ktfbfetsn

andf.ktfbfetsn = fi.ts#

andf.ktfbfefno = fi.relfile#

andts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

select

ts.name,u.ktfbuebno,

u.ktfbueblks * ts.blocksize,u.ktfbueblks,u.ktfbuefno

from sys.recyclebin$ rb,sys.ts$ ts,sys.x$ktfbue u,sys.file$ fi

where ts.ts# = rb.ts#

andrb.ts# = fi.ts#

andu.ktfbuefno = fi.relfile#

andu.ktfbuesegtsn = rb.ts#

andu.ktfbuesegfno = rb.file#

andu.ktfbuesegbno = rb.block#

andts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

union all

select ts.name,u.block#,

u.length * ts.blocksize,u.length,u.file#

from sys.ts$ ts,sys.uet$ u,sys.file$ fi,sys.recyclebin$rb

where ts.ts# = u.ts#

andu.ts# = fi.ts#

andu.segfile# = fi.relfile#

andu.ts# = rb.ts#

andu.segfile# = rb.file#

andu.segblock# = rb.block#

andts.bitmapped = 0

2、查看回收站 dba_recyclebin 信息

sql> select count(1) from recyclebin$; 同dba_recyclebin系统表

COUNT(1)

----------

60035

3、加快查询表空间

如果不需要闪回，恢复(有备份除外)等操作(谨慎)，可以选择清空回收站，使得查询表空间加快。

sql> purge dba_recyclebin;

DBA Recyclebin purged.