This content has been marked as final. Show 1 reply
please try this. It should clarify your first question. the date will change please believe me. If my example is wrong please give me feedback.
CREATE OR REPLACE PACKAGE PACKAGE1 AS PROCEDURE prc_test ; END PACKAGE1; / CREATE OR REPLACE PACKAGE body PACKAGE1 AS PROCEDURE prc_test as BEGIN NULL; END; END; / drop table obj_ddl_test; create table obj_ddl_test as select 1 as run_id, to_char(o.last_ddl_time,'YYYYMMDD HH24:MI:SS') last_ddl_time, o.object_name, o.object_type from user_objects o where o.object_name = 'PACKAGE1'; begin dbms_lock.sleep(10); dbms_utility.compile_schema('OTN'); end; / insert into obj_ddl_test select 2 as run_id, to_char(o.last_ddl_time,'YYYYMMDD HH24:MI:SS') last_ddl_time, o.object_name, o.object_type from user_objects o where o.object_name = 'PACKAGE1'; select * from obj_ddl_test where object_name = 'PACKAGE1' order by 1,2,3,4;
1. COMPILE_SCHEMA changes the LAST_DDL_TIME (see above)
2. If "decompiled" means invalid, then take a look at the data-dictionary-views concerning dependencies.
Very short example:
Along these dependencies objects will get invalidated.
select usr.username username, usr.user_id, usr.created usr_creation, deps.* from user_dependencies deps , all_users usr where usr.username = 'OTN' and deps.schemaid = usr.user_id and deps.referenced_owner ='OTN' ;
Edited by: stratmo on Nov 30, 2012 10:02 AM