to Find the time a SP was compiled

[uparna]

Hi,
Which sys table/view contains the information on when an SP was compiled? (Question-1)

Last_DDL_Time in All_Objects seems to be the answer, but even on compiling my SP , the time didnt appear to change in this table -- Is this table updated only on every analyze, right? (Question 2)

If not , i am not looking at the right place to find the time when the SP was compiled.

Would anyone who knows the answer point me to the right column in the right table/view?

Thanks!

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Uparna,

Here is a session that illustrates that you can use the USER_OBJECTS view to see what you want. You can also see that a secon "CREATE OR REPLACE PROCEDURE..." command actually leaves the original CREATED time, but updates the LAST_DDL_TIME:

SQL> create or replace procedure xyz is begin null; end;
  2  /

Procedure created.

SQL> col a heading "Procedure|Name" format a9
SQL> col b heading "Object|Type" format a9
SQL> col c heading "Create Time" format a19
SQL> col d heading "Last-modify Time" format a19
SQL> select object_name a
  2        ,object_type b
  3        ,to_char(created,'yyyy-mm-dd hh24:mi:ss') c
  4        ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') d
  5  from user_objects
  6  where object_name = 'XYZ'
  7  /

Procedure Object
Name      Type      Create Time         Last-modify Time
--------- --------- ------------------- -------------------
XYZ       PROCEDURE 2006-01-13 14:48:19 2006-01-13 14:48:19

SQL> create or replace procedure xyz is begin null; end;
  2  /

Procedure created.

SQL> select object_name a
  2        ,object_type b
  3        ,to_char(created,'yyyy-mm-dd hh24:mi:ss') c
  4        ,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') d
  5  from user_objects
  6  where object_name = 'XYZ'
  7  /

Procedure Object
Name      Type      Create Time         Last-modify Time
--------- --------- ------------------- -------------------
XYZ       PROCEDURE 2006-01-13 14:48:19 2006-01-13 14:48:20

Let us know if this provides the information you wanted to see.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[uparna]

Dave ,
yes, this is what i suspected----and you have answered my Q1.

i am very interested in the answer to question 2 -- why did the sp compile time not change in the all_objects view , though i knew explicitly that it had changed? Your test above seems to prove that "analyze" is not the problem...is this a bugaboo or is there an explanation for it?.....

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Notice, Jayaram, that the date/time columns are "CREATED" and "LAST_DDL_TIME"...neither is "COMPILE_TIME". The closer of the two to "COMPILE_TIME" is "LAST_DDL_TIME", and that, in fact, does change to reflect the most recent compile time. If you want "CREATED" to reflect the most recent compile time, then you must:

DROP PROCEDURE <proc_name>;
CREATE PROCEDURE <proc_name>;

...instead of:

CREATE OR REPLACE PROCEDURE <proc_name>;

Let us know if this explains Oracle's otherwise esoteric behaviour.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[uparna]

Dave ,
Yes , i understand that -- and i am concentrating on the last_ddl_time only -- not the created time. Strangely, the prod db was analyzed over the weekend , and i now see the proper compile times there....so the analyze seems to have woken up the all_objects view from its slumber. I cant doi much in the prod db except query and smile...so this cant be tested by me further there.

in the dev dbs though , i compiled a couple of SPs and the last_ddl_time changed instantaneously. curiouser and curiouser....


Regards,
S. Jayaram Uparna .