Got the request to reorder a table’s column order. It was/is a simple enough table: 4 columns where the last two needed to be the first two.
Look after the jump to see how it went down:
Updated!
source: B14258-02
First, check whether the table can be redefinitioned online using:
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('OWNER'
,'ORIG_TABLE'
,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
If the function completes without error, the table can be redefinioned online.
Create the interim table as needed, in this case i could reuse the creation script and alter the order of the columns.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('OWNER'
,'ORIG_TABLE'
,'INTERIM_TABLE'
,'[expression_3] COL_3
, [expression_4] COL_4
, [expression_1] COL_1
, [expression_2] COL_2'
,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
The expression can include columns from table being redefined, constants, operators, function or method calls and will define the interim column as specified in to the newly to form table. Contrary to what may be suggested in the code, the order of the column mapping pairs is unimportant.
DECLARE
NUM_ERRORS PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('OWNER'
,'ORIG_TABLE'
,'INTERIM_TABLE'
,DBMS_REDEFINITION.CONS_ORIG_PARAMS
,TRUE
,TRUE
,TRUE
,FALSE
,NUM_ERRORS
,TRUE);
END;
/
Check whether there were errors:
SELECT object_name , base_table_name , ddl_txt FROM DBA_REDEFINITION_ERRORS /
Output would be similar to this:
SQL> SELECT object_name
, base_table_name
, ddl_txt
FROM DBA_REDEFINITION_ERRORS
/
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------- ---------------- ------------------------------
SYS_C005836 ADMIN_EMP CREATE UNIQUE INDEX "HR"."TMP$
$_SYS_C0058360" ON "HR"."INT_A
DMIN_EMP" ("EMPNO")
SYS_C005836 ADMIN_EMP ALTER TABLE "HR"."INT_ADMIN_EM
P" ADD CONSTRAINT "TMP$_SYS_C
0058360" PRIMARY KEY
It is not clear to me how or why, but in my first attempt to re-order the columns, i did not use the SYNC_INTERIM_TABLE function. I just started the START_REDEF_TABLE function and finished it like it said in the examples. The resulting table kept the old order of columns, the second try, i used the SYNC_INTERIM_TABLE and the re-ordering worked.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('OWNER'
,'ORIG_TABLE'
,'INTERIM_TABLE');
END;
/
When done, finish the redefinition like so:
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('OWNER'
,'ORIG_TABLE'
,'INTERIM_TABLE');
END;
/
Voila….
Update: Well, not exactly “voila”, ’cause however the test in my own schema (with the same objects and dependencies) went allright. The actual move itself busted.
It seems one of my colleagues, implemented a ddl, which left a couple of trigers invalid.
Whadda ya know !?!, it seems i needed to those to complete my redefinition. Below are the EM’s i got:
ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-04098: trigger 'OWNER.TMP$_SOME_TRIGGER0' is invalid and failed re-validation ORA-06512: at "SYS.DBMS_REDEFINITION", line 63 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1060 ORA-06512: at line 2
Anyway, Google is your friend and indeed, i aborted the redfinition, dropped the triggers, fired up the redefinition and created them again.