| Who can explain this transaction for me? Thanks [message #56679] |
Wed, 16 April 2003 09:41  |
lg
Messages: 5
Registered: November 2000
|
Junior Member |
|
|
I select the row from v$transaction, I found one transaction holding for about two days. and the UBAXXX are all 0. Moroever,the transaction doesn't timeout.
Who can explain this?
BC2A6F4C 20 57 68326 0 0 0 0 ACTIVE 03/23/03 14:20:48
|
|
|
|
| Re: Who can explain this transaction for me? Thanks [message #56680 is a reply to message #56679] |
Wed, 16 April 2003 10:21  |
Michel Bartov
Messages: 35
Registered: February 2003
|
Member |
|
|
Someone started an insert/delete or update and didn't commit or rollback.
I posted an integrated set of GUI tools to administer and tune the Oracle database.
One of the many functions is to monitor database transactions. This function will give you all the information about the transaction including the SQL statement. Another function is the monitoring of the database locks. This function also has all the information needed and also allows you to kill the session that initiates that transaction.
You may need same help to get started, so feel free to contact me (michelbartov@yahoo.com). The link is http://www.barsoft.net/
|
|
|
|
| Re: Who can explain this transaction for me? Thanks [message #56681 is a reply to message #56680] |
Wed, 16 April 2003 10:29 |
lg
Messages: 5
Registered: November 2000
|
Junior Member |
|
|
|
Thank you,Michel, I will download the GUI tool for further ayalyses. But I don't if there are some way to get the SQL which executed by a long exist transaction? I can get the sql from sqltext but it is related to the current session, not the sql which the transation executed.
|
|
|
|
|
|
| Re: Who can explain this transaction for me? Thanks [message #56685 is a reply to message #56680] |
Wed, 16 April 2003 11:17 |
Michel Bartov
Messages: 35
Registered: February 2003
|
Member |
|
|
UBA shows the address where the last data block of the rollback segment/undo segment exists. So 0 means there is no rollback information.
Try to run these SQL statements.
SELECT b.start_time, a.sid, a.osuser, a.status, b.status, b.used_urec,
a.program, a.machine, b.log_io, b.phy_io, b.CR_GET , b.cr_change
from v$session a, v$transaction b
where a.saddr = b.ses_addr
order by b.start_time;
Replace 12 by the SID in your transaction.
SELECT sql_text
from v$sqltext
where (address, hash_value) = (select sql_address, sql_hash_value
from v$session
where sid = 12)
or (address, hash_value) = (select prev_sql_addr, prev_hash_value
from v$session
where sid = 12)
order by piece;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
