Oracle PL/SQL Tutorial/Function Procedure Packages/Alter recompile
alter procedure p compile and check the dba_ddl_locks again
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 sqle 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 sqle P_SPLIT Table/Procedure/Type Null None
20 sqle P Table/Procedure/Type Null None
20 SYS DBMS_SYS_SQL Table/Procedure/Type Null None
20 SYS DBMS_OUTPUT Body Null None
20 sqle UPDATE_EMP Table/Procedure/Type Null None
20 SYS PLITBLM Table/Procedure/Type Null None
20 sqle 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 sqle AUTHORS_SEL Table/Procedure/Type Null None
20 sqle 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 sqle 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 sqle P_HELLOTO Table/Procedure/Type Null None
20 sqle MEASURE_USAGE Table/Procedure/Type Null None
20 sqle sqle 18 Null None
20 SYS DBMS_SQL Table/Procedure/Type Null None
20 sqle MY_FIRST_PROC Table/Procedure/Type Null None
20 sqle TEST_TIME Table/Procedure/Type Null None
20 SYS DBMS_ROWID Table/Procedure/Type Null None
20 sqle P_HELLO Table/Procedure/Type Null None
20 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
20 SYS DBMS_UTILITY Body Null None
20 sqle 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 sqle BIND_TEST Table/Procedure/Type Null None
20 SYS DBMS_SPACE Body Null None
20 sqle 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 sqle 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 sqle UPDATE_EMP Table/Procedure/Type Null None
20 SYS PLITBLM Table/Procedure/Type Null None
20 sqle 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 sqle AUTHORS_SEL Table/Procedure/Type Null None
20 sqle 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 sqle 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 sqle P_HELLOTO Table/Procedure/Type Null None
20 sqle MEASURE_USAGE Table/Procedure/Type Null None
20 sqle sqle 18 Null None
20 SYS DBMS_SQL Table/Procedure/Type Null None
20 sqle MY_FIRST_PROC Table/Procedure/Type Null None
20 sqle TEST_TIME Table/Procedure/Type Null None
20 SYS DBMS_ROWID Table/Procedure/Type Null None
20 sqle P_HELLO Table/Procedure/Type Null None
20 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
20 SYS DBMS_UTILITY Body Null None
20 sqle 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 sqle BIND_TEST Table/Procedure/Type Null None
20 SYS DBMS_SPACE Body Null None
20 sqle 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.
Recompile objects is to use the ALTER object type object name COMPILE command
SQL>
SQL> create or replace function getArea (i_rad NUMBER)
2 return NUMBER
3 is
4 begin
5 return 3.14*(i_rad**2);
6 end;
7 /
Function created.
SQL>
SQL>
SQL>
SQL> alter function getArea compile;
Function altered.
SQL>
SQL> select status
2 from user_objects
3 where object_name = "GETAREA";
STATUS
----------
VALID