DBMS_REDEFINITION- DeFragamentation

 Defragamentation of Tables  in database

https://oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1

https://medium.com/@dmitry.romanoff/how-to-check-fragmentation-in-an-oracle-database-a575b8a6e2ea

Calculate Fragmented Space in Tables

Check Db_Block_Size  parameter , here it is 8 KB.

SELECT      
  table_name,     
  avg_row_len, num_rows,    
  ROUND(((blocks*8/1024)),2) || 'MB' AS "TOTAL_SIZE",     
  ROUND((num_rows*avg_row_len/1024/1024),2) || 'MB' AS "ACTUAL_SIZE",     
  ROUND(((blocks*8/1024) - (num_rows*avg_row_len/1024/1024)),2) || 'MB' AS "FRAGMENTED_SPACE",     
  (ROUND(((blocks*8/1024) - (num_rows*avg_row_len/1024/1024)),2) / ROUND(((blocks*8/1024)),2)) * 100 AS "PERCENTAGE" 
FROM user_tables  
WHERE ROUND(((blocks*8/1024)),2) > 0 
ORDER BY 6 DESC;

Once you’ve identified fragmentation, consider the following methods to address it:

ALTER TABLE YOUR_TABLE_NAME MOVE;

ALTER INDEX YOUR_INDEX_NAME REBUILD;

For large tables, consider using online redefinition to minimize downtime:

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE (
      uname => 'COMMON', orig_table => 'CITY', int_table => 'CITY_TMP');
END;
EXEC DBMS_REDEFINITION.sync_interim_table('COMMON', 'CITY', 'CITY_TMP'); 
ALTER TABLE COMMON.CITY_TMP ADD (CONSTRAINT CITY_pk2 PRIMARY KEY (CITY_ID));
EXEC DBMS_REDEFINITION.finish_redef_table('COMMON', 'CITY', 'CITY_TMP'); 

Online Table Redefinition Including Dependents (COPY_TABLE_DEPENDENTS)

The above process drops the dependent Trigger , indexes on the table.

So we use dbms_redefinition.copy_table_dependents   to copy dependent objects like triggers, indexes, constraints, grants and optionally its statistics and materialized view logs.

-- Copy dependents.

SET SERVEROUTPUT ON

DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname               => 'TEST',
    orig_table          => 'REDEF_TAB',
    int_table           => 'REDEF_TAB2',
    copy_indexes        => 1,             -- Default
    copy_triggers       => TRUE,          -- Default
    copy_constraints    => TRUE,          -- Default
    copy_privileges     => TRUE,          -- Default
    ignore_errors       => FALSE,         -- Default
    num_errors          => l_num_errors,
    copy_statistics     => FALSE,         -- Default
    copy_mvlog          => FALSE);        -- Default
    
  DBMS_OUTPUT.put_line('num_errors=' || l_num_errors); 
END;
/