reorganize tables online with dbms_redefinition

Sometimes it’s neccessary to change table structures or to rebuild tables.

Unfortunately you wont get a downtime to do so…. even business as usual 😉

With the package dbms_redefinition you can modify your tables online (with only a view exclusive locks)

---

short summary :

1. check for redefinition

2. create a interim table (with your new definition)

3. duplicate original table to interim table

in background a mlog-table (if rowid is used) will be updated while both tables are “in use”

4. create dependent objects (e.g. indexes, views …)

5. resync tables

6. redefinition finish

7. drop interim table

9. possible problems

Advertisements