PT - Row Migration Using Online Redefinition

Row Migration Using Online Redefinition

• The chaining percentage (CHAIN_PCT) is not gathered using stats gathering. 

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST');

PL/SQL procedure successfully completed.

• Using Analyze CHAIN_CNT (row migration and row chaining of blocks will be detected).

SQL> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT CHAIN_CNT,

  2         ROUND(CHAIN_CNT/NUM_ROWS*100,2) CHAIN_PCT,

  3         AVG_ROW_LEN, PCT_FREE , PCT_USED, BLOCKS

  4    FROM USER_TABLES

  5  WHERE TABLE_NAME = 'CUST';

• Correcting Row Migration Using Online Redefinition

 CHAIN_CNT  CHAIN_PCT AVG_ROW_LEN   PCT_FREE   PCT_USED     BLOCKS

---------- ---------- ----------- ---------- ---------- ----------

     61267      30.63         431         10                 13036

• To solve the row migration issue by online redefinition. The script rebuilds the table in its same tablespace.

SQL> BEGIN

  2  DBMS_REDEFINITION.REDEF_TABLE(

  3   UNAME => 'SOE',

  4   TNAME => 'CUST',

  5   TABLE_PART_TABLESPACE => 'SOETBS',

  6   INDEX_TABLESPACE => 'SOETBS');

  7  END;

  8  /

PL/SQL procedure successfully completed.

SQL> ANALYZE TABLE CUST COMPUTE STATISTICS;

Table analyzed.

SQL> SELECT CHAIN_CNT,

  2         ROUND(CHAIN_CNT/NUM_ROWS*100,2) CHAIN_PCT,

  3         AVG_ROW_LEN, PCT_FREE , PCT_USED, BLOCKS

  4    FROM USER_TABLES

  5  WHERE TABLE_NAME = 'CUST';

 CHAIN_CNT  CHAIN_PCT AVG_ROW_LEN   PCT_FREE   PCT_USED     BLOCKS

---------- ---------- ----------- ---------- ---------- ----------

         0          0         429         10                 13059