在11g數(shù)據(jù)庫版本，隨著數(shù)據(jù)庫的使用，垃圾數(shù)據(jù)的積累，訪問dba_free_space視圖的消耗會越來越高，訪問耗時越來越長

建議采取以下措施，優(yōu)化dba_free_space視圖的訪問效率

1、重建dba_free_space視圖

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#
??and f.ts# = fi.ts#
??and f.file# = fi.relfile#
??and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
? ?? ? ts.name, fi.file#, 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
??and f.ktfbfetsn = fi.ts#
??and f.ktfbfefno = fi.relfile#
??and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
? ?? ? ts.name, fi.file#, 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#
??and rb.ts# = fi.ts#
??and u.ktfbuefno = fi.relfile#
??and u.ktfbuesegtsn = rb.ts#
??and u.ktfbuesegfno = rb.file#
??and u.ktfbuesegbno = rb.block#
??and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, 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#
??and u.ts# = fi.ts#
??and u.segfile# = fi.relfile#
??and u.ts# = rb.ts#
??and u.segfile# = rb.file#
??and u.segblock# = rb.block#
??and ts.bitmapped = 0
/


2、清空數(shù)據(jù)庫回收站

sqlplus / as sysdba
purge dba_recyclebin;

說明：在11.2.0.4上測試有效