To find out total space wasted in a table
========================================
--exec as the owner of the table/sys
Select table_name,tablespace_name,status,num_rows,chain_cnt,
blocks,empty_blocks,avg_row_len,
(blocks * 8192 )/1024/1024 "Used_Size_MB",
round(((num_rows * avg_row_len)/1024/1024),2) "Acutal_Data_Size_MB",
((blocks * 8192 )/1024/1024) - (round(((num_rows * avg_row_len)/1024/1024),2)) "Space_Wasted_MB"
FROM user_tables
ORDER BY 11 DESC;
Methods to reclaim wasted space in a table
==========================================
1. Alter table table_name move tablespace users;
--Move table to any other tablespace
--Then back to the original tablespace
--Note that this operation will change rowids of the rows
because of this indexes on that table will go invalid, we need to rebuild all of them manually;
--DML,DDL cannot be done during this time.
2. Create table as select * ...
--This will create a table similar to the original one, But constraints and indexes should be taken care.
3. alter table table_name shrink space;
--execute 'alter table table_name enable row movement;'
--execute 'alter table table_name shrink space compact;'(This will rearrange the rows, this doest not required dml locks)
--execute 'alter table table_name shrink space;'(actual shrinking operation, requires locks,DML cannot perform during the time)
--Indexes remains valid
--gather stats on the table 'exec dbms_stats.gather_table_stats(TAB_OWNER, 'MYTABLE', cascade => TRUE);'
4. DBMS_REDEFINITION
--Online redefinition (does not required any down time for the table)
--create an interim table as per the requirement
--check for the eligibility to do redefinition using DBMS_REDEFINITION.can_redef_table
--start redefinition by using DBMS_REDEFINITION.start_redef_table
--Sync table with interim table by using DBMS_REDEFINITION.sync_interim_table
--Copy dependent objects (such as triggers, indexes, grants, and constraints)
using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
--finfish redefinition using DBMS_REDEFINITION.finish_redef_table
For more detail please visit:
http://avdeo.com/2012/10/28/redefining-tables-online-oracle-11g/
