This discussion is archived
3 Replies Latest reply: Mar 21, 2013 7:03 AM by AnkitV RSS

How to determine which user uses which database-object

vivalavida Journeyer
Currently Being Moderated
Hi,
currently we are in the process of consolidating our databases. One of the neccessary steps is to figure out which user connects to the database at all. That's easy, we implemented a logon-trigger and log the collected information into a separate table.
If a user with objects (e.g. tables, views, procedures) exists - but this user never connects to the database - does that automatically mean that these objects are not used at all? No need to say that this is not true. But how can we figure out if a connected user has selected an object of this user?
Our porblem is that we have alot of schemas in our database - but the developers don't know if this schema is not used by an application or not (sad but true).
To enable auditing would be one choice to figure out if an object was ever used or not.
Are there any other possibilities?
Any help will be appricated

Rgds
Jan
  • 1. Re: How to determine which user uses which database-object
    Pavan DBA Expert
    Currently Being Moderated
    this might help you
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4977262687416
  • 2. Re: How to determine which user uses which database-object
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    VivaLaVida wrote:
    Hi,
    currently we are in the process of consolidating our databases. One of the neccessary steps is to figure out which user connects to the database at all. That's easy, we implemented a logon-trigger and log the collected information into a separate table.
    It could have been even easier by turning on the built-in audit feature.
    If a user with objects (e.g. tables, views, procedures) exists - but this user never connects to the database - does that automatically mean that these objects are not used at all? No need to say that this is not true. But how can we figure out if a connected user has selected an object of this user?
    audit would be a good choice.
    Our porblem is that we have alot of schemas in our database - but the developers don't know if this schema is not used by an application or not (sad but true).
    Not sure what developer will do with used/not used application.
    To enable auditing would be one choice to figure out if an object was ever used or not.
    What's wrong in auditing ?
    Even though audit may have a footprint on performance, that would probably be less costly than any custom solution.

    Nicolas.
  • 3. Re: How to determine which user uses which database-object
    AnkitV Newbie
    Currently Being Moderated
    Hi,
    If a user with objects (e.g. tables, views, procedures) exists - but this user never connects to the database - does that automatically mean that these objects are not used at all? No need to say that this is not true. 
    You need to give privilege on the given objects to another user(s) and can create synonyms in those user(s) schema.
    But how can we figure out if a connected user has selected an object of this user?
    Use Audit feature of Oracle
    To enable auditing would be one choice to figure out if an object was ever used or not.
    Are there any other possibilities?
    Auditing is the only way. Better one is FGA (Fine Grained Auditing), which limits audit data.

    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points