====== [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 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>