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;