Sample Header Ad - 728x90

Finding extents of Oracle BIN$ segments

1 vote
2 answers
2821 views
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.
Asked by eckes (1456 rep)
Nov 9, 2017, 02:39 AM
Last activity: Nov 12, 2017, 01:14 AM