how to check database history? 1

[huchen]

I have a view in oracle database scott.vw_myView

There are many users in the database,

How can I check if anybody called this view?

Thank you very much.

 

[huchen]

Acutually my problem is: this view scott.vw_myView is invalid, I want to know who called this view while it is invalid so that I know this view is used anywhere.
Thank you.

 

[SantaMufasa]

Unfortunately, Huchen, monitoring/auditing SELECT access of objects in Oracle is not at all in Oracle's strong suit. (Oracle argues that there are performance reasons for not, for example, being able to build triggers based upon SELECT behaviours, but I believe they are weak arguments.)

You can invoke Oracle's AUDIT feature where you can specify that you want the AUDIT report to show Oracle user names that access your VIEW via their SELECT privilege. Once you discover the information that you require, be sure to turn of the AUDITING to reduce the impacts upon performance

So, read up on Oracle AUDIT command.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[DataSheet]

1. Add AUDIT_TRAIL = DB in init.ora file
2. issue command AUDIT SELECT ON scott.vw_myView BY SESSION WHENEVER SUCCESSFUL
3. SELECT * FROM DBA_AUDIT_TRAIL

you can find who accessed scott.vw_myView.

Data Sheet!

 

[huchen]

Thank you DataSheet. I will try it. This is very helpful for monitoring other objects been used too.