Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 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.

share|improve this answer
    
You may want to move indexes in the new tablespace as well and rebuild them too but indeed, there's no need to use rdbms_redefinition for this. –  Benoit Apr 1 '11 at 12:12
    
You can just rebuild the indexes. There is no need to move the to do so. –  BillThor Apr 1 '11 at 13:29

You should re-create them, with unique names.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.