I’ve been learning about online table redefinition in Oracle 12c. Here is a zip of some test scripts I built for my own understanding: zip
I spent some time trying out DBMS_REDEFINITION on an 11.2 Exadata test system and then started messing with it on 12c in a generic Linux VM. The 12c version of the DBMS_REDEFINITION includes a new procedure called REDEF_TABLE which lets you do in one step certain things you did with multiple calls to the package in 11.2. This is an online table compress on 12c:
BEGIN DBMS_REDEFINITION.REDEF_TABLE( uname => user, tname => 'TEST', table_compression_type => 'COMPRESS'); END; /
Things that can’t be done in one step like this require calls to procedures such as CAN_REDEF_TABLE, START_REDEF_TABLE, REGISTER_DEPENDENT_OBJECT, COPY_TABLE_DEPENDENTS, and FINISH_REDEF_TABLE. Example online12c5.sql uses all of these. Here is a summary of each included file’s test:
online12c1.sql – compress table in one step
online12c2.sql – compress table in multiple steps and show that it creates a hidden column without a primary key
online12c3.sql – same as previous test but with primary key
online12c4.sql – copy contraints and indexes
online12c5.sql – change the columns for the non-unique index
online12c6.sql – change order and type of columns
Nice to see ur article here….Thanks Vijay , Infosys
Is this applicable to 12c Release 1?
Yes. I am pretty sure that when I created the post in 2014 I only had 12.1. I just retested the scripts in the attached zip on a 18.104.22.168 instance with the January 2019 patch set and they all worked fine.