Useful shortcuts for vi editor

Tuesday 29 August 2017

e.g. shrink table usage

Steps:
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics

SQL>
SELECT owner,
       table_name,
       ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
  FROM dba_tables
 WHERE table_name = 'EMPLOYEES';
-- Check table actual size

SQL>
  SELECT table_name,
         ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
         ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
         (  ROUND ( (blocks * 8) / 1024, 2)
          - ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
            "wasted_space (MB)"
    FROM dba_tables
   WHERE     (ROUND ( (blocks * 8), 2) >
                 ROUND ( (num_rows * avg_row_len / 1024), 2))
         AND table_name = 'EMPLOYEES'
         AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size

SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation

SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix

SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands 

SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation

SQL>
  SELECT table_name,
         ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
         ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
         (  ROUND ( (blocks * 8) / 1024, 2)
          - ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
            "wasted_space (MB)"
    FROM dba_tables
   WHERE     (ROUND ( (blocks * 8), 2) >
                 ROUND ( (num_rows * avg_row_len / 1024), 2))
         AND table_name = 'EMPLOYEES'
         AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again

Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html