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

Source: Internet
Author: User
Tags sesion

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.