Oracle Monitoring – Monitor Unusable Indexes

This solution does not require triggers but requires setup and causes a performance hit if you enable it for all tables.

Auditing has been built into Oracle for many releases. There is an article here which goes into some detail. Basically, you turn it on, tune it for what level of detail you want and the output is available as a dba view or as XML.

Check the system parameter AUDIT TRAIL, it sounds like you want the fine grained auditing available from 9i onwards so you can access the SQL_BIND and SQL_TEXT.

There is an SCN number so you can flashback to the state of the data when the change was made.

Keep in mind that there is a performance hit for all this. You must have the hardware and resources available if this is a production system.

I might have found a couple of answers on my own. Posting them here in case others need to know:

• Google (or my google skills) seems to be getting increasingly less reliable.

• Searching the forums at https://forums.oracle.com/ I found this thread: https://forums.oracle.com/forums/thread.jspa?messageID=9956411, which references http://www.morganslibrary.org/reference/dba_best_practices.html. Here is the code mentioned:

  • SELECT start_time, end_time, input_type, input_type, status FROM v$rman_backup_job_details ORDER BY 1;

    and

  • SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time AND vrbjd.input_type <> 'ARCHIVELOG' ORDER BY 2,1;

• As for why the query in Enterprise Manager picked older jobs I learned a nice trick from someone at work: You can read recent queries from the view v$sqlarea. Something like select sql_text from v$sqlarea order by last_active_time desc should do the trick.