select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as OBJ_NAME, -- 对象名称(已经被锁住) LOCK_INFO.SUBOBJ_NAME as SUBOBJ_NAME, -- 子对象名称(已经被锁住) SESS_INFO.MACHINE as MACHINE, -- 机器名称 LOCK_INFO.SESSION_ID as SESSION_ID, -- 会话SESSION_ID SESS_INFO.SERIAL# as SERIAL#, -- 会话SERIAL# LOCK_INFO.ORA_USERNAME as ORA_USERNAME, -- ORACLE系统用户名称 LOCK_INFO.OS_USERNAME as OS_USERNAME, -- 操作系统用户名称 LOCK_INFO.PROCESS as PROCESS, -- 进程编号 LOCK_INFO.OBJ_ID as OBJ_ID, -- 对象ID LOCK_INFO.OBJ_TYPE as OBJ_TYPE, -- 对象类型 SESS_INFO.LOGON_TIME as LOGON_TIME, -- 登录时间 SESS_INFO.PROGRAM as PROGRAM, -- 程序名称 SESS_INFO.STATUS as STATUS, -- 会话状态 SESS_INFO.LOCKWAIT as LOCKWAIT, -- 等待锁 SESS_INFO.ACTION as ACTION, -- 动作 SESS_INFO.CLIENT_INFO as CLIENT_INFO -- 客户信息 from (select obj.OWNER as OWNER, obj.OBJECT_NAME as OBJ_NAME, obj.SUBOBJECT_NAME as SUBOBJ_NAME, obj.OBJECT_ID as OBJ_ID, obj.OBJECT_TYPE as OBJ_TYPE, lock_obj.SESSION_ID as SESSION_ID, lock_obj.ORACLE_USERNAME as ORA_USERNAME, lock_obj.OS_USER_NAME as OS_USERNAME, lock_obj.PROCESS as PROCESS from (select * from all_objects where object_id in (select object_id from v$locked_object)) obj, v$locked_object lock_obj where obj.object_id = lock_obj.object_id) LOCK_INFO, (select SID, SERIAL#, LOCKWAIT, STATUS, PROGRAM, ACTION, CLIENT_INFO, LOGON_TIME, MACHINE from v$session) SESS_INFO where LOCK_INFO.SESSION_ID = SESS_INFO.SID;
--强杀进程
-- alter system kill session 'sid,serial#'--如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号： --select spid, osuser, s.program from v$session s,v$process p where --s.paddr=p.addr and s.sid=130；--去linux后台使用 kill -9 进程号