Ask The Real Tom

Just gona post my query I just used now. It’s incomplet an dirty now.
If somebody has better version of it, please poste it.

The query just work for index tablespace (also with partitioned and subpartitioned indexes)

<code>select ‘alter ‘||decode(segment_type,’INDEX’,'INDEX’,'INDEX PARTITION’,'INDEX’,'INDEX SUBPARTITION’,'INDEX’)||’ ‘||

      owner||'.'||segment_name||' rebuild '||
      decode(segment_type,'INDEX','','INDEX PARTITION','partition','INDEX SUBPARTITION','subpartition')||' '||partition_name||';' cmd
    from rim_dba_extents
    where (file_id,block_id) in (select file_id,block_id from
                                  (select file_id,block_id from rim_dba_extents sde3
                                       where file_id in (select file_id
                                                                 from dba_data_files
                                                                 where tablespace_name like 'Tablespace_name%'
                                                                    and sde3.block_id>(select min(BLOCK_ID)+1 from dba_free_space where file_id=sde3.file_id)
                                                              )
                                   order by block_id)
                where rownum < 100)

</code>

btw: I use a copy of dba_extents becuase in 10.2.0.4 is bug which makes dba_extents extremly slow.

create table rim_dba_extents as select * from dba_extents;
drop table rim_dba_extents;

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