Oracle tables cannot be DDL and stored procedures cannot be used to create processing methods, oracleddl

Oracle tables cannot be DDL and stored procedures cannot be used to create processing methods, oracleddl

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

1. You cannot modify or increase the length of a field in a table.

2. stored procedures cannot be overwritten.

Scenario 1: Add hang fields to the table
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 -- 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 the session. Note that if it is RAC, G must be added to the view. For example, v $ lock is GV $ lock.

Scenario 2: stored procedures cannot be covered

Session1 is created and executed:

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

Call p_test_pin ();

Session2 overwrite:
Create or replace procedure p_test_pin ()
Begin
Dbms_lock.sleep (1000 );
End;

Method 1: locate the session and process id. If it is RAC, check 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

First kill the session: alter system kill session '8, 17376 ';
If the session cannot be killed, kill the process from the operating system: kill-9 22118


Method 2: Find the owner:
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 10000000127618fa8 2 0
Find the owner's SQL statement
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 ()