We can use the following script for preparing a script for rebuilding the last table in the tablespace, its indices and analyzing all this stuff:
select comm from ( select 3,'ALTER TABLE '||owner||'.'||SEGMENT_NAME||' MOVE TABLESPACE fire_tables;' comm from (select owner, segment_name, segment_type, block_id from dba_extents where file_id = ( select ddf.file_id from dba_data_files ddf where ddf.file_name = '(file name)' ) order by block_id desc ) where rownum = 1unionselect 1,'exec dbms_stats.gather_table_stats(ownname=>'''||owner||''',tabname=>'''||segment_name||''',estimate_percent=>20,cascade=>true);' comm from (select owner, segment_name, segment_type, block_id from dba_extents where file_id = ( select ddf.file_id from dba_data_files ddf where ddf.file_name = '(file name)' ) order by block_id desc ) where rownum = 1 unionselect 2,'ALTER INDEX '||idx.owner||'.'||idx.index_name||' REBUILD TABLESPACE fire_indexes online;' comm from (select owner,SEGMENT_NAME from (select owner, segment_name, segment_type, block_id from dba_extents where file_id = ( select ddf.file_id from dba_data_files ddf where ddf.file_name = '(file name)' ) order by block_id desc ) where rownum = 1) tbs, dba_indexes idx where idx.table_name=tbs.segment_name and idx.owner=tbs.owner ) order by 1 asc;After the rebuilding the table you should figure out the block size:
select value from v$parameter where name = 'db_block_size'/Then you can execute the script which creates a script for maximum shrinking a required data file:
select 'alter database datafile ''' ||file_name|| ''' resize ' || ceil( (nvl(hwm,1)*(db block size))/1024/1024 )||'m;' cmdfrom dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) bwhere a.file_id = b.file_id(+) and ceil(blocks*(db block size)/1024/1024)-
ceil((nvl(hwm,1)*(db block size))/1024/1024 ) > 0/
Then we may increase a size of each data file to 10% if we want.
select 'alter database datafile ''' ||file_name|| ''' resize '||ceil((100+10)*ceil(bytes/1024/1024)/100)||'m;' from dba_data_files;
And then we may set up auto-extend to a size of file plus 2Gb for example:
select 'ALTER DATABASE DATAFILE '''||file_name||''' AUTOEXTEND ON NEXT 5M MAXSIZE '||to_char(bytes+2147483648)||';' from dba_data_files;