a) auditing, you want to enable auditing.

No, once they overwrite the code -- the old code is gone, wiped out, not there anymore.


Interesting idea I just had tho -- you could use a DDL trigger (before create) to maybe capture the code and do the audit as well?

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table old_code
2 as
3 select user username, 0 version, sysdate date_changed, user_source.*
4 from user_source
5 where 1=0
6 /

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence version_seq;

Sequence created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger create_trigger
2 before create on schema
3 declare
4 l_date date := sysdate;
5 l_ver number;
6 begin
7 if (ora_dict_obj_type in ( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION' ) )
8 then
9 select version_seq.nextval into l_ver from dual;
10
11 insert into old_code
12 select user, l_ver, l_date, user_source.*
13 from user_source
14 where name = ora_dict_obj_name
15 and type = ora_dict_obj_type;
16 end if;
17 end;
18 /

Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function f return number
2 as
3 begin
4 return 0;
5 end;
6 /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from old_code;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function f return date
2 as
3 begin
4 return sysdate;
5 end;
6 /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from old_code;

USERNAME VERSION DATE_CHAN NAME TYPE LINE TEXT
---------- ------- --------- ---- -------- ---- -------------------------
OPS$TKYTE 2 17-OCT-02 F FUNCTION 1 function f return number
OPS$TKYTE 2 17-OCT-02 F FUNCTION 2 as
OPS$TKYTE 2 17-OCT-02 F FUNCTION 3 begin
OPS$TKYTE 2 17-OCT-02 F FUNCTION 4 return 0;
OPS$TKYTE 2 17-OCT-02 F FUNCTION 5 end;

ops$tkyte@ORA817DEV.US.ORACLE.COM>





b) DBA_DEPENDENCIES has that information.