[HOWTO] DBMS_REDEFINITION usage

Previous Requirements

Name of te table to compact/redefine/reindex…

Official documentation

https://docs.oracle.com/database/121/ARPLS/d_redefi.htm
(Oracle can move this document at any time)

Step 1: check if DBMS_REDEFINITION can be used

There are multiple ways to use DBMS_REDEFINITION. I mainly use 2:

  • Using PK
  • Using ROWID


I've been always be able to finish the whole process with any of this two methods.

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
   uname        => 'SCHEMA_NAME',
   tname        => 'TABLENAME',
   options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
   uname        => 'SCHEMA_NAME',
   tname        => 'TABLENAME',
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

Step 2: Creating pivot table

Or interim as named in oracle. I prefer to use DBMS_METADATA to control the 100% of the process.

This is the most basic way to create the pivot table:

CREATE TABLE SCHEMA_NAME.INT_V AS SELECT * FROM SCHEMA_NAME.TABLENAME WHERE 1 = 2;

Obtain metadata:

SELECT DBMS_METADATA.GET_DDL('TABLE','TABLENAME', 'SCHEMA_NAME') FROM DUAL ;

Then modify the table name and if there's any LOB defined as BASICFILE, re-define it as SECUREFILE (default in 11g and onward).

Step 3: begin redefinition

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
   uname        => 'SCHEMA_NAME',
   orig_table   => 'TABLENAME',
   int_table    => 'INTM_TABLENAME',
   col_mapping  => NULL,
   options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
   uname        => 'SCHEMA_NAME',
   orig_table   => 'TABLENAME',
   int_table    => 'INTM_TABLENAME',
   col_mapping  => NULL,
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

If the table has virtual columns you'll obtain a nice:

ORA-01733: virtual column not allowed here

When using DBMS_REDEFINITION. A little of tunning must be done while redefining the table.

Is necessary to specify the NON-VIRTUAL columns and pass them to DBMS_REDEFINITION (l_colmap variable):

DECLARE
    l_colmap VARCHAR(512);
BEGIN
    l_colmap := 'COLUMN1, COLUMN2, COLUMN3';
 
    dbms_redefinition.start_redef_table
    (  
   uname        => 'SCHEMA_NAME',
   orig_table   => 'TABLENAME',
   int_table    => 'INTM_TABLENAME',
      col_mapping    => l_colmap,
      options_flag => DBMS_REDEFINITION.cons_use_rowid );
END;
/

Step 4: copy table objects

DECLARE
num_errors PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA_NAME', 'TABLENAME','INTM_TABLENAME',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
    dbms_output.put_line('Num_errors = ' || num_errors);
END;
/

Then check the errors:

SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS;

If there're index/pk's/fk's/constraints you'll see errors there cause DBMS_REDEFENITION will try to do it twice.

Step 5: Sync tables

BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA_NAME', 'TABLENAME', 'INTM_TABLENAME');
END;
/

Step 6: FINISH IT!

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA_NAME', 'TABLENAME', 'INTM_TABLENAME');
END;
/

Example

SYS@BAVEL12R_1> 
 
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
   uname        => 'VOXEL',
   tname        => 'SUPERTABLE_H',
   options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-12089: cannot online redefine TABLE "DODGER"."SUPERTABLE_H" WITH no PRIMARY KEY
ORA-06512: at "SYS.DBMS_REDEFINITION", line 173
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3759
ORA-06512: at line 2
 
SYS@BAVEL12R_1> 
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
   uname        => 'VOXEL',
   tname        => 'SUPERTABLE_H',
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
  7  /
 
PL/SQL PROCEDURE successfully completed.
 
SYS@BAVEL12R_1> SELECT DBMS_METADATA.GET_DDL('TABLE', 'SUPERTABLE_H', 'VOXEL') TXT FROM DUAL ;
 
TXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
  CREATE TABLE "DODGER"."SUPERTABLE_H"
   (    "INTID_FAMPROV" NUMBER(19,0) NOT NULL ENABLE,
        "INTID_PROVEEDOR" NUMBER(19,0) NOT NULL ENABLE,
        "STRID_FAMILIA" VARCHAR2(128),
        "STRFAMILIA" VARCHAR2(128),
        "INTID_FAMILIA" NUMBER(19,0) NOT NULL ENABLE,
        "INTID_SUBFAMILIA" NUMBER(19,0) NOT NULL ENABLE,
        "TSMODIFICADO" TIMESTAMP (6) NOT NULL ENABLE,
        "INTMODIFIED_BY" NUMBER(19,0) NOT NULL ENABLE,
        "LOGACTIVE" NUMBER(19,0) NOT NULL ENABLE,
        "TSCHANGED" TIMESTAMP (6) WITH TIME ZONE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "DODGER"  ENABLE ROW MOVEMENT ;
 
 
1 ROW selected.
 
SYS@BAVEL12R_1> 
 
CREATE TABLE "DODGER"."SUPERTABLE_REDEF"
   (    "INTID_FAMPROV" NUMBER(19,0) NOT NULL ENABLE,
        "INTID_PROVEEDOR" NUMBER(19,0) NOT NULL ENABLE,
        "STRID_FAMILIA" VARCHAR2(128),
        "STRFAMILIA" VARCHAR2(128),
        "INTID_FAMILIA" NUMBER(19,0) NOT NULL ENABLE,
        "INTID_SUBFAMILIA" NUMBER(19,0) NOT NULL ENABLE,
        "TSMODIFICADO" TIMESTAMP (6) NOT NULL ENABLE,
        "INTMODIFIED_BY" NUMBER(19,0) NOT NULL ENABLE,
        "LOGACTIVE" NUMBER(19,0) NOT NULL ENABLE,
        "TSCHANGED" TIMESTAMP (6) WITH TIME ZONE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 15   TABLESPACE "DODGER"  ENABLE ROW MOVEMENT ;
 
TABLE created.
 
SYS@BAVEL12R_1> 
 
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
   uname        => 'VOXEL',
   orig_table   => 'SUPERTABLE_H',
   int_table    => 'SUPERTABLE_REDEF',
   col_mapping  => NULL,
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
  9  /
 
 
PL/SQL PROCEDURE successfully completed.
 
SYS@BAVEL12R_1> SYS@BAVEL12R_1> 
SYS@BAVEL12R_1> 
 
DECLARE
num_errors PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('VOXEL', 'SUPERTABLE_H','SUPERTABLE_REDEF',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
    dbms_output.put_line('Num_errors = ' || num_errors);
END;
  8  /
 
Num_errors = 7
 
PL/SQL PROCEDURE successfully completed.
 
SYS@BAVEL12R_1> SYS@BAVEL12R_1> 
SYS@BAVEL12R_1> r
  1* SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS
 
OBJECT_NAME                         BASE_TABLE_NAME                          DDL_TXT
----------------------------------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_C0017232                        SUPERTABLE_H                  ALTER TABLE "DODGER"."SUPERTABLE_REDEF" MODIFY ("INTID_FAMPROV" CONSTRAINT "TMP$$_SYS_C00172320" NOT NULL ENABLE NOVALIDATE)
SYS_C0017233                        SUPERTABLE_H                  ALTER TABLE "DODGER"."SUPERTABLE_REDEF" MODIFY ("INTID_PROVEEDOR" CONSTRAINT "TMP$$_SYS_C00172330" NOT NULL ENABLE NOVALIDATE)
SYS_C0017234                        SUPERTABLE_H                  ALTER TABLE "DODGER"."SUPERTABLE_REDEF" MODIFY ("INTID_FAMILIA" CONSTRAINT "TMP$$_SYS_C00172340" NOT NULL ENABLE NOVALIDATE)
SYS_C0017238                        SUPERTABLE_H                  ALTER TABLE "DODGER"."SUPERTABLE_REDEF" MODIFY ("LOGACTIVE" CONSTRAINT "TMP$$_SYS_C00172380" NOT NULL ENABLE NOVALIDATE)
SYS_C0017236                        SUPERTABLE_H                  ALTER TABLE "DODGER"."SUPERTABLE_REDEF" MODIFY ("TSMODIFICADO" CONSTRAINT "TMP$$_SYS_C00172360" NOT NULL ENABLE NOVALIDATE)
SYS_C0017237                        SUPERTABLE_H                  ALTER TABLE "DODGER"."SUPERTABLE_REDEF" MODIFY ("INTMODIFIED_BY" CONSTRAINT "TMP$$_SYS_C00172370" NOT NULL ENABLE NOVALIDATE)
SYS_C0017235                        SUPERTABLE_H                  ALTER TABLE "DODGER"."SUPERTABLE_REDEF" MODIFY ("INTID_SUBFAMILIA" CONSTRAINT "TMP$$_SYS_C00172350" NOT NULL ENABLE NOVALIDATE)
 
7 ROWS selected.
 
SYS@BAVEL12R_1> 
BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('VOXEL', 'SUPERTABLE_H','SUPERTABLE_REDEF');
END;
/
 
 
PL/SQL PROCEDURE successfully completed.
 
SYS@BAVEL12R_1> 
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('VOXEL', 'SUPERTABLE_H','SUPERTABLE_REDEF');
END;
  4  /
 
PL/SQL PROCEDURE successfully completed.
 
SYS@BAVEL12R_1>
  • oracle/dbms_redefinition_usage.txt
  • Last modified: 2018/04/27 10:53
  • by dodger