Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes from another session while the transaction is not committed. Is this possible?

Example:

Insert into table myTable (col1, col2) values ("col1", "col2");

--Somehow view the pending transaction maybe by system view?....

...other DML statements....

commit;
share|improve this question
    
There is more than one way to do it. For example, the SQL statements here can solve : ducquoc.wordpress.com/2012/07/14/oracle-uncommited-changes good luck, –  user15303 Nov 13 '12 at 15:46
add comment

7 Answers 7

There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes.

1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require multiple days or weeks of humans figuring out where to run a pipeline in one transaction). Your batch process could create a new workspace (which is logically like creating a new transaction), make whatever changes it would like in that workspace while committing whenever it wanted. In a separate session, you wouldn't see any of the committed changes until you entered the batch process's workspace. When the batch process finished, it could merge it's workspace back into the live workspace which is the equivalent of committing a transaction.

2) The DBMS_XA package can be used to allow you to "hand off" a transaction from one session to another and to allow one session to connect to a transaction started by another session. This is a pretty obscure package to be using, but there was a nice example of using it in the PL/SQL Challenge (you may need a free account to access it) recently.

3) If you're just trying to see the status of the batch process rather than seeing the actual data, the batch process can write logging information using autonomous transactions that you could then query from another session. Or you could use the DBMS_APPLICATION_INFO package to have your application update various attributes in V$SESSION and/or V$SESSION_LONGOPS so that you could monitor the status of the load from another session.

share|improve this answer
add comment

What Oracle doesn't have is a read uncommitted isolation mode. In other words you will not be able to query uncommitted data in another transaction.

There are ways of getting information out of a long running transaction - one not mentioned so far is autonomous transactions (which should be used with caution)

share|improve this answer
add comment

Yes - LogMiner can do this. In fact, if you only want committed transactions, you have to specifically filter the output! And there is TABLE_NAME in V$LOGMINER_CONTENTS, that's how you would look at a single table.

share|improve this answer
add comment

edit: this was written before the question was clarified

You could use flashback queries to see the table without your own uncommited data.

Consider:

SQL> CREATE TABLE my_table
  2  AS SELECT ROWNUM ID FROM dual CONNECT BY LEVEL <= 5;

Table created

SQL> INSERT INTO my_table VALUES (6);

1 row inserted

To see the difference between the table with my transaction and the table as seen by others I could issue:

SQL> SELECT * FROM my_table
  2  MINUS
  3  SELECT * FROM my_table AS OF TIMESTAMP (systimestamp);

        ID
----------
         6
share|improve this answer
1  
+1 for the great answer. -1 because it is not an answer to the question asked! –  Jack Douglas May 30 '11 at 18:50
2  
@jack: it's not clear if the OP wants to see uncommited data outside of its session (the pseudo-script could be in a single session). My answer would only work to see one's own pending modifications to a table. –  Vincent Malgrat May 30 '11 at 20:15
    
you're right, sorry. Great answer. –  Jack Douglas May 31 '11 at 4:27
add comment

There is no direct method; you'll either have to parse through the logs (as mentioned in another answer), or use alternate methods to see what's happening in a long-running process.

Personally, I suggest using autonomous transactions to enable this feature -- not on the transaction itself, but as a logging mechanism letting you know what is going on. For example, you could have PROCEDURE LONG_ACTION call PROCEDURE WRITE_LOG_ENTRY (defined as an autonomous transaction) that would write a VARCHAR2 to another table. Autonomous transactions do NOT interfere with your current transaction (from a LOGICAL perspective; beware of potential impacts to performance) and so you can see what's going on via your logging entries regardless of a COMMIT or ROLLBACK in your current transaction. That said, you can'd do that with one massive DML statement; you'd have to use a loop.

Consider:

TABLE LOG_ENTRIES defined as
    activity_date  date,
    log_entry varchar2(2000)

TABLE BIG_JOB (definition doesn't really matter)

PROCEDURE WRITE_LOG_ENTRY
                        ( str VARCHAR2 )
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO LOG_ENTRIES VALUES ( SYSDATE, str );
    COMMIT;
END;

PROCEDURE LONG_ACTION IS
    c NUMBER;
BEGIN
    FOR r IN ( SELECT * FROM BIG_JOB )
    LOOP
       c := c + 1;
       UPDATE BIG_JOB z
          SET fld = hairy_calculation
        WHERE z.rowid = r.rowid;
       IF MOD(c,500) = 0 THEN
           WRITE_LOG_ENTRY ( c || ' rows processed.' );
       END IF;
    END LOOP;
    COMMIT;
END;

Given the above, you'll get a log entry for every 500 rows processed regardless of success of the long action. If you need an exact duplicate of the data to see as it is working, I suggest making a duplicate table and calling an procedure that will duplicate the data (the procedure being an autonomous transaction). Then nuke the data after-the-fact. (No need for duplication.)

Further, if this is for a debugging purpose, I suggest removing or drastically reducing the need for such logging when things have been tested. And, as always, test, test, test on your own system to verify how things will work. (See the comment from Niall for a good example of how logging can drastically affect performance.)

(Finally, because I neglected to mention it before: beware autonomous transactions. Understand them fully before implementing, and don't use them "just because". They can be used in a million ways incorrectly (say, for example, to ATTEMPT to avoid a mutate error in a trigger), so it is always best to find alternatives, if possible. If you can't, then proceed with caution. Logging during long-running ops has always been one case where it is fairly safe (ignoring performance issues), but don't rush in to apply it to other uses without knowing the consequences.)

share|improve this answer
1  
The caveats at the end of this suggestion are explored further in my long response (with code) at orawin.info/blog/2011/09/06/advice-from-the-internet . In short adopting this approach may seriously and adversely affect code that already is slow. –  Niall Litchfield Sep 6 '11 at 11:23
1  
@Niall Litchfield, As always, when taking advice from the internet, one should always test, test, test. When mentioned that the autonomous transaction doesn’t affect the transaction, I was referring to the fact that it neither COMMITs or ROLLsBACK your current transaction; therefore in a LOGICAL sense, it does nothing to your current transaction. Yes, of course, Oracle has do things behind-the-scenes to make things work, which can mean performance problems, from a transaction-only viewpoint, the autonomous transaction doesn’t get in the way of the state of my current transaction. –  Kerri Shotts Sep 10 '11 at 16:55
    
@Niall Litchfield, All that said, autonomous transactions have their own fair share of problems (one of which happens to be that people try to use them to get around a mutating table), and so I recommend them sparingly and with caution, and ONLY with understanding what’s going on. –  Kerri Shotts Sep 10 '11 at 16:55
add comment

Not available in 10g, but DBMS_XA can allow a transaction to cross multiple sessions. Using that the second session can see what is happing in the transaction

share|improve this answer
add comment

In addition to the other information here, some additional ways to send information about an uncommitted transaction would be to send an email or write to a text file.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.