Oracle PL/SQL/System Tables Views/dba ddl locks
Query dba_ddl_locks table before and after recompile a procedure
SQL>
SQL> create or replace procedure p as begin null; end;
2 /
Procedure created.
SQL>
SQL> exec p;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from dba_ddl_locks;
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ -------------------- ---------------------------------------- --------- ---------
40 SYS AQ$_ALERT_QT_E 10 Null None
40 SYS DBMS_HA_ALERTS_PRVT Body Null None
40 SYS DBMS_RCVMAN Table/Procedure/Type Null None
40 SYS DBMS_PRVT_TRACE Table/Procedure/Type Null None
40 SYS PLITBLM Table/Procedure/Type Null None
40 SYS PLITBLM Table/Procedure/Type Null None
40 SYS DBMS_HA_ALERTS_PRVT Table/Procedure/Type Null None
40 SYS DBMS_PRVT_TRACE Body Null None
40 SYS DBMS_APPLICATION_INF Table/Procedure/Type Null None
O
40 SYS STANDARD Body Null None
40 SYS ALERT_QUE 10 Null None
40 SYS DBMS_APPLICATION_INF Body Null None
O
40 SYS DBMS_BACKUP_RESTORE Body Null None
40 SYS ALERT_QUE_R 23 Null None
40 SYS DBMS_BACKUP_RESTORE Table/Procedure/Type Null None
40 SYS DBMS_RCVMAN Body Null None
29 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
29 SYS DBMS_SYS_SQL Body Null None
29 XDB DBMS_XDBZ0 Body Null None
29 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
29 SYS DBMS_OUTPUT Body Null None
29 sqle ADDRESS Table/Procedure/Type Null None
29 SYS PLITBLM Table/Procedure/Type Null None
29 SYS DATABASE 18 Null None
29 SYS DBMS_SQL Body Null None
29 SYS DBMS_APPLICATION_INF Table/Procedure/Type Null None
O
29 sqle GETEMPBLDGNAME Table/Procedure/Type Null None
29 SYS STANDARD Body Null None
29 SYS AW_DROP_PROC Table/Procedure/Type Null None
29 SYS IDGEN1$ Table/Procedure/Type Null None
29 sqle sqle 18 Null None
29 sqle ASSIGNEMPTOBLDG Table/Procedure/Type Null None
29 SYS DBMS_SQL Table/Procedure/Type Null None
29 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
29 sqle ADDRESS Body Null None
29 SYS DBMS_APPLICATION_INF Body Null None
O
29 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
23 sqle PRINT_TABLE Table/Procedure/Type Null None
23 SYS DBMS_RANDOM Table/Procedure/Type Null None
23 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
23 SYS DBMS_STANDARD Table/Procedure/Type Null None
23 XDB DBMS_XDBZ0 Body Null None
23 sqle DEPT_SEQ Table/Procedure/Type Null None
23 sqle P Table/Procedure/Type Null None
23 SYS XML_SCHEMA_NAME_PRES Table/Procedure/Type Null None
ENT
23 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
23 SYS DBMS_OUTPUT Body Null None
23 XDB XDuubYCRNYQDOCOoaecR 25 Share None
Uoig==
23 SYS PLITBLM Table/Procedure/Type Null None
23 SYS XML_SCHEMA_NAME_PRES Body Null None
ENT
23 SYS ORA_PLAN_ID_SEQ$ Table/Procedure/Type Null None
23 SYS UTL_RAW Body Null None
23 SYS UTL_RAW Table/Procedure/Type Null None
23 V1 43 Null None
23 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
23 SYS DBMS_ASSERT Body Null None
23 SYS DBMS_SYS_ERROR Table/Procedure/Type Null None
23 sqle VIRTUAL_TABLE Table/Procedure/Type Null None
23 SYS DBMS_UTILITY Table/Procedure/Type Null None
23 SYS DBMS_METADATA_UTIL Table/Procedure/Type Null None
23 sqle PIVOT Table/Procedure/Type Null None
23 SYS DBMS_FLASHBACK Body Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ -------------------- ---------------------------------------- --------- ---------
23 SYS DATABASE 18 Null None
23 SYS DBMS_SQL Body Null None
23 XDB XD8Vf3vgTDR1aSQOGkEt 25 Share None
R2uQ==
23 SYS DBMS_OUTPUT Table/Procedure/Type Null None
23 SYS DBMS_METADATA_INT Body Null None
23 SYS DBMS_APPLICATION_INF Table/Procedure/Type Null None
O
23 SYS STANDARD Body Null None
23 XDB XDh1jUheYAR5PgNAgAIL 25 Share None
JCxg==
23 SYS AW_DROP_PROC Table/Procedure/Type Null None
23 SYS DBMS_LOB Table/Procedure/Type Null None
23 SYS IDGEN1$ Table/Procedure/Type Null None
23 XDB XDbD/PLZ01TcHgNAgAII 25 Share None
egtw==
23 sqle sqle 18 Null None
23 XDB XDB$EXTNAME2INTNAME Table/Procedure/Type Null None
23 SYS DBMS_METADATA Table/Procedure/Type Null None
23 SYS DBMS_SYS_ERROR Body Null None
23 SYS DBMS_SQL Table/Procedure/Type Null None
23 SYS DBMS_METADATA_UTIL Body Null None
23 SYS DBMS_XPLAN Table/Procedure/Type Null None
23 SYS DBMS_METADATA Body Null None
23 SYS DBMS_FLASHBACK Table/Procedure/Type Null None
23 sqle COMPANY_LISTING Table/Procedure/Type Null None
23 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
23 SYS DBMS_XPLAN Body Null None
23 SYS DBMS_UTILITY Body Null None
23 SYS XMLTYPE Table/Procedure/Type Null None
23 SYS DBMS_APPLICATION_INF Body Null None
O
23 sqle EMP_SEQ Table/Procedure/Type Null None
23 SYS DBMS_METADATA_INT Table/Procedure/Type Null None
23 SYS OBJECT_GRANT Table/Procedure/Type Null None
23 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
23 SYS XMLTYPE Body Null None
23 sqle REMOVE_CONSTANTS Table/Procedure/Type Null None
23 SYS DBMS_LOB Body Null None
23 SYS DBMS_ASSERT Table/Procedure/Type Null None
97 rows selected.
SQL>
SQL> alter procedure p compile;
Procedure altered.
SQL>
SQL> select * from dba_ddl_locks;
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ -------------------- ---------------------------------------- --------- ---------
40 SYS AQ$_ALERT_QT_E 10 Null None
40 SYS DBMS_HA_ALERTS_PRVT Body Null None
40 SYS DBMS_RCVMAN Table/Procedure/Type Null None
40 SYS DBMS_PRVT_TRACE Table/Procedure/Type Null None
40 SYS PLITBLM Table/Procedure/Type Null None
40 SYS PLITBLM Table/Procedure/Type Null None
40 SYS DBMS_HA_ALERTS_PRVT Table/Procedure/Type Null None
40 SYS DBMS_PRVT_TRACE Body Null None
40 SYS DBMS_APPLICATION_INF Table/Procedure/Type Null None
O
40 SYS STANDARD Body Null None
40 SYS ALERT_QUE 10 Null None
40 SYS DBMS_APPLICATION_INF Body Null None
O
40 SYS DBMS_BACKUP_RESTORE Body Null None
40 SYS ALERT_QUE_R 23 Null None
40 SYS DBMS_BACKUP_RESTORE Table/Procedure/Type Null None
40 SYS DBMS_RCVMAN Body Null None
29 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
29 SYS DBMS_SYS_SQL Body Null None
29 XDB DBMS_XDBZ0 Body Null None
29 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
29 SYS DBMS_OUTPUT Body Null None
29 sqle ADDRESS Table/Procedure/Type Null None
29 SYS PLITBLM Table/Procedure/Type Null None
29 SYS DATABASE 18 Null None
29 SYS DBMS_SQL Body Null None
29 SYS DBMS_APPLICATION_INF Table/Procedure/Type Null None
O
29 sqle GETEMPBLDGNAME Table/Procedure/Type Null None
29 SYS STANDARD Body Null None
29 SYS AW_DROP_PROC Table/Procedure/Type Null None
29 SYS IDGEN1$ Table/Procedure/Type Null None
29 sqle sqle 18 Null None
29 sqle ASSIGNEMPTOBLDG Table/Procedure/Type Null None
29 SYS DBMS_SQL Table/Procedure/Type Null None
29 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
29 sqle ADDRESS Body Null None
29 SYS DBMS_APPLICATION_INF Body Null None
O
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ -------------------- ---------------------------------------- --------- ---------
29 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
23 sqle PRINT_TABLE Table/Procedure/Type Null None
23 SYS DBMS_RANDOM Table/Procedure/Type Null None
23 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
23 SYS DBMS_SYS_SQL Body Null None
23 XDB DBMS_XDBZ0 Body Null None
23 SYS XML_SCHEMA_NAME_PRES Table/Procedure/Type Null None
ENT
23 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
23 SYS DBMS_OUTPUT Body Null None
23 XDB XDuubYCRNYQDOCOoaecR 25 Share None
Uoig==
23 SYS PLITBLM Table/Procedure/Type Null None
23 SYS XML_SCHEMA_NAME_PRES Body Null None
ENT
23 SYS ORA_PLAN_ID_SEQ$ Table/Procedure/Type Null None
23 SYS UTL_RAW Body Null None
23 SYS UTL_RAW Table/Procedure/Type Null None
23 V1 43 Null None
23 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
23 SYS DBMS_ASSERT Body Null None
23 SYS DBMS_SYS_ERROR Table/Procedure/Type Null None
23 sqle VIRTUAL_TABLE Table/Procedure/Type Null None
23 SYS DBMS_UTILITY Table/Procedure/Type Null None
23 SYS DBMS_METADATA_UTIL Table/Procedure/Type Null None
23 SYS DBMS_RANDOM Body Null None
23 sqle PIVOT Table/Procedure/Type Null None
23 SYS DBMS_FLASHBACK Body Null None
23 SYS DATABASE 18 Null None
23 SYS DBMS_SQL Body Null None
23 XDB XD8Vf3vgTDR1aSQOGkEt 25 Share None
R2uQ==
23 SYS DBMS_OUTPUT Table/Procedure/Type Null None
23 SYS DBMS_METADATA_INT Body Null None
23 SYS DBMS_APPLICATION_INF Table/Procedure/Type Null None
O
23 SYS STANDARD Body Null None
23 XDB XDh1jUheYAR5PgNAgAIL 25 Share None
JCxg==
23 SYS AW_DROP_PROC Table/Procedure/Type Null None
23 SYS DBMS_LOB Table/Procedure/Type Null None
23 SYS IDGEN1$ Table/Procedure/Type Null None
23 XDB XDbD/PLZ01TcHgNAgAII 25 Share None
egtw==
23 sqle sqle 18 Null None
23 XDB XDB$EXTNAME2INTNAME Table/Procedure/Type Null None
23 SYS DBMS_METADATA Table/Procedure/Type Null None
23 SYS DBMS_SYS_ERROR Body Null None
23 SYS DBMS_SQL Table/Procedure/Type Null None
23 SYS DBMS_METADATA_UTIL Body Null None
23 SYS DBMS_XPLAN Table/Procedure/Type Null None
23 SYS DBMS_METADATA Body Null None
23 SYS DBMS_FLASHBACK Table/Procedure/Type Null None
23 sqle COMPANY_LISTING Table/Procedure/Type Null None
23 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
23 SYS DBMS_XPLAN Body Null None
23 SYS DBMS_UTILITY Body Null None
23 SYS XMLTYPE Table/Procedure/Type Null None
23 SYS DBMS_APPLICATION_INF Body Null None
O
23 sqle EMP_SEQ Table/Procedure/Type Null None
23 SYS DBMS_METADATA_INT Table/Procedure/Type Null None
23 SYS OBJECT_GRANT Table/Procedure/Type Null None
23 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
23 SYS XMLTYPE Body Null None
23 sqle REMOVE_CONSTANTS Table/Procedure/Type Null None
23 SYS DBMS_LOB Body Null None
23 SYS DBMS_ASSERT Table/Procedure/Type Null None
96 rows selected.
SQL>
SQL> --