Query dba_ddl_locks table before and after recompile a procedure : dba_ddl_locks « System Tables Views « Oracle PL / SQL






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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         JAVA2S               18                                       Null      None
        29 JAVA2S                         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 JAVA2S                         ADDRESS              Body                                     Null      None
        29 SYS                            DBMS_APPLICATION_INF Body                                     Null      None
                                          O

        29 SYS                            DICTIONARY_OBJ_NAME  Table/Procedure/Type                     Null      None
        23 JAVA2S                         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 JAVA2S                         DEPT_SEQ             Table/Procedure/Type                     Null      None
        23 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         JAVA2S               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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         JAVA2S               18                                       Null      None
        29 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         JAVA2S               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 JAVA2S                         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 JAVA2S                         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 JAVA2S                         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> --

 








Related examples in the same category