存储过程锁问题、锁表问题 存储过程编译锁问题---解锁办法,Oracle

 存储过程锁问题  存储过程编译锁问题---解锁办法

/*
\\ 存储过程锁问题  存储过程编译锁问题---解锁办法
*/
--过程的SID
select a.OBJECT,a.TYPE, B.SID, b.USERNAME, b.MACHINE
  FROM V$ACCESS A, V$SESSION B
WHERE A.SID = B.SID  and a.TYPE = 'PACKAGE' and a.OBJECT = 'PAC_PUB';
--找到操作系统的进程号
select spid os_sid
  from v$process
where addr = (select paddr from v$session where sid = 139);
--找到进程号
ps -ef|grep 7641
--杀进程
kill -9 7641

  查询锁表

SELECT /*+ rule */
 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';',
 s.username,
 decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
 o.owner,
 o.object_name,
 o.object_type,
 s.sid,
 s.serial#,
 s.terminal,
 s.machine,
 s.program,
 s.osuser
  FROM v$session s, v$lock l, dba_objects o
 WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT Null

  查询正在运行的存储过程

select owner, name
  from v$db_object_cache
 where type like '%PROCE%'
   and locks > 0
   and pins > 0;