oracle tablespace verkleinern / resize smaller

get minimum size of files / minimalgroesse der Datei:

select f.file_name, (t.block_size*max(e.block_id)/1024/1024) MB
from dba_tablespaces t, dba_data_files f, dba_extents e
where e.tablespace_name = 'SYSAUX'
and   e.tablespace_name = f.tablespace_name
and e.tablespace_name = t.tablespace_name
group by f.file_name, t.block_size;

(from http://www.informatik-aktuell.de/betrieb/datenbanken/der-oracle-sysaux-tablespace-reorganisation-und-verkleinerung.html)

alter database datafile '' resize  ;

durch reorganisieren des frgamentierten Files kann die minimalgroesse herabgesetzt werden - the minimal size can be reduced by reorganicing the fragmented tablespace:

1. get objects at end of file (first rows):
   set pagesize 3000
   set linesize 3000
   select e.file_id, max(e.block_id),
   e.owner, e.segment_name, segment_type, partition_name
   from dba_extents e
   where e.tablespace_name = 'SYSAUX'
   group by e.file_id, e.owner, e.segment_name, segment_type, partition_name
   order by 1 desc , 2 desc;
2. reorganize object blocking end of file:
   alter table owner.tablename move;
   alter Index rebuild;
    
3. get minimum size of file shrink:
   select f.file_name, (t.block_size*max(e.block_id)/1024/1024) MB
   from dba_tablespaces t, dba_data_files f, dba_extents e
   where e.tablespace_name = 'SYSAUX'
   and e.tablespace_name = f.tablespace_name
   and e.tablespace_name = t.tablespace_name
   group by f.file_name, t.block_size;
4. shrink file:
   alter database datafile 'C:\ORA_DAT\myfile.DBF' resize 4500M;