finding high watermark on a datafile
Guess2Jan 30 2012 — edited Jan 30 2012I am trying to resize some of my datafiles and make them small. When I do this, I get several 'ORA-03297: file contains used data beyond requested RESIZE value
how do i figure out where the high watermark is on a datafile? So I know how much I can shrink it?
how do I figure out what object(s) are near the watermark?
When I look in dba_extents, I see
file_id,tablespace_name,file_id,partiiton_name: So I can figure out which objects are in which data_file
I see a block_id. Not sure how to use that to do this. Where do I go from here?
This way I don't have to do trial and error. I can also either move some objects to a new tablespace, shrink the datafile, move some back. Or drop and re-create an index, etc...