Ask The Real Tom

February 18, 2009



Find the last object in a oracle datafile

Filed under: oracle — Tags: , , — admin @ 3:44 pm

Is always annoying to resize a datafile with empty space but some blocks occupied at the end of the file.
You get the

ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

There are may brute force ways to solve this as rebuild or move everthing in the tablespace or exp/imp.

Sometimes you just want resize one datafile to archive some monitoring limits with no impact to production.

Find the last object in the datafiles for a specific tablespace (the is slow, checkout Metalink Note 422730)

select owner,segment_name,partition_name,segment_type
from dba_extents
where (file_id,block_id) in (select file_id,max(block_id) from dba_extents group by file_id)
and tablespace_name like 'INDEX_TBSP_NAME%'

If you have the object, you can do:

Alter table move;  -- ! table unavailable, indexes need to be rebuild

or

alter index rebuild; -- index unavailable

or

alter index rebuild online; -- index available during rebuild

Powered by WordPress