Reorg Script for Oracle Tables Within the Same Tablespace

 -- Ensure you're logged in as the appropriate user

-- 1. Define variables for schema and table names

DEFINE schema_name = 'YOUR_SCHEMA_NAME';

DEFINE table_name = 'YOUR_TABLE_NAME';

-- 2. Check the status and size of the table before reorganization

SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len

FROM dba_tables

WHERE owner = '&schema_name'

AND table_name = '&table_name';

-- 3. Move the table within the same tablespace to reorganize it

-- No tablespace specified, it will remain in the same tablespace

ALTER TABLE &schema_name..&table_name MOVE;

-- 4. Rebuild indexes associated with the table

-- Get all indexes associated with the table

SELECT 'ALTER INDEX ' || index_name || ' REBUILD;' 

FROM dba_indexes 

WHERE owner = '&schema_name'

AND table_name = '&table_name';

-- 5. Gather statistics for the table after reorganization

EXEC DBMS_STATS.GATHER_TABLE_STATS('&schema_name', '&table_name');

-- 6. Check the table status and size after reorganization

SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len

FROM dba_tables

WHERE owner = '&schema_name'

AND table_name = '&table_name';

-- 7. Optionally, shrink the table space after reorganization (use with caution)

ALTER TABLE &schema_name..&table_name SHRINK SPACE CASCADE;

-- 8. If partitioned, handle partitions separately

-- ALTER TABLE &schema_name..&table_name MOVE PARTITION partition_name;