alter procedure p compile and check the dba_ddl_locks again : Alter recompile « Function Procedure Packages « Oracle PL/SQL Tutorial
- Oracle PL/SQL Tutorial
- Function Procedure Packages
- Alter recompile
SQL> create or replace procedure p as begin null; end;
2 /
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
---------- ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
20 JAVA2S NAMED_BLOCK Table/Procedure/Type Null None
20 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
20 SYS DBMS_STANDARD Table/Procedure/Type Null None
20 XDB DBMS_XDBZ0 Body Null None
20 JAVA2S P_SPLIT Table/Procedure/Type Null None
20 JAVA2S P Table/Procedure/Type Null None
20 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
20 SYS DBMS_OUTPUT Body Null None
20 JAVA2S UPDATE_EMP Table/Procedure/Type Null None
20 SYS PLITBLM Table/Procedure/Type Null None
20 JAVA2S CHANGEPRICE Table/Procedure/Type Null None
20 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
20 SYS DBMS_UTILITY Table/Procedure/Type Null None
20 SYS DBMS_WARNING Table/Procedure/Type Null None
20 JAVA2S AUTHORS_SEL Table/Procedure/Type Null None
20 JAVA2S COMPUTE_DISCOUNTS Table/Procedure/Type Null None
20 SYS DATABASE 18 Null None
20 SYS DBMS_SQL Body Null None
20 SYS DBMS_OUTPUT Table/Procedure/Type Null None
20 JAVA2S AUTHOR_SEL Table/Procedure/Type Null None
20 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
20 SYS STANDARD Body Null None
20 SYS AW_DROP_PROC Table/Procedure/Type Null None
20 SYS DBMS_WARNING Body Null None
20 JAVA2S P_HELLOTO Table/Procedure/Type Null None
20 JAVA2S MEASURE_USAGE Table/Procedure/Type Null None
20 JAVA2S JAVA2S 18 Null None
20 SYS DBMS_SQL Table/Procedure/Type Null None
20 JAVA2S MY_FIRST_PROC Table/Procedure/Type Null None
20 JAVA2S TEST_TIME Table/Procedure/Type Null None
20 SYS DBMS_ROWID Table/Procedure/Type Null None
20 JAVA2S P_HELLO Table/Procedure/Type Null None
20 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
20 SYS DBMS_UTILITY Body Null None
20 JAVA2S EMP_CHANGE_S Table/Procedure/Type Null None
20 SYS DBMS_APPLICATION_INFO Body Null None
20 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
20 SYS DBMS_ROWID Body Null None
20 JAVA2S BIND_TEST Table/Procedure/Type Null None
20 SYS DBMS_SPACE Body Null None
20 JAVA2S EMPLOYEE_COUNT Table/Procedure/Type Null None
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_INFO Table/Procedure/Type Null None
40 SYS STANDARD Body Null None
40 SYS ALERT_QUE 10 Null None
40 SYS DBMS_APPLICATION_INFO Body Null None
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
41 SYS SCHEDULER$_INSTANCE_S Table/Procedure/Type Null None
58 rows selected.
SQL> alter procedure p compile;
SP2-0805: Procedure altered with compilation warnings
SQL>
SQL> select * from dba_ddl_locks;
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
20 JAVA2S NAMED_BLOCK Table/Procedure/Type Null None
20 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
20 SYS DBMS_SYS_SQL Body Null None
20 XDB DBMS_XDBZ0 Body Null None
20 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
20 SYS DBMS_OUTPUT Body Null None
20 JAVA2S UPDATE_EMP Table/Procedure/Type Null None
20 SYS PLITBLM Table/Procedure/Type Null None
20 JAVA2S CHANGEPRICE Table/Procedure/Type Null None
20 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
20 SYS DBMS_UTILITY Table/Procedure/Type Null None
20 SYS DBMS_WARNING Table/Procedure/Type Null None
20 JAVA2S AUTHORS_SEL Table/Procedure/Type Null None
20 JAVA2S COMPUTE_DISCOUNTS Table/Procedure/Type Null None
20 SYS DATABASE 18 Null None
20 SYS DBMS_SQL Body Null None
20 SYS DBMS_OUTPUT Table/Procedure/Type Null None
20 JAVA2S AUTHOR_SEL Table/Procedure/Type Null None
20 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
20 SYS STANDARD Body Null None
20 SYS AW_DROP_PROC Table/Procedure/Type Null None
20 SYS DBMS_WARNING Body Null None
20 JAVA2S P_HELLOTO Table/Procedure/Type Null None
20 JAVA2S MEASURE_USAGE Table/Procedure/Type Null None
20 JAVA2S JAVA2S 18 Null None
20 SYS DBMS_SQL Table/Procedure/Type Null None
20 JAVA2S MY_FIRST_PROC Table/Procedure/Type Null None
20 JAVA2S TEST_TIME Table/Procedure/Type Null None
20 SYS DBMS_ROWID Table/Procedure/Type Null None
20 JAVA2S P_HELLO Table/Procedure/Type Null None
20 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
20 SYS DBMS_UTILITY Body Null None
20 JAVA2S EMP_CHANGE_S Table/Procedure/Type Null None
20 SYS DBMS_SPACE Table/Procedure/Type Null None
20 SYS DBMS_APPLICATION_INFO Body Null None
20 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
20 SYS DBMS_ROWID Body Null None
20 JAVA2S BIND_TEST Table/Procedure/Type Null None
20 SYS DBMS_SPACE Body Null None
20 JAVA2S EMPLOYEE_COUNT Table/Procedure/Type Null None
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_INFO Table/Procedure/Type Null None
40 SYS STANDARD Body Null None
40 SYS ALERT_QUE 10 Null None
40 SYS DBMS_APPLICATION_INFO Body Null None
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
41 SYS SCHEDULER$_INSTANCE_S Table/Procedure/Type Null None
57 rows selected.