Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:howtos:dbms_redefinition_usage

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
dba:oracle:howtos:dbms_redefinition_usage [2023/01/31 08:26] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:howtos:dbms_redefinition_usage [2023/01/31 08:26] (current) – ↷ Page moved from dba:oracle:dbms_redefinition_usage to dba:oracle:howtos:dbms_redefinition_usage dodger
Line 1: Line 1:
 +====== [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.
 +
 +
 +===== Step 1.1: Check Using PK =====
 +
 +<code sql>
 +BEGIN
 +  DBMS_REDEFINITION.CAN_REDEF_TABLE(
 +   uname        => 'SCHEMA_NAME',
 +   tname        => 'TABLENAME',
 +   options_flag => DBMS_REDEFINITION.CONS_USE_PK);
 +END;
 +/
 +</code>
 +
 +
 +===== Step 1.2: Using ROWID =====
 +<code sql>
 +BEGIN
 +  DBMS_REDEFINITION.CAN_REDEF_TABLE(
 +   uname        => 'SCHEMA_NAME',
 +   tname        => 'TABLENAME',
 +   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
 +END;
 +/
 +</code>
 +
 +
 +====== Step 2: Creating pivot table ======
 +Or interim as named in oracle.
 +I prefer to use DBMS_METADATA to control the 100% of the process.
 +
 +===== Step 2.1:  Using CREATE .. SELECT =====
 +
 +This is the most basic way to create the pivot table:
 +<code sql>
 +create table SCHEMA_NAME.INT_V as select * from SCHEMA_NAME.TABLENAME WHERE 1 = 2;
 +</code>
 +
 +
 +===== Step 2.2: Using DBMS_METADATA =====
 +Obtain metadata:
 +<code sql>
 +SELECT DBMS_METADATA.GET_DDL('TABLE','TABLENAME', 'SCHEMA_NAME') FROM DUAL ;
 +</code>
 +
 +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 ======
 +===== Step 3.1: Using PK =====
 +
 +<code sql>
 +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;
 +/
 +</code>
 +
 +===== Step 3.1: Using ROWID =====
 +
 +<code sql>
 +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;
 +/
 +</code>
 +
 +===== Step 3.1: Using ROWID with virtual columns=====
 +
 +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):
 +
 +<code sql>
 +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;
 +/
 +</code>
 +
 +====== Step 4: copy table objects======
 +
 +<code sql>
 +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;
 +/
 +</code>
 +
 +Then check the errors:
 +
 +<code sql>
 +select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
 +</code>
 +
 +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 ======
 +
 +<code sql>
 +BEGIN 
 +  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA_NAME', 'TABLENAME', 'INTM_TABLENAME');
 +END;
 +/
 +</code>
 +
 +====== Step 6: FINISH IT! ======
 +
 +<code sql>
 +BEGIN
 +  DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA_NAME', 'TABLENAME', 'INTM_TABLENAME');
 +END;
 +/
 +</code>
 +
 +
 +====== Example ======
 +
 +<code sql>
 +
 +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> 
 +
 +</code>
 +