Oracle Database Discussions

Hi,

I am runnig 10gR2 and would like to change state of a constraint from
DEFERABLE to IMMEDIATE without recreating it.
The change is working at the session level with
SET CONSTRAINT <constraint name> IMMEDIATE;

But this is not visible for other users.
So my question is, if there is any other way to do it, so the change would be visible for every user.

Here is what I have done:

CREATE TABLE TEST_TBL
(
ID NUMBER
)

ALTER TABLE TEST_TBL ADD CONSTRAINT pk_test_tbl PRIMARY KEY(ID)
INITIALLY DEFERRED DEFERRABLE;

SQL> INSERT INTO test_tbl VALUES(1);

1 row created.

SQL> INSERT INTO test_tbl VALUES(1);

1 row created.

SQL> INSERT INTO test_tbl VALUES(1);

1 row created.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (TEST_SCHEMA.PK_TEST_TBL) violated

The constraint is checked only at commit,

To change this:

SQL> SET CONSTRAINT pk_test_tbl IMMEDIATE;

Constraint set.

SQL> INSERT INTO test_tbl VALUES(1);

1 row created.

SQL> INSERT INTO test_tbl VALUES(1);
INSERT INTO test_tbl VALUES(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST_SCHEMA.PK_TEST_TBL) violated

But if I would connect with user B, I would be able to do multiple inserts with value 1.

Thanks