This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Sep 22, 2010 2:41 AM by Franck Pachot Go to original post RSS
  • 15. Re: Data reverts back on commit
    799084 Newbie
    Currently Being Moderated
    It's a table, not a synonym. I don't think there are any "phantom processes", but that's what I'm trying to find out. I was hoping someone can tell me how to get that information via some trace files or something. I will try to get the answers to the other queries soon.

    Note that since the same kind of DMLs work for other IDs in that table, I have no reason to suspect that it won't work for new users/tables that are created.
  • 16. Re: Data reverts back on commit
    CharlesHooper Expert
    Currently Being Moderated
    dbtrix wrote:
    It's a table, not a synonym. I don't think there are any "phantom processes", but that's what I'm trying to find out. I was hoping someone can tell me how to get that information via some trace files or something. I will try to get the answers to the other queries soon.

    Note that since the same kind of DMLs work for other IDs in that table, I have no reason to suspect that it won't work for new users/tables that are created.
    dbtrix,

    A couple of people have asked you for specific information regarding the table definition and asked you to try to reproduce the problem using an entirely new table. Until you answer those questions it is mostly a guessing game trying to figure out exactly what is happening. Try the following to retrieve the table definition:
    SET PAGESIZE 0
    SET LONG 90000
    SPOOL 'GETMETA.SQL'
     
    SELECT
      DBMS_METADATA.GET_DDL('TABLE','JOB_REQUESTS',USER)
    FROM
      DUAL;
    
    SPOOL OFF
    For example, the output might look something like this:
      CREATE TABLE "TESTUSER"."T1"
       (    "C1" NUMBER,
            "C2" DATE,
            "C3" DATE,
             CONSTRAINT "CHECK_DATE" CHECK (
        NVL(C2,TO_DATE('01-JAN-2000','DD-MON-YYYY')) < NVL(C3,TO_DATE('01-JAN-2000',
    'DD-MON-YYYY'))) DEFERRABLE INITIALLY DEFERRED ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "DATA"
    Enable a 10046 trace file at level 4 and perform your DML statement, then review the results. For example:
    CREATE TABLE T1 (
      C1 NUMBER,
      C2 DATE,
      C3 DATE,
      CONSTRAINT "CHECK_DATE" CHECK (
        NVL(C2,TO_DATE('01-JAN-2000','DD-MON-YYYY')) < NVL(C3,TO_DATE('01-JAN-2000','DD-MON-YYYY')))
        INITIALLY DEFERRED DEFERRABLE);
     
    INSERT INTO T1 VALUES (1,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));
    INSERT INTO T1 VALUES (2,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));
    INSERT INTO T1 VALUES (3,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));
     
    COMMIT;
     
    ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_COMMIT_TEST'; 
    ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
     
    UPDATE
      T1
    SET
      C2=SYSDATE
    WHERE
      C1 IN (1,2);
     
    COMMIT;
     
    UPDATE
      T1
    SET
      C2=SYSDATE-6000
    WHERE
      C1 IN (1,2);
     
    COMMIT;
    If you then review the raw contents of the 10046 trace file you should be able to see if the commit is actually happening. For example:
    PARSING IN CURSOR #5 len=48 dep=0 uid=60 oct=6 lid=60 tim=2961993454917 hv=626671218 ad='2339784c' sqlid='czba7d8kpngmk'
    UPDATE
      T1
    SET
      C2=SYSDATE
    WHERE
      C1 IN (1,2)
    END OF STMT
    PARSE #5:c=0,e=0,p=0,cr=16,cu=0,mis=1,r=0,dep=0,og=1,plh=2927627013,tim=2961993454917
    EXEC #5:c=0,e=31256,p=0,cr=7,cu=3,mis=0,r=2,dep=0,og=1,plh=2927627013,tim=2961993486173
    STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  T1 (cr=7 pr=0 pw=0 time=0 us)'
    STAT #5 id=2 cnt=2 pid=1 pos=1 obj=100689 op='TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=0 us cost=2 size=62 card=2)'
    CLOSE #5:c=0,e=0,dep=0,type=0,tim=2961993486173
    
    *** 2010-09-21 13:08:00.215
    =====================
    PARSING IN CURSOR #4 len=6 dep=0 uid=60 oct=44 lid=60 tim=2962000892362 hv=255718823 ad='0' sqlid='8ggw94h7mvxd7'
    COMMIT
    END OF STMT
    PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2962000892362
    XCTEND rlbk=0, rd_only=0, tim=2962000892362
    The above shows that two rows were updated (STAT #5 id=2 cnt=2) and that the COMMIT happened (XCTEND rlbk=0, rd_only=0), but does not show whether or not an error was triggered on the COMMIT.

    -------------------
    Edit:
    If you want to determine if the COMMIT was successful, enable the 10046 trace at level 12 (level 8 will also work). You can then use the wait events to determine if the COMMIT actually took effect. If you see a 'log file sync' wait followed by a 'SQL*Net break/reset to client' wait, then you can assume that the COMMIT could not complete due to an error:
    ...
    STAT #9 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=5 pr=0 pw=0 time=142 us)'
    STAT #9 id=2 cnt=1 pid=1 pos=1 obj=28 op='TABLE ACCESS BY INDEX ROWID CON$ (cr=3 pr=0 pw=0 time=60 us)'
    STAT #9 id=3 cnt=1 pid=2 pos=1 obj=49 op='INDEX UNIQUE SCAN I_CON2 (cr=2 pr=0 pw=0 time=41 us)'
    STAT #9 id=4 cnt=1 pid=1 pos=2 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=74 us)'
    STAT #9 id=5 cnt=1 pid=4 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=7 us)'
    WAIT #2: nam='log file sync' ela= 262 buffer#=995 p2=0 p3=0 obj#=355 tim=195834105
    WAIT #2: nam='SQL*Net break/reset to client' ela= 6 driver id=1413697536 break?=1 p3=0 obj#=355 tim=195834427
    WAIT #2: nam='SQL*Net break/reset to client' ela= 393 driver id=1413697536 break?=0 p3=0 obj#=355 tim=195834898
    -------------------

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

    Edited by: Charles Hooper on Sep 21, 2010 2:17 PM
    Added a note about a 10046 trace at level 12 and using wait events to determine if the COMMIT actually happened.
  • 17. Re: Data reverts back on commit
    jgarry Guru
    Currently Being Moderated
    dbtrix wrote:
    Here's the SQLPlus output from my terminal. If I run "show errors" after the commit, I don't see any errors.

    SQL> UPDATE job_requests set released_date = SYSDATE where id in (161, 195);

    2 rows updated.

    SQL> select released_date from job_requests where id in (161, 195);

    RELEASED_
    ---------
    20-SEP-10
    20-SEP-10

    SQL> commit;

    Commit complete.

    SQL> select released_date from job_requests where id in (161, 195);

    RELEASED_
    ---------



    SQL>
    Try alter session isolation level serializable. Then you'll get an ora-8177 if some other process is deleting job_requests that are released before you commit. (At least I hope I'm remembering how that works!) You might also want to search v$sql or some such view for such a delete statement.

    Maybe a select for update will lock out someone else and they'll squawk, or you can see the locked row and session.

    I didn't quite get if you said this happens repeatedly with (161,195) or different random rows.
  • 18. Re: Data reverts back on commit
    gary myers Explorer
    Currently Being Moderated
    One possibility is a background job that is continually looking for rows in that table meeting a certain criteria, then updating them. That might be the case for some form of data propagation where the updated rows are being pushed to another place, then 'reset' so they don't get picked up again.

    You could stick an AUDIT statement on the table that logs updates and track down the session details.
  • 19. Re: Data reverts back on commit
    user503699 Expert
    Currently Being Moderated
    dbtrix wrote:
    It's a table, not a synonym. I don't think there are any "phantom processes", but that's what I'm trying to find out. I was hoping someone can tell me how to get that information via some trace files or something. I will try to get the answers to the other queries soon.

    Note that since the same kind of DMLs work for other IDs in that table, I have no reason to suspect that it won't work for new users/tables that are created.
    You may want to enable audit for all DML operations on this table and check the audit records to see if any additional DML statements are executed "behind the scenes".
    Sorry, I did not see another poster above already suggested this.

    Edited by: user503699 on Sep 22, 2010 1:53 PM
  • 20. Re: Data reverts back on commit
    Franck Pachot Journeyer
    Currently Being Moderated
    Hi,

    Can you run:

    set constraint all immediate;

    just before the commit, in order to see if it raises an ORA-02290

    Regards,
    Franck.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points