Donald K. Burleson Oracle Tips

Auditing Oracle DDL with the data dictionary

When monitoring activity within an Oracle database, the Oracle administrator needs to know those times when a table or index was created.  Oracle has a new column called CREATED with the DBA_OBJECTS view that can be used to display the data that an object was created.

This is a critical report in a production environment when an audit of DDL must be kept.

Here is a script to produce a great management report showing all tables and indexes that were created within the past 14 days:

alter session set nls_date_format='YY-MON-DD HH24';

set pages 999

column c1 heading 'Date/time|created'

column c2 heading 'Object|Type'        format a20

column c3 heading 'Object|Name'        format a40

select

   created        c1,

   object_type    c2,

   object_name    c3

from

  dba_objects

where

   created > sysdate-14

order by

   created desc;

This elegant report gives complete statistics for all recent create DDL statements.  Here is a sample of the report.

Date/time    Object               Object

created      Type                 Name

------------ -------------------- -------------------------------------

02-FEB-27 07 TABLE                ORACHECK_FS_TEMP

02-FEB-26 10 TABLE                GL_TRANSLATION_INTERIM

02-FEB-26 10 INDEX                GL_TRANSLATION_INTERIM_N1

02-FEB-26 10 SYNONYM              GL_TRANSLATION_INTERIM

02-FEB-26 10 INDEX                GL_POSTING_INTERIM_N1

02-FEB-26 10 SYNONYM              GL_POSTING_INTERIM

02-FEB-26 10 INDEX                GL_POSTING_INTERIM_N2

02-FEB-26 10 TABLE                GL_POSTING_INTERIM

02-FEB-25 08 PROCEDURE            MC_LOAD_RESOURCE

02-FEB-20 15 VIEW                 MC_MPS_BREAKUP

02-FEB-15 13 DATABASE LINK        TEST2.WORLD

02-FEB-15 12 DATABASE LINK        TEST.WORLD

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm