Using Oracle flashback querying for DDL audit

Oracle's new flashback querying is an immensely useful feature. Many of us now use it for getting a view of past data, or even for recovery. Here, I am trying to extend this facility to auditing the DDLs (data dictionary changes) issued on the entire database. We cannot use dictionary views like DBA_TAB_COLUMNS straight away for flashback querying, as those operations are incredibly costly and error-prone. Here I have devised a selective approach to make it possible. The prime advantage of this facility is that you can do this auditing without the overhead of imposing any additional objects or triggers on the database.

However, for this to work properly,

1. You *must* be using Oracle 9i (Release 9.2+).
2. It is recommended that automatic undo management is enabled.
3. UNDO tablespace /ROLLBACK segs should be sufficiently large.
4. UNDO_RETENTION (time in seconds for which the undo information -- minimum of 7200 in this example, for 2 hours -- has to be retained) has to be set to be a sufficiently high value in the PFILE/SPFILE.
5. Database has to have been up and running during the interval specified.
6. You *must* log in as SYS. (otherwise some other user has to be granted privileges to select from all the component tables in these queries explicitly)

1. Information about columns that had been added / dropped in the entire database during a specified period (past 2 hours in this example)
2. List of all major objects added or dropped in the entire database during a specified period (past 2 hours in this example)

1:
-------
select obj.name ,col.name colname,col.oper oper ,usr.name Schema
 from
 sys.obj$ obj
 inner join
 (
  ( select 'Added' oper,name,obj# from sys.col$
   minus
   select  'Added'oper, name,obj# from sys.col$
   as of timestamp(systimestamp-interval'2'hour)
  )
   union all
  ( select 'Dropped'oper,name,obj# from sys.col$
   as of timestamp(systimestamp-interval'2'hour)
   minus
   select 'Dropped'oper,name,obj# from sys.col$
  )
 ) col
 on col.obj#=obj.obj#
 inner join
 sys.user$ usr
 on
 usr.user#=obj.owner#;


2:
-------
select obj.name  ,obj.oper  ,obj.type ,usr.name username
from
(
  (select owner#,name ,'Added' oper,decode(type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER') Type from sys.obj$
   minus
   select  owner#,name ,'Added' oper, decode(type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 

'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER') Type from sys.obj$
   as of timestamp(systimestamp-interval'2'hour)
  )
   union all
  ( select owner#,name ,'Dropped' oper,decode(type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 

'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER') Type from sys.obj$
   as of timestamp(systimestamp-interval'2'hour)
   minus
   select owner#,name ,'Dropped' oper,decode(type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 

'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER') Type  from sys.obj$
  ) 
) obj
inner join
sys.user$ usr
on 
usr.user#=obj.owner#  
where obj.type is not null;

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.