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

雨点打透心脏的1/2处 2022-08-08 12:52 252阅读 0赞

在做数据迁移的过程中，我们会面临两种常见的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  
**如果有library cache lock这样的等待事件也是一样的处理方式，把w.event like '%library cache pin%'换成w.event like '%library cache lock%'即可，上次遇到不能删除同义词，也是按照这种方法处理的。**  
**先杀掉会话：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()