Created
December 14, 2014 20:25
-
-
Save uilian/5030c9f802975ed37e3e to your computer and use it in GitHub Desktop.
Oracle locked objects
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 1 - Identify lock | |
column oracle_username format a15 | |
column os_user_name format a15 | |
column object_name format a37 | |
column object_type format a37 | |
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from | |
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, | |
(SELECT OBJECT_ID, OWNER, OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS) B | |
where a.object_id=b.object_id; | |
-- 2 - Identify which session is locking | |
SET LINESIZE 100 | |
COLUMN spid FORMAT A10 | |
COLUMN username FORMAT A10 | |
COLUMN program FORMAT A45 | |
SELECT s.inst_id, | |
s.sid, | |
s.serial#, | |
P.SPID, | |
s.username, S.OSUSER, | |
s.program | |
FROM gv$session s | |
JOIN GV$PROCESS P ON P.ADDR = S.PADDR AND P.INST_ID = S.INST_ID | |
WHERE s.type != 'BACKGROUND' and s.sid = ?; | |
-- 3 - kill the session | |
ALTER SYSTEM KILL SESSION '168,770'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment