1

I am an Oracle DBA. Application owner is asking me when was the last time Oracle database was accessed? How do I find information about the DB usage timeline? I mean, I want to check whether it is being used by the application or not?

Unfortunately the application owner has no idea whether her application is used by the users or not.

From DBA's side which data dictionary should I look into? Can ASH help? if yes, then which specific section should I focus on?

2
  • What version of Oracle do you have? Jan 24 '15 at 22:07
  • Answer to this question may be very different by this kind of question : are all of the application objects isolated into a separate DB schema, or are they placed all over the database ?
    – tvCa
    Jan 25 '15 at 13:20
2

Take a look at it this thread on AskTom:

Auditing of course would be the easiest way

You could possibly get something from the DBA_HIST views (part of AWR/ASH - workload repository and active session history) but bear in mind

  • they are sampled snapshots - not 100% of everything is in there
  • many of them are "top" reports only - dba_hist_seg_stat for example - shows IO's against segments - it seems perfect, but it is only "This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT" - it will not have 100% of everything.

Auditing is the right answer, funny thing with auditing - we only really want it when it isn't on don't we :)

Oracle has a lot of history tables as you can see here: http://www.dba-oracle.com/oracle10g_tuning/t_dba_hist_data_dictionary_views.htm

1
  • Note that you need Diagnostic Pack to even access most of those views. Jan 24 '15 at 22:05
1

If you don't have any database internal mechanisms like auditing in place, you could also look at the listener log.

You can even search it via SQL if you use it as external table – Have a look at The Oak Table or this blog entry

0

What do you know about the application? Sometimes the easiest way is to grep listener.log. There you can find hostaname/IP of the application and also name of the application(if it does not use JDBC).

0

If you have access to the console running v$session should work.

0

You can also use this view for logons/logoffs, it is on by default in 11g:

select * from dba_audit_session

Auditing is automatically turned on for logons and logoffs. You can query this DBA view and get when users logged on or off. This would tell you if somebody accessed the database.

Query:

select 
    os_username,
        username,
        userhost,
        terminal,
        timestamp,
        action_name,
        logoff_time
from dba_audit_session
2
  • 1
    This is a rather empty answer. Could you add more to it to make it more helpful?
    – DForck42
    Jan 26 '15 at 22:33
  • Sure, auditing is automatically turned on for logons and logoffs. You can query this DBA view and get when users logged on or off. This would tell you if somebody access the database which is what the original poster wanted. Query: select os_username,username,userhost,terminal,timestamp,action_name,logoff_time from dba_audit_session; Jan 28 '15 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.