1 2 Previous Next 20 Replies Latest reply: Sep 22, 2010 4:41 AM by Franck Pachot Go to original post RSS
      • 15. Re: Data reverts back on commit
        799084
        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
          Charles Hooper
          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
            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
              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
                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
                  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