-
How to make commit to fail
Hello,
I am in a process of testing of Oracle transparent Gateway which supposed to provide us with the 2 phase commit between Oracle and DB2 databases.
One of the test scenario calls for simulation of the commit on the Oracle side to fail and make sure that both changes (Oracle and DB2 ) rolled back. I have not yet been successfull trying to make "commit " to fail.
Your input will be greatly appreciated.
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
Originally Posted by BV1963
Hello,
I am in a process of testing of Oracle transparent Gateway which supposed to provide us with the 2 phase commit between Oracle and DB2 databases.
One of the test scenario calls for simulation of the commit on the Oracle side to fail and make sure that both changes (Oracle and DB2 ) rolled back. I have not yet been successfull trying to make "commit " to fail.
Your input will be greatly appreciated.
Did you try to put the tablespace in read only mode which in turn fail your transaction in Oracle?
Tamil
-
I think it will not do it. If I make a tablespace read only than DML will fail before I even get to commit.
Any other idea?
1* ALTER TABLESPACE psdefault read only
HCMDEV:SYSTEM> /
Tablespace altered.
HCMDEV:SYSTEM>
HCMDEV:SYSTEM> insert into ORACLETEST values ('11111');
insert into ORACLETEST values ('11111')
*
ERROR at line 1:
ORA-00372: file 3 cannot be modified at this time
ORA-01110: data file 3: '/u03d/oradata/HCMDEV/psdefault.dbf'
Last edited by BV1963; 11-03-2005 at 01:55 PM.
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
Originally Posted by BV1963
I think it will not do it. If I make a tablespace read only than DML will fail before I even get to commit.
Any other idea?
1* ALTER TABLESPACE psdefault read only
HCMDEV:SYSTEM> /
Tablespace altered.
HCMDEV:SYSTEM>
HCMDEV:SYSTEM> insert into ORACLETEST values ('11111');
insert into ORACLETEST values ('11111')
*
ERROR at line 1:
ORA-00372: file 3 cannot be modified at this time
ORA-01110: data file 3: '/u03d/oradata/HCMDEV/psdefault.dbf'
Try savepoint method:
Code:
SQL> create table T99 (id int) ;
Table created.
SQL> savepoint abc ;
Savepoint created.
SQL> insert into T99 values (100) ;
1 row created.
SQL> rollback to ABC ;
Rollback complete.
SQL> commit ;
Commit complete.
SQL> select * from T99 ;
no rows selected
Tamil
-
My goal was to make a commit to fail so I can validate that 2 phase commit is working properly. So far I could not accomplish it and suggested solutions although greatly apprecicated did not solve my problem
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
how about you create a refresh-on-commit materialized view on a table, and use that as the mechanism?
Code:
create table t (col1 char(1) primary key);
create materialized view mv
refresh complete on commit
as
select
to_number(col1) col1,
count(*)
from t
group by to_number(col1);
insert into t values (1);
commit;
insert into t values ('A');
commit;
select * from t;
-
Originally Posted by BV1963
My goal was to make a commit to fail so I can validate that 2 phase commit is working properly. So far I could not accomplish it and suggested solutions although greatly apprecicated did not solve my problem
Open two sessions, keep the commit ready and in another session drop the dblink, simultaneously commit enter and DROP the DBlink(OR)
2. DELETE the ODBC system DNS file
3. you can even unplug the DB2 server network cable - Rough though!
which will commit at the local server and since there is no response from the remote will fail (2PC).
"What is past is PROLOGUE"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|