We had seen case when the DDL timestamp of a procedure was changed in the middle of the night and one reason we found that was Oracle will try to recompile the store procedure/function/package in certain condition.

Here is a scenerio which shows Oracle recompiles the object
— 1) Create a table named “X” and create a store procedure that references that table
SQL> create table x ( x number );

Table created.

SQL> create or replace procedure x_proc
as
l_var number;
begin
select x into l_var from x;
end;
/

Procedure created.

— 2) Get the status and last_ddl_time of the store procedure
SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
VALID 17-DEC-2010 20:55:22

— 3) Drop the table and the store procedure becomes invalid note last_ddl_time doesn't change of the store procedure
SQL> drop table x;

Table dropped.

— get last_ddl_time of the store procedure
SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:55:22

— 4) Table is being recreated, the LAST_DDL_TIME still remains the same
SQL> create table x ( x number );

Table created.

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:55:22

SQL> insert into x values (1);

1 row created.

SQL> commit;

Commit complete.

— 5) Execute the store procedure and it runs successfully and Oracle will recompile the store procedure to see if it make it valid as you see the LAST_DDL_TIME has changed on the store procedure
SQL> exec x_proc;

PL/SQL procedure successfully completed.

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
VALID 17-DEC-2010 20:57:14

It is also smart enough to know to try recompile once so i think it somehow keeps track of the dependency
— 6) Table dropped again which will make the store procedure invalid, the LAST_DDL_TIME does not change on the store procedure as expected
SQL> drop table x;

Table dropped.

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:57:14

— 7) Execute the store procedure but will fail to execute as the table does not exists so check the LAST_DDL_TIME to verify it was recompiled
SQL> exec x_proc;
BEGIN x_proc; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.X_PROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:59:25

— 8) Try to execute the store procedure and this time it will not recompile the store procedure as the LAST_DDL_TIME hasn't changed
SQL> exec x_proc;
BEGIN x_proc; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.X_PROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select status, last_ddl_time from user_objects where object_name = 'X_PROC';

STATUS LAST_DDL_TIME
——- ——————–
INVALID 17-DEC-2010 20:59:25