oracle表不能DDL和預存程序不能建立處理方法，oracleddl

oracle表不能DDL和預存程序不能建立處理方法，oracleddl

    在做資料移轉的過程中，我們會面臨兩種常見的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()