Re: auditing code changes ?

On Thu, Sep 25, 2008 at 5:48 AM, Freeman, Donald <dofreeman_at_state.pa.us>wrote:

> I believe you can at least check dba_objects and see the
> last_compile_time. Won't tell you who did it though.
>
>

Relying on LAST_DDL_TIME can be kind of tricky, as it can be indirectly altered by a user other than the one that made the change.

Consider that a view is created on a table.

The table is then modified by adding a column.

The view is now invalidated, and the next time a user queries it the view will be recompiled.

As the following example shows, the LAST_DDL_TIME of the view XVT is updated when the user queries it.

10:24:52 SQL> create table xt ( c1 number); Table created.

10:24:52 SQL> create or replace view xtv as select * from xt; View created.

10:24:52 SQL> select object_name, last_ddl_time
10:24:52   2  from user_objects
10:24:52   3  where object_name = 'XTV'
10:24:52   4  /

                               LAST DDL
OBJECT NAME                    TIME
------------------------------ -------------------
XTV                            10/02/2008 10:24:52

1 row selected.

10:24:52 SQL> exec dbms_lock.sleep(10)

PL/SQL procedure successfully completed.

10:25:02 SQL> select object_name, last_ddl_time
10:25:02   2  from user_objects
10:25:02   3  where object_name = 'XTV'
10:25:02   4  /

                               LAST DDL
OBJECT NAME                    TIME
------------------------------ -------------------
XTV                            10/02/2008 10:24:52

1 row selected.

10:25:02 SQL> alter table xt add( c2 number);

Table altered.

10:25:02 SQL> select c1 from xtv;

no rows selected

10:25:02 SQL> select object_name, last_ddl_time
10:25:02   2  from user_objects
10:25:02   3  where object_name = 'XTV'
10:25:02   4  /

                               LAST DDL
OBJECT NAME                    TIME
------------------------------ -------------------
XTV                            10/02/2008 10:25:02

1 row selected.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l