Menu
I am getting ORA-02049 occasionally for some long-running and/or intensive transactions. There is seemingly no pattern to this, but it happens on a simple INSERT.
I have no clue how to get any sort of information out or Oracle, but there has to be a way? A log over locking or atleast a way to see current locks?
891
And Sometimes this error is got because of open_links , so To solve this error, You should increase the open_links parameter as follows. alter system set open_links=20 scope=spfile sid='*'; alter system set open_links_per_instance=20 scope=spfile sid='*'; Then restart database to activate these parameter as follows.
Although locks are a necessity in Oracle, they can create performance issues. Each time a user issues a lock, another user would be prevented from processing the locked data. Oracle locking allows a variety of locks depending on the resources required – a single row, many rows, an entire table, many tables, etc.
Types of Locks Oracle automatically locks a resource on behalf of a transaction to prevent other transactions from doing something also requiring exclusive access to the same resource. The lock is released automatically when some event occurs so that the transaction no longer requires the resource.
When Oracle detects a deadlock, the current SQL in the session detecting the deadlock is cancelled and 'statement-level rollback' is performed so as to free up resources and not block all activity. The session that detected the deadlock is still 'alive' and the rest of the transaction is still active.
One possible way might be to increase the INIT.ORA parameter for distributed_lock_timeout to a larger value. This would then give you a longer time to observe the v$lock table as the locks would last for longer.
To achieve automation of this, you can either
Run an SQL job every 5-10 seconds that logs the values of v$lock or the query that sandos has given above into a table and then analyze it to see which session was causing the lock.
Run a STATSPACK or an AWR Report. The sessions that got locked should show up with high elapsed time and hence can be identified.
v$session has 3 more columns blocking_instance, blocking_session, blocking_session_status that can be added to the query above to give a picture of what is getting locked.
168
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
