Hey all,
I’m working on a table redefinition project to migrate the existing tables and indexes to new compressed tablespaces. As the customer asked to have near 0 downtime to its data we decided to go with DBMS_REDEFINITION.
Simple right? Well… I sure hoped so.
I’m preparing a serie of posts about it, but before that I would like to share some hands on issues and that the magic of the new redef_table is not that great yet, at least on 12cR1/12.1.0.2.
Prior 12c, when would need to redefine a table you would use the DBMS_REDEFINITION and its 6 steps:
0 – Manually create a interim table to receive the data with the same structure as the original table
1 – DBMS_REDEFINITION.can_redef_table
2 – DBMS_REDEFINITION.start_redef_table
3 – DBMS_REDEFINITION.sync_interim_table
4 – DBMS_REDEFINITION.copy_table_dependents
5 – DBMS_REDEFINITION.finish_redef_table
And sometimes you would need to user the DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT to help on some issues but if everything was good you would only need to do the steps above.
There are a few issues with the approach by that I mean BUGS 🙂 so you need to watch your back do an a good prep work.
In 12c you have a new procedure called DBMS_REDEFINITION.redef_table that would bundle all the 6 steps into one single procedure call. With its up and down side.
For me, the down side is that we can’t monitor the procedure, once this is no loger anything being recorded on dba_redefinition_errors.
By working as a transaction, everything works or its rolled back (Or it should but I will leave it for another post).
So the only way to know what is being done is to trace the session that is doing the redefinition. And that what I needed to do to see what was going on with a strange situation.
This is what was happening: I 1st tried the DBMS_REDEFINITION.redef_table and it raised ORA-02158: invalid CREATE INDEX option but when I used the 6 steps mentioned above (can_redef_table,start_redef_table,etc) it worked without issues:
That bugged me so I traced the session.
SQL> exec dbms_monitor.session_trace_enable(binds=>true,waits=>true); SQL> BEGIN DBMS_REDEFINITION.REDEF_TABLE(uname => 'USER1', tname => 'TEST1', table_compression_type => 'COMPRESS FOR OLTP', table_part_tablespace => 'DATA_COMP', index_tablespace => 'DATA_COMP', index_key_compression_type => 'COMPRESS ADVANCED LOW', lob_compression_type => 'COMPRESS HIGH', lob_tablespace => 'DATA_COMP', lob_store_as => 'SECUREFILE'); END; / BEGIN* ERROR at line 1:ORA-02158: invalid CREATE INDEX option ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385 ORA-06512: at line 2
And this was the create index statement that was in the trace:
250438 PARSE ERROR #140737488293344:len=421 dep=1 uid=0 oct=9 lid=0 tim=3977494319094 err=2158250439 CREATE INDEX "USER1"."TMP$$_INDEX0" ON "USER1"."REDEF$_16752430_0" ("VISIT_NO")250440 PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED LOWNOLOGGING250441 STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645250442 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1250443 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)250444 TABLESPACE "DATA_COMP"
Can you see the issue?
Well neither could I, a colleague read the trace again and found a silly bug.
Here it is The create index had this:
COMPRESS ADVANCED LOWNOLOGGING
Instead of this, it had this:
COMPRESS ADVANCED LOW NOLOGGING
A silly space was missing and was causing the entire redefinition to fail!
I could not find any reference in MOS but that was it a space prevented to use the redef_table and caused me to lose some hours on it.
Hope this save you some time on your troubleshooting and I will be posting other strange situations that I found using the redef_table on Oracle 12cR1.
See you next post!
Elisson Almeida