Oracle uncommited changes

Inspired by the question on DBA stackexchange (which doesn’t have an easy answer),

Oracle – I’m debugging through a batch proess 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 while the transaction is not commited. Is this possible?

I’d like to share some SQL to view “pending” changes in Oracle.

Basically, they are based on the built-in views such as v$transaction and  v$session .

.

SELECT s.sid, s.username, s.osuser, s.machine, s.status, s.program, t.start_time
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
;

.

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.status, t.start_time
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
--AND s.sid in (select m.sid from v$mystat m)
;

.

SELECT *
FROM v$session v
WHERE v.AUDSID = userenv('sessionid')
 AND v.TADDR IS NOT NULL
;

.

select s.sid, s.serial#, s.status, s.osuser, s.machine, d.owner, d.object_name, d.object_type
from v$session s, dba_objects d, v$locked_object l
where s.sid = l.session_id
AND d.object_id = l.object_id
;

.

Select dbms_transaction.local_transaction_id from dual;

.

That’s it.

.

UPDATED: Some ppl may wonder why I don’t post directly my “answers” to SE site. Well, I don’t want to contribute for StackOverflow or StackExchange sites.

I already mentioned this in a few posts, but the below comment help me edit this post to make it clear now. The SO/SE site owners got enough people to contribute answers/knowledge there already. As a result the sites has big reputation, and the owners got probably very, very much money with it. Why do I have to contribute to such guys, while I can contribute to other places (such as: mailing-list, support forum, … or even my own website), which I have been doing in several years?

See my post in the comments for a longer explanation.

./.

About these ads

About DucQuoc.wordpress.com

A coder, brother, and player at times...
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Oracle uncommited changes

  1. Yeah, what you did here was not “answer a Stack Exchange question” but rather “look how smart I am, I answered the question outside the network and made it hard for anyone to find the answer, except by following a cryptic link”. In the future, if you’re certain you would like to provide an answer to any Stack Exchange questions, please answer those questions on the Stack Exchange network site responsible for the question. You are also encouraged to either go back and delete your answer, make it more full (such as including all the code from this “blog post” and including comments explaining why this works), or ask us to convert to a comment.

  2. Thank you for your feedback. Yes, somehow the post makes me like a “smart” guy ;-) .

    * Short answer:
    Ok, feel free to go ahead and convert my answer on SE to a “comment” . It’s totally fine with me. (actually it’s more helpful that way, people can see it easier than “high-voted-but-not-useful” answers).

    * Long answer:
    What I forgot to mention is that I do NOT want to support StackOverflow (and StackExchange sites, in general).
    The SO site has huge reputation already, and related SE sites are also pretty good, IMO. And the guys who create them *already has tons of money/benefits thanks to that*, I guess. Many developers got good answers there, so it’s kind of Win-Win.
    Who lost?

    It’s the other “Answer” channels, such as supporting forums, IRC, mailing-list… Actually they make more efforts than just creating a Q&A site, they including lots of people who volunteer their own time, without getting any money in return. Now with SO/SE even the reputation of their “community” is reduced, and their efforts are less appreciated.

    Therefore, if I have to put a detailed/full answer, I’m going to post it to the mailing-list or supporting forum of the specific topic (Spring, Hibernate, Ubuntu, CentOs, LinuxQuestions, …) , not some SE site.
    And I don’t intend to get reputation from SO/SE sites, either.
    (If you search my name in SE sites, the most you can see is 1 or 2 times – when I think somebody really needs help).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s