must have excluded the recyclebin objects. Here's how; this is the listing from the text from DBA_VIEWS for the view DBA_EXTENTS
select ds.owner
, ds.segment_name
, ds.partition_name
, ds.segment_type
, ds.tablespace_name
, e.ext#
, f.file#
, e.block#
, e.length * ds.blocksize
, e.length
, e.file#
from sys.uet$ e
, sys.sys_dba_segs ds
, sys.file$ f
where e.segfile# = ds.relative_fno
and e.segblock# = ds.header_block
and e.ts# = ds.tablespace_id
and e.ts# = f.ts#
and e.file# = f.relfile#
and bitand(NVL(ds.segment_flags,0), 1) = 0
and bitand(NVL(ds.segment_flags,0), 65536) = 0
union all
select ds.owner
, ds.segment_name
, ds.partition_name
, ds.segment_type
, ds.tablespace_name
, e.ktfbueextno
, f.file#
, e.ktfbuebno
, e.ktfbueblks * ds.blocksize
, e.ktfbueblks
, e.ktfbuefno
from sys.sys_dba_segs ds
, sys.x$ktfbue e
, sys.file$ f
where e.ktfbuesegfno = ds.relative_fno
and e.ktfbuesegbno = ds.header_block
and e.ktfbuesegtsn = ds.tablespace_id
and ds.tablespace_id = f.ts#
and e.ktfbuefno = f.relfile#
and bitand(NVL(ds.segment_flags, 0), 1) = 1
and bitand(NVL(ds.segment_flags,0), 65536) = 0
Notice the last bitand - it is this flag that is set when the object is a dropped object.
Secondly, the objects must have been included in the view DBA_FREE_SPACE.
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
So I simply knocked out the last 2 bits of unioned SQL statements did an outer join to the SYS.RECYCLEBIN$ and hey presto I have my query.
select *
from (
select
f.file# file_id
, e.block# block_id
, case when bitand(NVL(ds.segment_flags,0), 65536) = 1
then '***-> '
||ds.owner
||'.'
||r.original_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
||'(Dropped)'
else ds.owner
||'.'
||ds.segment_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
end
||' extent '
||e.ext# object_name
, (e.length * ds.blocksize)/1024/1024 mbytes
from sys.uet$ e
, sys.sys_dba_segs ds
, sys.obj$ o left outer join sys.recyclebin$ r
on o.obj# = r.obj#
, sys.file$ f
where e.segfile# = ds.relative_fno
and e.segblock# = ds.header_block
and e.ts# = ds.tablespace_id
and e.ts# = f.ts#
and e.file# = f.relfile#
and ds.segment_objd = o.dataobj#
and bitand(NVL(ds.segment_flags,0), 1) = 0
union all
select
f.file# file_id
, e.ktfbuebno block_id
, case when bitand(NVL(ds.segment_flags,0), 65536) = 1
then '***-> '
||ds.owner
||'.'
||r.original_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
||'(Dropped)'
else ds.owner
||'.'
||ds.segment_name
||decode( ds.partition_name
,NULL
,NULL
,'.'||ds.partition_name)
end
||' extent '
||e.ktfbueextno object_name
, (e.ktfbueblks * ds.blocksize)/1024/1024 mbytes
from sys.sys_dba_segs ds
, sys.x$ktfbue e
, sys.file$ f
, sys.obj$ o left outer join sys.recyclebin$ r
on o.obj# = r.obj#
where e.ktfbuesegfno = ds.relative_fno
and e.ktfbuesegbno = ds.header_block
and e.ktfbuesegtsn = ds.tablespace_id
and ds.tablespace_id = f.ts#
and e.ktfbuefno = f.relfile#
and ds.segment_objd = o.dataobj#
and bitand(NVL(ds.segment_flags, 0), 1) = 1
) dba_extents
where dba_extents.file_id = &1
union all
select *
from (
select fi.file# file_id
, f.block# block_id
, '************* FREE *************' object_name
, (f.length * ts.blocksize)/1024/1024 mbytes
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) */
fi.file#
, f.ktfbfebno
, '************* FREE *************'
, (f.ktfbfeblks * ts.blocksize)/1024/1024
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
) dba_free_space
where dba_free_space.file_id = &1
order by 1 , 2
/
And it seems to work for me!