oracle表不能DDL和存储过程不能创建处理方法

浏览数：15 / 时间：2015年06月12日

在做数据迁移的过程中，我们会面临两种常见的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 ws
WHERE 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 User TEST
DB User TEST
SID 8 --锁持有者sid
Serial# 16603 --锁持有者serial#
address 00
Sql hash 0
Blocking App PlSqlDev.exe
Blocking Machine COMTOP\HLPNT2X
Blocking OS User Administrator
Serial# 16603
Waiting User TEST
WSID 11
Waiting App PlSqlDev.exe
Waiting Machine COMTOP\HLPNT2X
Waiting OS User Administrator
WSerial# 14858
LOCK_TYPE TX
MODE_HELD 6
MODE_REQUESTED 4
LOCK_ID1 589845
LOCK_ID2 5272
BLOCKING_OTHERS 1

alter system kill session ‘8,16603‘;--干掉session,注意的是如果是RAC，视图都要加上G,如v$lock，是GV$lock。

场景2：存储过程不能覆盖

session1 新建并执行:

create or replace procedure p_test_pin
as
begin
dbms_lock.sleep(3000);
end;

call p_test_pin();

session2 覆盖:
create or replace procedure p_test_pin()
begin
dbms_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()