Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         RNTSOFT               18                                       Null      None         29 RNTSOFT                         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 RNTSOFT                         ADDRESS              Body                                     Null      None         29 SYS                            DBMS_APPLICATION_INF Body                                     Null      None                                           O         29 SYS                            DICTIONARY_OBJ_NAME  Table/Procedure/Type                     Null      None         23 RNTSOFT                         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 RNTSOFT                         DEPT_SEQ             Table/Procedure/Type                     Null      None         23 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         RNTSOFT               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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         RNTSOFT               18                                       Null      None         29 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         RNTSOFT               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 RNTSOFT                         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 RNTSOFT                         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 RNTSOFT                         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> --