Oracle tables cannot be DDL and stored procedures cannot create a processing method

In the process of data migration, we face two common hang-live scenarios:

1. The table cannot modify the Add field and cannot increase the length.

2. Stored procedures cannot be overwritten.

Scenario 1: Adding a field to a table hang handle
Session1:
CREATE TABLE Test as SELECT * from Dba_objects;
SELECT * FROM Test where object_id = a for update;
Session2:
ALTER TABLE test add AA Number;--hang Live
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--Lock holder SID
serial# 16603 --Lock holder 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 ';--kill session, note that if it is a RAC, the view must be added G, such as V$lock, is gv$lock.

Scenario 2: Stored procedures cannot overwrite

Session1 New and executed:

Create or replace procedure P_test_pin
As
Begin
Dbms_lock.sleep (3000);
End

Call P_test_pin ();

Session2 Coverage:
Create or Replace procedure P_test_pin ()
Begin
Dbms_lock.sleep (1000);
End

Method 1, locate the session and process ID, and if it is a RAC, look for GV:

X$KGLPN Library Cache Pin Information
X$kglob Library Cache Object Information

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

Kill session First: Alter system kill session ' 8,17376 ';
If the session cannot be killed, only the process is killed from the operating system level: Kill-9 22118


Method 2, locate the holder:
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
Find out what the holder is doing in 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 ()

Oracle tables cannot be DDL and stored procedures cannot create a processing method