How To Know If An Object (Table / Procedure / View /…) Is Currently Being

linfeng_oracle發表於2013-10-22
ObjectView
How To Know If An Object (Table / Procedure / View /…) Is Currently Being
 

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Goal

The information is intended to help with situations where you want to know whether a certain object is being used, e.g.

1. If the objects is currently being used, e.g. by a DML statement, DDL on the objects will fail. How can you check it before executing the DDL command?

2. If a procedure "ROB.TEST_PROCEDURE" is being executed by user TONY and owner ROB wants to replace the procedure code using "create or replace procedure test_procedure ….". How can you find the session that is running the procedure "ROB.TEST_PROCEDURE"?

Solution

Situation 1

To check objects currently being used, e.g. by a DML statement, before executing a DDL command, you can query v$locked_object as in the example below.

Session A

SQL> connect miles/oracle;
Connected.

SQL> drop table test;

Table dropped.

SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.

Session B

SQL> connect / as sysdba
Connected.

SQL> truncate table miles.test;
truncate table miles.test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
--&gt this is expected because miles.test is locked in Session A

SQL>
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
       lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
       lo.PROCESS, lo.LOCKED_MODE
from   dba_objects do, v$locked_object lo
where  do.OBJECT_ID = lo.OBJECT_ID
and    do.OWNER = 'MILES'
and    do.OBJECT_NAME = 'TEST';

OWNER    OBJECT_NAME  OBJECT_ID  SESSION_ID ORACLE_USERNAME
-------- ------------ ---------- ---------- ------------------------------
OS_USER_NAME         PROCESS        LOCKED_MODE
-------------------- -------------- -----------
MILES    TEST            63754         1079 MILES
oracle               23013                    3

From above, you can see miles.test is locked by Oracle user ‘MILES’ with OS user name ‘oracle’.
Session id is 1079, OS process id is 23013.

You can kill the session to release the lock.

SQL> select SID, SERIAL# from v$session where sid = 1079;

SID        SERIAL#
---------- ----------
1079       663

SQL> alter system kill session '1079,663';

System altered.

Then the DDL can be executed successfully:

SQL> truncate table miles.test;

Table truncated.

 

Situation 2

If a DDL statement on an object, e.g. create or replace procedure, is hanging because another user is executing the procedure, you can find the blocking session as in the example below.

Using 3 sessions:

  1. Session A: connect as sysdba
  2. Session B: connect as user rob/rob
  3. Session C: connect as user tony/tony

Session A

SQL>
connect / as sysdba
create user rob identified by rob;
grant connect, resource to rob;
create user tony identified by tony;
grant connect, resource to tony;
grant execute on dbms_lock to rob;
grant execute on dbms_lock to tony;

Session B

SQL>
connect rob/rob

create or replace procedure rob_test_p1 is
begin
  null;
end;
/

grant execute on rob_test_p1 to tony;

Session C

SQL>
connect tony/tony
begin
  rob.rob_test_p1;
  dbms_lock.sleep(120);
end;
/

Session A

SQL>
connect / as sysdba

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
       ob.kglnaobj obj_name, ob.KGLNAOWN obj_owner,
       ses.KSUUDNAM cur_user
from   x$kglpn pn, x$kglob ob, x$ksuse ses
where  ob.KGLNAOBJ='ROB_TEST_P1'
and    (ob.KGLHDPMD <> 0
        or
        (ob.KGLHDPMD = 0 and ob.KGLHDLMD not in (0,1))
       )
and    ob.kglhdadr = pn.kglpnhdl
and    pn.kglpnuse = ses.addr;

SID        SERIAL#    OBJ_NAME             OBJ_OWNER       CUR_USER
---------- ---------- -------------------- --------------- ------------------
1094       5909       ROB_TEST_P1          ROB             TONY

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and    b.serial#=5909;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob.rob_test_p1; dbms_lock.sleep(120); end;

If the DDL is already hanging due to a lock, you can follow below test case to find the lock details.

Session C

SQL> connect tony/tony

SQL>
begin
  rob.rob_test_p1;
  dbms_lock.sleep(120);
end;
/

Session B

SQL> connect rob/rob

SQL> alter procedure rob_test_p1 compile;
     --&gt This is hanging

Session A

col obj_name format a30
col pin_cnt format 999
col pin_mode format 999
col pin_req format 999
col state format a30
col event format a30
col wait_time format 999999999
col seconds_in_wait format 999999999

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
       ob.kglnaobj obj_name,
       pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req,
       w.state, w.event, w.wait_time, w.seconds_in_Wait
from   x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
where  pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
and    ob.kglhdadr = pn.kglpnhdl
and    pn.kglpnuse = ses.addr
and    w.sid = ses.indx
order by seconds_in_wait desc;

SID        SERIAL#    OBJ_NAME                       PIN_CNT PIN_MODE PIN_REQ
---------- ---------- ------------------------------ ------- -------- -------
STATE                          EVENT                          WAIT_TIME
------------------------------ ------------------------------ ----------
SECONDS_IN_WAIT
---------------
      1082       6765 ROB_TEST_P1                          3        2       0
WAITING                        PL/SQL lock timer                       0
             39

      1074       6060 ROB_TEST_P1                          0        0       3
WAITING                        library cache pin                       0
             27

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and    b.serial#=6765;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob_test_p1; dbms_lock.sleep(120); end;

SQL>
select sql_text
from   v$sql a, v$session b
where  a.sql_id=b.sql_id
and  b.serial#=6060;

SQL_TEXT
--------------------------------------------------------------------------------
alter procedure rob_test_p1 compile

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24996904/viewspace-774807/，如需轉載，請註明出處，否則將追究法律責任。

相關文章