alter procedure p compile and check the dba_ddl_locks again : Alter recompile « Function Procedure Packages « Oracle PL/SQL Tutorial






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.








27.13.Alter recompile
27.13.1.Recompile objects is to use the ALTER object type object name COMPILE command
27.13.2.alter procedure p compile and check the dba_ddl_locks again