Database Reference
In-Depth Information
To see a breakable parse lock in action, let's first create and run a stored procedure, P :
EODA@ORA12CR1> create or replace procedure p
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
EODA@ORA12CR1> exec p
PL/SQL procedure successfully completed.
The procedure, P , will now show up in the DBA_DDL_LOCKS view. We have a parse lock on it:
EODA@ORA12CR1> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks
4 where session_id = (select sid from v$mystat where rownum=1)
5 /
SID OWNER NAME TYPE HELD REQUEST
------ -------- --------------------- -------------------- ---------- --------
22 EODA P Table/Procedure/Type Null None
...
22 SYS DATABASE 18 Null None
9 rows selected.
We then recompile our procedure and query the view again:
EODA@ORA12CR1> alter procedure p compile;
Procedure altered.
EODA@ORA12CR1> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks
4 where session_id = (select sid from v$mystat where rownum=1)
5 /
SID OWNER NAME TYPE HELD REQUEST
------ -------- --------------------- -------------------- ---------- --------
22 SYS DBMS_OUTPUT Body Null None
22 SYS DBMS_OUTPUT Table/Procedure/Type Null None
22 EODA EODA 18 Null None
22 SYS DBMS_APPLICATION_INFO Body Null None
22 SYS PLITBLM Table/Procedure/Type Null None
22 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
22 EODA 73 Share None
22 SYS DATABASE 18 Null None
8 rows selected.
 
Search WWH ::




Custom Search