---------------------------------
Background Knowledge
---------------------------------
On oracle 10g, if flash drop enable, when drop table, table will not physically drop but put in recyclebin,
of which space will reclaim if no enough space. when table is drop, the segment space will become free space,
that is, this part of free space will be included in the result of query dba_free_space

---------
Issiue
---------
Querying DBA_FREE_SAPCE for tablespace Usage takes long time

 

 

1 Run tbs usage query sql takes long time

set linesize 180
select * from
(select
d.tablespace_name,space1 "Total", space "sum_space(M)",
space-nvl(free_space,1) "used_space(M)",
space1-(space-nvl(free_space,0)) "free_space(M)",
round((space-nvl(free_space,1))/space1*100,2) "used_rate(%)"
from
(select
        tablespace_name,
        round(sum(decode(maxbytes,0,bytes,maxbytes))/(1024*1024),2) space1,
        round(sum(bytes)/(1024*1024),2) space
from dba_data_files
group by tablespace_name) d,
(select
    tablespace_name,
    round(sum(bytes)/(1024*1024),2) free_space
from dba_free_space
group by tablespace_name) f
where d.tablespace_name=f.tablespace_name(+)
order by round((space-nvl(free_space,1))/space1*100,2) desc ) where rownum<10
/

9 rows selected.

Elapsed: 00:02:19.53     --take 2 mins

2 Check dba_recyclebin

SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
     18945

3 Purge reycyclebin

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Elapsed: 00:17:23.71

4 Run tbs usage query sql again

9 rows selected.

Elapsed: 00:00:00.46      --less than 1s

5 About dba_free_space and dba_data_files View

--DBA_FREE_VIEW: Involve --recyclebin$
SQL> set long 999999999
SQL> set pages 99
SQL> select text from dba_views where view_name='DBA_FREE_SPACE';

TEXT
--------------------------------------------------------------------------------
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                     --recyclebin$
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                        --recyclebin$
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
 
--DBA_DATA_FILES: No recyclebin$

SQL> select text from dba_views where view_name='DBA_DATA_FILES';

TEXT
--------------------------------------------------------------------------------
select v.name, f.file#, ts.name,
       ts.blocksize * f.blocks, f.blocks,
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
       ts.blocksize * f.maxextend, f.maxextend, f.inc,
       ts.blocksize * (f.blocks - 1), f.blocks - 1,
       decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
         decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
  and f.spare1 is NULL
  and f.ts# = ts.ts#
  and fe.fenum = f.file#
union all
select
       v.name,f.file#, ts.name,
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#,
       decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
       decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
         decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
  and f.spare1 is NOT NULL
  and v.file# = hc.ktfbhcafno
  and hc.ktfbhctsn = ts.ts#
  and fe.fenum = f.file#