在做数据迁移的过程中，我们会面临两种常见的hang住场景：
1.表不能修改加字段，不能增大长度。
2.存储过程不能覆盖。
场景1：为表添加字段hang住的处理session1:create table test as select * from dba_objects;select * from test where object_id = 20 for update;session2:alter table test add aa number;–hang住session3:SELECT bs.username "Blocking User", bs.username "DB User", bs.SID "SID", bs.serial# "Serial#", bs.sql_address "address", bs.sql_hash_value "Sql hash", bs.program "Blocking App", bs.machine "Blocking Machine", bs.osuser "Blocking OS User", bs.serial# "Serial#", ws.username "Waiting User", ws.SID "WSID", ws.program "Waiting App", ws.machine "Waiting Machine", ws.osuser "Waiting OS User", ws.serial# "WSerial#", wk.TYPE lock_type, hk.lmode mode_held, wk.request mode_requested, TO_CHAR(hk.id1) lock_id1, TO_CHAR(hk.id2) lock_id2, hk.BLOCK blocking_others FROM v$lock hk, v$session bs, v$lock wk, v$session wsWHERE hk.BLOCK = 1 AND hk.lmode != 0 AND hk.lmode != 1 AND wk.request != 0 AND wk.TYPE(+) = hk.TYPE AND wk.id1(+) = hk.id1 AND wk.id2(+) = hk.id2 AND hk.SID = bs.SID(+) AND wk.SID = ws.SID(+) AND (bs.username IS NOT NULL) AND (bs.username <> ‘SYSTEM’) AND (bs.username <> ‘SYS’)ORDER BY 1;Blocking UserTESTDB UserTESTSID8 –锁持有者sidSerial#16603–锁持有者serial#address00Sql hash0Blocking AppPlSqlDev.exeBlocking MachineCOMTOP\HLPNT2XBlocking OS UserAdministratorSerial#16603Waiting UserTESTWSID11Waiting AppPlSqlDev.exeWaiting MachineCOMTOP\HLPNT2XWaiting OS UserAdministratorWSerial#14858LOCK_TYPETXMODE_HELD6MODE_REQUESTED4LOCK_ID1589845LOCK_ID25272BLOCKING_OTHERS1
alter system kill session ‘8,16603’;–干掉session,注意的是如果是RAC，视图都要加上G,如v$lock，是GV$lock。
场景2：存储过程不能覆盖
session1 新建并执行:
create or replace procedure p_test_pinasbegindbms_lock.sleep(3000);end;call p_test_pin();
session2 覆盖:create or replace procedure p_test_pin()begindbms_lock.sleep(1000);end;方法1，，找到所在会话和进程id,如果是RAC则都要查GV：
x$kglpn library cache pin信息x$kglob library cache object信息
SQL> SELECT s.sid,s.SERIAL#, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process" FROM v$session_wait w, sys.x$kglpn p, v$session s, v$process o WHERE p.kglpnuse = s.saddr AND kglpnhdl = w.p1raw and w.event like ‘%library cache pin%’ and s.paddr = o.addr; SID SERIAL# Mode Req OS Process———- ———- ———- ———- ——————- 8 17376 2 0 22118 133 4430 0 3 22120先杀掉会话：alter system kill session ‘8,17376’;如果会话杀不掉，则只有从操作系统层面杀进程：kill -9 22118 方法2，找到持有者： SQL> SELECT sid Holder_sid, SERIAL# hold_SERIAL, KGLPNUSE Sesion, KGLPNMOD Held, KGLPNREQ Req FROM sys.x$kglpn, v$session WHERE KGLPNHDL IN (SELECT p1raw FROM v$session_wait WHERE event LIKE ‘library cache pin%’) AND KGLPNMOD <> 0 AND v$session.saddr = x$kglpn.kglpnuse;HOLDER_SID HOLD_SERIAL SESION HELD REQ———- ———– —————- ———- ———- 8 17376 0000000127618FA8 2 0找到持有者在执行什么SQL SQL> SELECT sid, sql_text FROM v$session, v$sqlarea WHERE v$session.sql_address = v$sqlarea.address AND sid =8; SID SQL_TEXT —— ———– 8 call p_test_pin()
当你能爱的时候就不要放弃爱
