Monday, August 27, 2007

Modify a colume of a replicated table from NOT NULL to NULL - is quiesce the replication group necessary?

This morning I was pinged by the manager to answer this question. Because a production change has been made last weekend and now we want to change a column from NOT NULL to NULL. I, as well as other two more experienced DBAs all believe we need to quiesce the group.

Later, I did the following test. It seems we can just apply the alter table statement directly at each site without shutdown the database.

(1) Replicated table t1 has a column B that is not null

repadmin@TSTQA> desc myschema.t1;

Name Null? Type
----------------------------------------------------- -------- ----------
A NOT NULL NUMBER
B NOT NULL NUMBER
C NUMBER


repadmin@TSTQB> desc myschema.t1

Name Null? Type
----------------------------------------------------- -------- -----------
A NOT NULL NUMBER
B NOT NULL NUMBER
C NUMBER


(2) Test replication

repadmin@TSTQA> insert into myschema.t1 values(10,11,12);
1 row created.

repadmin@TSTQA> commit;
Commit complete.

repadmin@TSTQA> select * from myschema.t1;
A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12

7 rows selected.

repadmin@TSTQA> select * from myschema.t1@tstqb;
A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12

7 rows selected.


(3) Change from not null to null

repadmin@TSTQB> alter table myschema.t1 modify (b null);

Table altered.

repadmin@TSTQB> desc myschema.t1

Name Null? Type
----------------------------------------------------- -------- ----------
A NOT NULL NUMBER
B NUMBER
C NUMBER



repadmin@TSTQA> alter table myschema.t1 modify (b null);

Table altered.

repadmin@TSTQA> desc myschema.t1

Name Null? Type
----------------------------------------------------- -------- ----------
A NOT NULL NUMBER
B NUMBER
C NUMBER


(4) Test replication with null values for B

repadmin@TSTQB> insert into myschema.t1 values(12, null, null);

1 row created.
repadmin@TSTQB> commit;
Commit complete.

repadmin@TSTQB> select * from myschema.t1;

A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12
12

8 rows selected.

repadmin@TSTQB> select * from myschema.t1@tstqa;

A B C
---------- ---------- ----------
1 2 3
2 4 6
4 5 6
7 8 9
8 9
9 11 12
10 11 12
12

8 rows selected.

No comments: