Shrink and Reclaim Space Oracle

--Script 1 

This will shrink and claim space from table and indexes.. 

select OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, BLOCKS_MB, 'REM '|| rownum||' / '||TOT|| ' ; '||CHR(10) ||SCRIPT SCRIPT from (  

    SELECT

    t.OWNER,    t.TABLE_NAME,    t.NUM_ROWS,    t.BLOCKS,    t.BLOCKS / 1024 AS blocks_mb, COUNT(*) OVER () AS TOT,  

    'ALTER TABLE ' || t.OWNER || '.' || t.TABLE_NAME || ' ENABLE ROW MOVEMENT ; ' || CHR(10) ||

    'ALTER TABLE ' || t.OWNER || '.' || t.TABLE_NAME || ' MOVE ONLINE; ' || CHR(10) ||

    LISTAGG('ALTER INDEX ' || I.OWNER || '.' || I.INDEX_NAME || ' REBUILD ONLINE;', CHR(10)) WITHIN GROUP (ORDER BY I.INDEX_NAME) || CHR(10) ||

    'ALTER TABLE ' || t.OWNER || '.' || t.TABLE_NAME || ' SHRINK SPACE CASCADE ; ' || CHR(10) ||

    'ALTER TABLE ' || t.OWNER || '.' || t.TABLE_NAME || ' DISABLE ROW MOVEMENT ; ' AS SCRIPT

FROM        dba_tables t

JOIN        dba_indexes I ON I.TABLE_OWNER = t.OWNER AND I.TABLE_NAME = t.TABLE_NAME

WHERE       t.TABLESPACE_NAME = 'PERFSTAT'

GROUP BY    t.OWNER, t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS

ORDER BY    BLOCKS_MB desc

);

Limitations:

1. Rebuild online, alter table move online are EE options, remove online for SE databases. 

2. Table columns  with data type long will not be moved with alter table move 

--Script 2

Reclaim Space from datafiles 

Generate alter database resize datafile script. 

SELECT 'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' ||

       CEIL((NVL(b.hwm, 1) * t.block_size) / 1024 / 1024) || 'M;' AS resize_command,

       t.tablespace_name,

       a.file_name,

       a.bytes / 1024 / 1024 AS current_size_mb,

       CEIL((NVL(b.hwm, 1) * t.block_size) / 1024 / 1024) AS minimum_resize_mb,

       (a.bytes - (NVL(b.hwm, 1) * t.block_size)) / 1024 / 1024 AS potential_reclaimable_mb

FROM   dba_data_files a

JOIN   dba_tablespaces t ON a.tablespace_name = t.tablespace_name

LEFT JOIN (SELECT file_id, MAX(block_id + blocks - 1) hwm

           FROM   dba_extents

           GROUP BY file_id) b ON a.file_id = b.file_id

WHERE  (a.blocks * t.block_size) - (NVL(b.hwm, 1) * t.block_size) > 0

ORDER BY potential_reclaimable_mb DESC;