To get detail of Locks with Object Locked:-
------------------------------------------------
If you want to know the objects which are locked in your database, then you can use the following query.
SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER",
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
Subscribe to:
Post Comments (Atom)
3 comments:
Hi,
Following queries can be used to
kill any job.
SELECT * FROM DBA_DDL_LOCKS WHERE NAME LIKE'PACKAGE NAME ON WHICH CONC
PGM IS RUNNING%';
-- TAKE SESSION_ID AS SID
SELECT * FROM V$SESSION WHERE SID=----;
--SELECT SID AND SERIAL# FROM THE ABOVE
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
Regards,
Phani
You can following the steps given to below to kill the session.
SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER", vs.SID,
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
From the above Query, take the session ID "vs.SID" value.
select sid, serial#, command, taddr, 'alter system kill session '||''''||sid||', '||serial#||''''
from v$session where sid in ('Your SID Value');
The above query would return some Alter command. Execute same Alter Command to Kill the session.
Example for the Alter command.
alter system kill session '31, 4857';
In the above Alter statement, 31 is the SID Number.
Thanks and regards,
Phani
Post a Comment