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

在做数据迁移的过程中，我们会面临两种常见的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 UserTEST

DB UserTEST

SID8     --锁持有者sid

Serial#16603 --锁持有者serial#

address00

Sql hash0

Blocking AppPlSqlDev.exe

Blocking MachineCOMTOP\HLPNT2X

Blocking OS UserAdministrator

Serial#16603

Waiting UserTEST

WSID11

Waiting AppPlSqlDev.exe

Waiting MachineCOMTOP\HLPNT2X

Waiting OS UserAdministrator

WSerial#14858

LOCK_TYPETX

MODE_HELD6

MODE_REQUESTED4

LOCK_ID1589845

LOCK_ID25272

BLOCKING_OTHERS1

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()

原文：http://blog.csdn.net/stevendbaguo/article/details/45818181