could you please explain why the below SQL query is blocked?
SELECT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=469 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY_ID=:1
SELECT 1 FROM DUAL is blocking the SQL statement on GTTAPPUSR@gttccuatcriba04 ( SID=367 ) blocked SQL -> DELETE FROM GTTDB.PURCHASE_ENTRY_ID=:1
I am scratching my head without any solution when I had a look at the db today. Thanks in advance for your help.
Edited by: user12075620 on Dec 4, 2012 8:58 AM
The utllockt.sql script displays, in a tree fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. The location of this script file is operating system dependent.Regards.
Session 1 now has a lock on the KING row in the EMP table. But session 1 neither commits nor rolls back, it is still in a transaction. Session 1 might not have any more activity for a long time-- the user might go off to lunch, for example (obviously, applications should not be designed to allow users to maintain open transactions indefinitely, but not all applications are designed correctly). Or it might start running other queries. Let's say that session 1 now runs a query that is going to go for an hour
UPDATE emp SET sal = sal * 2 WHERE ename = 'KING'
Now, at 12:45, session 2 comes in and runs
SELECT * FROM giant_view_with_lots_of_computations
Session 2 is blocked. Session 2 is running the UPDATE statement. Session 1 still holds the lock but it is running some completely unrelated SQL statement.
UPDATE emp SET bonus = 100 WHERE ename = 'KING'
Supposing the blocking session issues lots of queries, Is it possible to display all the sql statements owned by that session?
post SQL & results which shows which user owns which SQL statement.
I bet you are incapable to do it.
Guess why it is called the Shared Global Area?