Oracle – Finding Extents of BIN$ Segments

flashbackoracleshrink

I am looking for information on recyclebin extents.

When dropping a table its segments are renamed to 'BIN$…' system generated names and the extents are hidden.

Will those extents still stop me from shrinking a data file (or tablespace)? If so I would like to account for them in the script.

Currently I am using a query inspired by AskTom's maxshrink.sql:

select tbs.TABLESPACE_NAME, df.FILE_NAME,
    round(MAX(e.BLOCKS+e.BLOCK_ID+1)*tbs.BLOCK_SIZE/1024/1024,2) "MinMB",
    round(MAX(df.BYTES)/1024/1024,2) "FileMB"
  from DBA_TABLESPACES tbs
  LEFT JOIN DBA_DATA_FILES df on tbs.TABLESPACE_NAME = df.TABLESPACE_NAME
  LEFT JOIN DBA_EXTENTS e on df.FILE_ID = e.FILE_ID AND df.RELATIVE_FNO = e.RELATIVE_FNO
  -- WHERE
  --   tbs.TABLESPACE_NAME like 'MY%'
  GROUP BY df.FILE_ID, df.RELATIVE_FNO, tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE, df.FILE_NAME
  ORDER BY 1,2;

Update: Initially my question also asked why I no longer see the 'BIN$' segments created by Flashback Drop Table anymore in 12.2 anymore. However that was an error on my side, my test tables had simply no segments to begin with.

Best Answer

create table bp.t1 as select * from dba_users;
drop table bp.t1;

select owner, object_name, original_name from dba_recyclebin where owner = 'BP';

OWNER OBJECT_NAME                     ORIGINAL_NAME
----- ------------------------------- -------------
BP    BIN$XY0E/EKVFyXgU0cBqMCr3Q==$0  T1           

select * from dba_extents where owner = 'BP';

no rows selected

Removing (commenting out) the filter from the relevant part of the definition of DBA_EXTENTS that filters dropped objects:

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
  and ds.owner = 'BP';

OWNER SEGMENT_NAME                   PARTITION_NAME SEGMENT_TYPE       TABLESPACE_NAME KTFBUEEXTNO      FILE#  KTFBUEBNO E.KTFBUEBLKS*DS.BLOCKSIZE KTFBUEBLKS  KTFBUEFNO
----- ------------------------------ -------------- ------------------ --------------- ----------- ---------- ---------- ------------------------- ---------- ----------
BP    BIN$XY0E/EKVFyXgU0cBqMCr3Q==$0                TABLE              USERS                     0          4        128                     65536          8          4
Related Question