Submitting forms on the support site are temporary unavailable for schedule maintenance. If you need immediate assistance please contact technical support. We apologize for the inconvenience.
The ALTER TABLE MOVE is not a supported DDL in Shareplex. If it is executed on source, one may see ORA-0001 on target as the following message shows:
12/03/02 12:15 Out of sync: sp_opst (for o.cerg-o.cerg queue ora2splex-ctl) table - "CONCIERGE"."EVENT" (rid:AAAA6BAAIAAABVkAAT) ORA-00001: unique constraint (CONCIERGE.PK_EVENT) violated.
원인
ALTER TABLE MOVE is executed on source and is not a supported DDL by Shareplex, causing the errors described above.
해결 방안
The following excerpt is taken from Shareplex 5.3 Admin Guide Chapter 9 titled "Changing Oracle Characteristics of Replicated Objects" and illustrates the correct way to execute the DDL:
The ALTER TABLE MOVE command affects replication in the following ways: - It causes the row IDs to change when Oracle deletes and inserts the rows. - It puts the object in a new segment or tablespace, causing the row-chaining to change.
To move a table that is in an active configuration, use one of the following procedures,depending on whether or not you are moving the table into a new tablespace.
Moving a table to a new segment (Does not require configuration reactivation) The following procedure avoids out-of-sync errors by allowing SharePlex to rebuild its row-chaining map for the table that you are moving.
1. Stop user access to the table. 2. Export the data from the table. 3. Truncate the table. 4. Issue the ALTER TABLE MOVE command to move the table to the new segment. 5. Import the data back into the table. 6. Allow users to access the table.
Moving a table to a new tablespace (Requires configuration reactivation) To move a table to another tablespace, use the following procedure, which enables SharePlex to update its object cache after the table is moved to the new tablespace.
1. Run sp_ctrl. 2. Copy the active configuration, but do not deactivate it. Use the show config or list config command if you are unsure of the name. Make a note of the name, because you will need it later. sp_ctrl(sysA)> copy config filename to newname 3. Open the new configuration using the edit config command. sp_ctrl(sysA)> edit config newname 4. Locate the line containing the table that you are moving, and use a pound symbol (#) to comment it out of the file, as shown in the following example. # scott.emp scott.emp sysB@o.oraB 5. Save the new configuration file with the :wq command if using vi, or with Save on the File menu if using WordPad. 6.Stop user access to the table. 7. Activate the new configuration. You are warned that this configuration deactivates the one that is active. Proceed with the activation, which removes the table from replication. sp_ctrl(sysA)> activate config newname 8. Issue the ALTER TABLE MOVE command to move the table to the new tablespace. 9. Activate the original configuration, which includes the uncommented entry for the table. You are warned that this configuration deactivates the one that is active. Proceed with the activation, which commences replication for the table again. The only object that is locked is the table for which you issued the ALTER TABLE MOVE command. sp_ctrl(sysA)> activate config filename 10. Grant users access to the table.
