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
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
Connected.
SQL> truncate table miles.test;
truncate table miles.test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
--> 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.
SID SERIAL#
---------- ----------
1079 663
SQL> alter system kill session '1079,663';
System altered.
Then the DDL can be executed successfully:
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:
- Session A: connect as sysdba
- Session B: connect as user rob/rob
- Session C: connect as user tony/tony
Session A
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
connect rob/rob
create or replace procedure rob_test_p1 is
begin
null;
end;
/
grant execute on rob_test_p1 to tony;
Session C
connect tony/tony
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/
Session A
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>
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/
Session B
SQL> alter procedure rob_test_p1 compile;
--> This is hanging
Session A
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博客 ” ，链接：https://blog.itpub.net/24996904/viewspace-774807/ ，如需转载，请注明出处，否则将追究法律责任。
