dba:oracle:howtos:dbms_redefinition_usage
Table of Contents
[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
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE( uname => 'SCHEMA_NAME', tname => 'TABLENAME', options_flag => DBMS_REDEFINITION.CONS_USE_PK); END; /
Step 1.2: Using ROWID
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.
Step 2.1: Using CREATE .. SELECT
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;
Step 2.2: Using DBMS_METADATA
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
Step 3.1: Using PK
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; /
Step 3.1: Using ROWID
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; /
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 DBMSREDEFINITION (l_colmap variable):
<code sql>
declare
lcolmap 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
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>
dba/oracle/howtos/dbms_redefinition_usage.txt · Last modified: 2023/01/31 08:26 by dodger