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.
Monday, August 27, 2007
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment