How to 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.
Answer:
I am using below query for my issue:
---- HOW SMALL I SHRINK A DATAFILE
select tablespace_name tablespace
, file_name
, decode(actual_size,-1,'n/a (offline)',actual_size) actual
, decode(shrink_to,-1,'n/a',null,'can''t be shrinked',actual_size,'can''t be shrinked', shrink_to) shrink_to
from (
select -- locally managed files that can be resized (last used block < last free block)
d.tablespace_name
, d.file_id
, d.file_name
, d.bytes/1024 actual_size --MB
, nvl((m.maxblock-1)*t.blocksize/1024, (64*1024+t.bitmapped*t.blocksize)/1024) shrink_to
from dba_data_files d
, sys.ts$ t
, (select u.fileid file_id, max(u.block+u.length) maxblock --last used block +1
from dba_lmt_used_extents u group by fileid ) m
where m.file_id(+)=d.file_id
and d.tablespace_name =t.name
and t.bitmapped > 0
and d.bytes is not null --online
union -- Offline LMT (Offline DMT info is still available in datadict.)
select d.tablespace_name, d.file_id, d.file_name, -1, -1 /* -1 => n/a, null => cannot be resized */
from dba_data_files d
where bytes is null -- Offline LMT: Size is unknown
union -- dictionary managed (online + offline)
select ts.name, a.file#, df.name, ts.blocksize*f.blocks/1024
, case when nvl(min(a.bl-1),f.blocks) > 2 --nvl: no free space
then nvl(min(a.bl-1),f.blocks)*(ts.blocksize)/1024
else 2*(ts.blocksize)/1024 end
from (select f.ts#, f.file#, block# bl
from sys.fet$ f
start with block#=(select d.blocks-f.length+1 m from sys.file$ d where f.file#=d.relfile#)
--end of free space area = end of datafile
connect by prior block#=length+block# --if area before is also free space
) a
, v$dbfile df
, sys.file$ f
, sys.ts$ ts
where f.relfile# = a.file# -- a no rows -> no free space
and f.ts# = ts.ts#
and f.file# = df.file#
and ts.bitmapped=0 --0=dictionary managed, else=locally managed
group by ts.name, a.file#, df.name, f.blocks , ts.blocksize
)
order by tablespace_name, file_name
Notes:
If you can’t resize datafile
its better the create new tablespace and use it instead of existing one.
