Recreate indexes and constraint when redefining table with DBMS_REDEFINITON

Question

I have a table A witht the following characteristics:

• multiple indexes
• constraints (null, unique, check, foreign key)
• 2 triggers (before update)
• primary key

If I were to redefine the table with dbms_redefintion in order to change storage, do I have to recreate the triggers, constraints and trigger pointing to the interim table or does this go automatically with only having to rename them once is it is redefined?

Answer 1

You should re-create them, with unique names.

Answer 2

If you are only redefining storage you can do that without redefining the table. You can alter the table definition. Rearranging column orders requires redefinition. Most other changes can be done by altering the table.

If you do redefine the table you need to define the constraints and indexes you want on the new table. These will need new names. Some objects can be renamed later.

If you want to rebuild the table with the new storage, you can do that with an online move. If the new storage definition does not include a new tablespace, you will need to move it twice, once to another tablespace, and then back into the original tablespace.