DDL锁

V$ACCESS 显示数据库中当前被锁定的对象以及正在访问这些对象的会话。
V$ACCESS displays objects in the database that are currently locked and the sessions that are accessing them.
Column Datatype Description
SID NUMBER Session number that is accessing an object
OWNER VARCHAR2(64) Owner of the object
OBJECT VARCHAR2(1000) Name of the object
TYPE VARCHAR2(24) Type identifier for the object

--RAC
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate;'
from gv$session s,gv$access a
where s.inst_id=a.inst_id
and s.sid=a.sid
and a.object='CUX_5_FND_COMMON_PKG'
and a.owner='APPS';

--Single instance
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''' immediate;'
from v$session s,v$access a
where s.sid=a.sid
and a.object='CUX_5_FND_COMMON_PKG'
and a.owner='APPS';

--Single instance
SELECT SID,
         SERIAL#,
         PADDR,
         'alter system kill session ''' || sid || ',' || serial# || ''' immediate;'
    FROM V$SESSION
   WHERE SID in (select /*+ rule*/
      SID
       from V$ACCESS
      WHERE object='CUX_5_FND_COMMON_PKG' and a.owner='APPS';

set lines 200 pages 50000
Select distinct 'ALTER SYSTEM DISCONNECT SESSION  ''' || b.sid ||','||b.SERIAL# || ',@' || b.INST_ID || ''' immediate;'
From dba_ddl_locks a, gv$session b
Where a.session_id=b.SID
And a.name =upper('CUX_5_FND_COMMON_PKG') and b.username=upper('APPS');