How to rebuild a table which occupies the last extend in the data file, and then shrink this file.

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 = 1
union
select 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
union
select 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;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents group by file_id ) b
where 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;