PL/SQL连接oracle时，JOBS无法打开，无法创建JOB问题处理

1、找到谁持有锁不释放
column object_name format a20
column username format a20
SELECT  s.username,decode(l.type,’TM’,'TABLE LOCK’,'TX’,'ROW LOCK’,NULL) LOCK_LEVEL,　　o.object_name,　　s.sid　,l.block　FROM v$session s,v$lock l,dba_objects o　　WHERE s.username=’OCBC’ and l.sid = s.sid　　AND l.id1 = o.object_id(+)　　AND s.username is NOT NULL order by s.username;
如果block列为1，说明对应的sid会话正持有object_name锁，其它和object_name有关的锁在等待sid提交或回滚。

2、根据sid获得这个连接的详细信息(操作系统用户、进程ID、程序名、机器名、终端名)
set linesize 120
column OSUSER format a15
column OSPID format a10
column MACHINE format a15
column TERMINAL format a12
column PROGRAM format a15
select s.SID, s.OSUSER, p.spid as OSPID, s.MACHINE,s.TERMINAL, s.PROGRAM from v$session s, v$process p where s.sid=12 and s.paddr=p.addr;

3、获得这个连接正在执行的sql文
set linesize 120
column SQL_TEXT format a100
select b.sql_text from v$session a, v$sql b where a.sid=12 and a.SQL_ADDRESS=b.ADDRESS(+);

检查当前持锁不释放的连接具体信息：
SELECT /*+ rule */ systimestamp dt,　s.username,s.sid,l.block,s.OSUSER, p.spid as OSPID, s.MACHINE,s.TERMINAL, s.PROGRAM,q.sql_text 　FROM v$session s,v$lock l,　v$process p,v$sql q　WHERE l.block=1 and l.sid = s.sid and s.paddr=p.addr and s.SQL_ADDRESS=q.ADDRESS;

如果想知道堵塞的sid及sql语句是什么，用下面语句（oracle10g测试通过），
select distinct b.sid, a.piece, a.sql_text from v$sqltext_with_newlines a, (select decode(s.sql_address,'00',s.prev_sql_addr,sql_address) sql_address,decode(s.sql_hash_value,0, s.prev_hash_value,s.sql_hash_value) sql_hash_value, s.sid from v$session s,v$lock l WHERE l.sid = s.sid and l.block=1) b where rawtohex(a.address)=b.sql_address and a.hash_value=b.sql_hash_value order by b.sid,a.piece ASC

如果想知道被堵塞sid及正执行的sql语句是什么，用下面语句（oracle10g测试通过），
select distinct b.sid, a.piece, a.sql_text from v$sqltext_with_newlines a, (select decode(s.sql_address,'00',s.prev_sql_addr,sql_address) sql_address,decode(s.sql_hash_value,0, s.prev_hash_value,s.sql_hash_value) sql_hash_value, s.sid from v$session s WHERE s.sid in (select sid from v$lock where lmode=0 and id1=(select l.ID1 from v$lock l WHERE l.block=1 and rownum<2) )) b where rawtohex(a.address)=b.sql_address and a.hash_value=b.sql_hash_value order by a.piece ASC

查看持锁会话服务器端：oracle服务进程id
select a.sid, b.spid from (select s.sid, s.paddr from v$session s,v$lock l WHERE l.sid = s.sid and l.block=1) a, v$process b where a.paddr=b.addr;


查看持锁会话客户端：用户程序进程id
select s.sid, s.process from v$session s,v$lock l WHERE l.sid = s.sid and l.block=1;
如果客户端在unix上，process就是用户进程id。
如果客户端在window上，process中前面数字就是用户进程id（如：728）。
SID PROCESS
---------- ------------
147 728:3920