Altering a Replicated Object

As your database needs change, you may need to modify the characteristics of your replicated objects. It is important that you do not directly execute DDL to alter your replicated objects. Doing so may cause your replicated environment to fail.

Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to alter the characteristics of your replicated objects. From the example below, notice that you simply include the necessary DDL within the procedure call (see the DDL_TEXT parameter).

Executed As: Replication Administrator

Executed At: Master Definition Site

Replication Status: Quiesced

CONNECT repadmin/repadmin@orc1.world

-- If the replication status is normal, change the status to quiesced.
BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/
BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE scott.emp ADD (site VARCHAR2(20))');
END;
/

--After you have inserted a new column into your replicated object,
--you need to make sure that you regenerate replication support for
--the affected object. This step should be performed immmediately
--after you alter the replicated object.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--NOTE: You should wait until the DBA_REPCATLOG view is empty. Execute 
--the following SELECT statement in another SQL*Plus session to monitor 
--the DBA_REPCATLOG view:
--
--SELECT * FROM dba_repcatlog WHERE gname = 'SCOTT_MG';

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/