0

we have tables that were created with "NOROWDEPENDENCIES" (the default). I've read in oracle documentation that the only two ways to do so are:

redefine the table using the DBMS_REDEFINITION package or recreate the table.

I can not recreate the table, so my only solution is to redefine it, but I couldn't find anywhere an example or a guide to do it.

can you show me an example or reference to any guide? Thanks

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Izik
  • 746
  • 1
  • 9
  • 25

1 Answers1

1

First of all you have to check if redefinition is allowed for your table. If the following procedure returns an exception you are not allowed:

BEGIN
     DBMS_REDEFINITION.CAN_REDEF_TABLE(
                  uname => '<USER NAME>',
                  orig_table => '<TABLE NAME>'
     );
END;
/

If the table can be redefined you have to create an interim table on the same schema with the desired attributes. The execute (no column mapped in your table):

BEGIN
     DBMS_REDEFINITION.start_redef_table (
                  uname => '<USER NAME>',
                  orig_table => '<TABLE NAME>',
                  int_table => 'INTERIM'
     );
END;
/

If everything is ok you can finish the process executing the procedure:

BEGIN
     DBMS_REDEFINITION.finish_redef_table (
                  uname => '<USER NAME>',
                  orig_table => '<TABLE NAME>',
                  int_table => 'INTERIM');
END;
/

Before finishing redefinition with the previous procedure you can create indexes, constraints, etc.. on the interim table.

If you want to keep the interim table synchronized with the original table you also might need:

BEGIN
     DBMS_REDEFINITION.sync_interim_table (
                  uname => '<USER NAME>',
                  orig_table => '<TABLE NAME>',
                  int_table => 'INTERIM');
END;
/

to be called before the FINISH_REDEF_TABLE Procedure. dbms_redefinition.copy_table_dependents(..); can be useful to complete the job. To abort the redefinition procedure you might need:

BEGIN
     DBMS_REDEFINITION.abort_redef_table (
                  uname => '<USER NAME>',
                  orig_table => '<TABLE NAME>',
                  int_table => 'INTERIM');
END;
/

Pay attention, you might need a lot of space if the original table is big. If the system is on line redefinition should be monitored. It might be heavy.

Saxon
  • 739
  • 3
  • 6
  • For this whole procedure, must I create the INTERIM table with the exact Clauses, Constraint, Triggers, etc... of the original table? The original tables are in production, I can not afford to lose any data relevant to these tables. – Izik Jan 31 '21 at 18:29
  • Of course you are going to use a different name for the interim table and everything that comes with it. At the end you can rename the original table and the interim table and you are done. – Saxon Feb 05 '21 at 20:55