Using Oracle flashback querying for DDL audit

Oracle's new flashback querying is an immensely useful feature and can be used to audit the DDLs (data dictionary changes) issued on the entire database.

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)

The following two queries will be helpful in tracking

  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.


This was first published in April 2004

Dig deeper on Oracle DBA tools

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close