Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Aug 22, 2002

Adding Columns in a Synchronous Multi-Master Replicated Environment

By Greg Johnson


Note: Tested on Oracle 8.1.7.3 Sun o/s 7

The following is a guide for adding a column to a table that is currently in a synchronous multi-master replicated environment. This guide assumes that the replicated environment has already been created.

The current replicated table has the following details and belongs to the master group REP_TEST_SETS.

CREATE TABLE TEST_SETS ( 
  USER_ID               VARCHAR2 (20)  NOT NULL, 
  CONSTRAINT TEST_SETS_PK
  PRIMARY KEY ( USER_ID ) 
    USING INDEX 
     TABLESPACE INDEX_SML)
   TABLESPACE DATA_MED

The column being added has the following features:

Name: ADDED_COLUMN
Datatype: CHAR(1)
Default: 'N'
Constraint: NOT NULL

Here are the necessary steps:

1. Suspend the required Master Group of which the table exists:

     exec DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('REP_TEST_SETS');

2. Add a new column to the table:

     exec DBMS_REPCAT.ALTER_MASTER_REPOBJECT('SCOTT', 'TEST_SETS', 'TABLE', -
     ddl_text=> 'alter table scott.test_sets add (ADDED_COLUMN CHAR(1))');

     PL/SQL procedure successfully completed.

3. Add the DEFAULT value to the column:

     exec DBMS_REPCAT.ALTER_MASTER_REPOBJECT('SCOTT','TEST_SETS','TABLE',-
     ddl_text=> 'alter table scott.test_sets modify (ADDED_COLUMN default ''N'')');

     PL/SQL procedure successfully completed.

4. Regenerate application support for the TABLE:

     exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (sname => 'SCOTT',oname => 'TEST_SETS', -
     type => 'TABLE', min_communication => TRUE);

5. Resume multi-master replication:

     exec DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP_TEST_SETS',true);

6. Using SQLPLUS, update the new column with the default value:

     UPDATE TEST_SETS
     SET ADDED_COLUMN = 'N';

     9 rows updated.

     COMMIT;

     Commit complete.

7. Suspend replication on the Master Group again in order to add the NOT NULL constraint:

     Exec DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('REP_TEST_SETS');

     PL/SQL procedure successfully completed

8. Add the NOT NULL Constraint:

     exec DBMS_REPCAT.ALTER_MASTER_REPOBJECT('SCOTT','TEST_SETS', 'TABLE', -
     ddl_text=> 'alter table scott.test_sets modify ADDED_COLUMN constraint TEST_SETS_AD_NN not null');

     PL/SQL procedure successfully completed.

9. Regenerate application support for the TABLE:

     exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(sname => 'SCOTT', oname => 'TEST_SETS', type => 'TABLE', min_communication => TRUE);

     PL/SQL procedure successfully completed.

10. Resume Multi-master replication:

     Exec DBMS_REPCAT.RESUME_MASTER_ACTIVITY('REP_TEST_SETS',true);

     PL/SQL procedure successfully completed.

The Table now will look like the following.

CREATE TABLE TEST_SETS ( 
  USER_ID               VARCHAR2 (20)  NOT NULL, 
  ADDED_COLUMN          CHAR (1)      DEFAULT 'N'
     CONSTRAINT TEST_SETS_AD_NN NOT NULL, 
  CONSTRAINT TEST_SETS_PK
  PRIMARY KEY ( USER_ID ) 
    USING INDEX 
     TABLESPACE INDEX_SML)
   TABLESPACE DATA_MED

NB: I attempted this process on a table in which I have to make three column additions. On the third column during the update, I received the following error.

     UPDATE
     SET = 'N';
     The following error has occurred:


     ORA-01006: bind variable does not exist
     ORA-02063: preceding line from


If you get this, you'll have to recreate the MASTER GROUP again. After you do this, you can continue to step 7. I'm not sure what the issue is, though.


About the Author: Greg Johnson is an OCP 8i Database Administrator with four years experience currently working for Dialog Information Technology as an Oracle Database Consultant. Dialog Information Technology is one of Australia's leading technology services organizations, trading nationally and internationally. Dialog provides a wide range of information technology services, including outsourcing and technology management, systems integration and application development, implementation, training and support. Greg can be contacted at Johnsog123@hotmail.com.




Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM