A view that is invaluable for looking at this information is DBA_DDL_LOCKS. There is no V$ view. The DBA_DDL_LOCKS view is built on the more mysterious X$ tables, and, by default, it might not be installed in your database. You can install this and other locking views by running the catblock.sql script found in the directory [ORACLE_HOME]/rdbms/ admin. This script must be executed as the user SYS in order to succeed. Once you have executed this script, you can run a query against the view. For example, in a freshly connected session, I might see the following:
$ sqlplus eoda/foo@PDB1
SQL> select session_id sid, owner, name, type, mode_held held, mode_requested request from dba_ddl_locks where session_id = (select sid from v$mystat where rownum=1);
SID OWNER NAME TYPE HELD REQUEST
286 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
286 SYS DBMS_APPLICATION_INFO Body Null None
286 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
286 SYS DBMS_STANDARD Table/Procedure/Type Null None
286 SYS DATABASE 18 Null None
286 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
286 SYS IS_VPD_ENABLED Table/Procedure/Type Null None
286 SYS IDGEN1$ Table/Procedure/Type Null None
286 EODA 73 Share None
286 EODA EODA 18 Null None
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:
SQL> create or replace procedure pasbeginnull;end;/Procedure created.
SQL> 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:
SQL> select session_id sid, owner, name, type, mode_held held, mode_requested request from dba_ddl_lockswhere session_id = (select sid from v$mystat where rownum=1)/ SIDOWNERNAMETYPEHELDREQUEST
We then recompile our procedure and query the view again:
SQL> alter procedure p compile;Procedure altered.
SQL> select session_id sid, owner, name, type, mode_held held, mode_requested request
from dba_ddl_lockswhere session_id = (select sid from v$mystat where rownum=1);
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
We find that P is now missing from the view. Our parse lock has been broken. This view is useful to you, as a developer, when it is found that some piece of code won’t compile in the test or development system—it hangs and eventually times out.
This indicates that someone else is using it (actually running it), and you can use this view to see who that might be. The same will happen with GRANT statements and other types of DDL against the object. You cannot grant EXECUTE on a procedure that is running, for example. You can use the same method to discover the potential blockers and waiters.
Note Oracle 11g Release 2 and above introduce the feature edition-based redefinition (EBR). With EBR, you can, in fact, grant EXECUTE and/or recompile code in the database without interfering with users currently executing the code. EBR allows you to have multiple versions of the same stored procedure in a schema at once. This allows you to work on a copy of the procedure in a new edition (version) without contending with the current version of the procedure being used by other users. We will not be covering EBR in this book, however, just mentioning it when it changes the rules.
Leave a Reply