Database Reference
In-Depth Information
These are all the objects that my session is locking. I have breakable parse locks on a couple of the DBMS_*
packages. These are a side effect of using SQL*Plus; it might call DBMS_APPLICATION_INFO , for example, when you
initially log in (to enable/disable DBMS_OUTPUT via the SET SERVEROUTPUT command). I may see more than one copy
of various objects here; this is normal, and it just means I have more than one thing I'm using in the shared pool that
references these objects. Note that in the view, the OWNER column is not the owner of the lock; rather, it is the
owner of the object being locked. This is why you see many SYS rows. SYS owns these packages, but they all belong to
my session.
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 /
 
Search WWH ::




Custom Search