But it was not as simple as I had hoped.
I started with this initial piece of SQL.
select file_id , block_id , owner ||'.' ||segment_name ||decode(partition_name,NULL,NULL,'.'||partition_name) ||' extent ' ||extent_id object_name , bytes/1024/1024 mbytes from dba_extents where file_id = &1 union select file_id , block_id , '************* FREE *************' , bytes/1024/1024 mbytes from dba_free_space where file_id = &1 order by 1 , 2 /
The output was strange: in my locally managed tablespace there were 2 rows next to each other saying they were free. How could this be?
Well a little bit of thought made me realise the obvious; old dropped objects in the recyclebin were being displayed as free space.
Well I wanted my datafile mapper to be clever and show those recyclebin objects, so I began to dig deeper.
Firstly, dba_extents 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!
