Considerations when moving Oracle tables from one tablespace to another using Oracle dbms_redefin (4299802)

The Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they are accepting updates. This solution delves on steps needed from Shareplex perspective when using this package on replicating tables, since the object id of the table changes.

General information.

Since the object id changes during this action, this type of reorg requires certain considerations when implementing it for replicating tables.

Here are the steps involved:

NOTE: To avoid out fo sync it is recommended to stop user activity on the table in question for the duration of procedurte below.

A. Make a copy of the currently active config file, comment out the table and activate the copy. This will succeed if the table does not have any open transactions on it.

sp_ctrl> copy config config1  to config2
sp_ctrl> edit config config2                                                    (to comment out the table)
sp_ctrl> activate config config2

B. Reorg the table using Oracle dbms_redefinition  package, quiet the table and then activate original config file to bring the table back in replication:

sp_ctrl> activate config config1

If, for any reason the step B fails due to activity on the table, then it can be done at any other quiet period. But this will require the table to be sync'd at a later time since the table will be out of sync for the time being.