How to make commit to fail
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to make commit to fail

Hybrid View

  1. #1
    Join Date
    Nov 2001
    Posts
    335

    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!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  3. #3
    Join Date
    Nov 2001
    Posts
    335
    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 12:55 PM.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote 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

  5. #5
    Join Date
    Nov 2001
    Posts
    335
    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!

  6. #6
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote 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"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width