Oracle PL/SQL/System Tables Views/dba ddl locks

Материал из SQL эксперт
Версия от 10:01, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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> --