Datafile HWM

Cleaning up "joins gone wild" application bug. First cleaned up a mess that consumed a lot of space in tablespaces. After that had to move objects with high block_id values so the datafile size may be redused. To avoid hitting the error ORA-03297: file contains used data beyond requested RESIZE value. Alter table move may be used only the objects are not used. If you need to do this as an online operation use DBMS_REDEFINITION.

Generators I used for tablespaces containing only tables.

SELECT file_id,'alter table '||owner||'.'||segment_name||' move'||
   case when segment_type='TABLE PARTITION' then ' PARTITION '||partition_name
        when segment_type='TABLE SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id,
SEGMENT_NAME, partition_name, SEGMENT_TYPE
  FROM DBA_EXTENTS
 WHERE FILE_ID in (4,5,6,7,8)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by file_id,max_block_id desc
;

select 'alter database datafile '||file_id||' resize '||(ceil(max(block_id)*db_block_size/1024/1024/1024))||'G;'
  from dba_extents, (
    select value db_block_size 
      from v$parameter 
     where name='db_block_size' 
     )
 where FILE_ID in (4,5,6,7,8)
 group by db_block_size,file_id
 order by 1
;

Indexes went broken.

select 'alter index '||owner||'.'||index_name||' rebuild;' from all_indexes where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from all_ind_partitions where status = 'UNUSABLE' order by 1;

select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||partition_name||';' from all_ind_subpartitions where status = 'UNUSABLE' order by 1;

Tablespaces including indexes.

SELECT distinct 'alter index '||owner||'.'||segment_name||' rebuild'||
   case when segment_type='INDEX PARTITION' then ' PARTITION '||partition_name
        when segment_type='INDEX SUBPARTITION' then ' SUBPARTITION '||partition_name 
    end ||';' sq, 
MAX(BLOCK_ID) max_block_id
  FROM DBA_EXTENTS
 WHERE FILE_ID in (9,10,11,12,13)
 group by file_id,owner,SEGMENT_NAME, partition_name, SEGMENT_TYPE
 having MAX(BLOCK_ID) > 100000
 order by max_block_id desc
;

And after that datafile resize.