Skip to Main Content
  • Questions

  • Viewing processes and sessions without being allowed DBA privileges

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Reuben.

Asked: June 16, 2018 - 12:46 pm UTC

Last updated: June 18, 2018 - 8:59 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Dear AskTOM team,

This question may have been asked previously but I have not been able to find anything either on AskTOM or using Google

My challenge is as follows:

We have a client who maintains the Oracle DB. We do not have any DBA type privileges granted and the client will NOT allow such

The challenge is without logging a ticket with the client's DBA's we cannot find out whether there are long running or blocking processes / jobs / sessions causing other such processes / jobs to be delayed or blocked due to locks

An example of such is a materialized view which normally takes under 30 seconds to run on the hour takes over 30 minutes to run on intermittent occasions

We have requested view / select access to DBA_JOBS_RUNNING, V$SESSION, DBA_JOBS and have been told that we cannot get such

Is there any way as a "regular" user to be able to query / view and also see any locks which would be affecting running processes / jobs / sessions without having some DBA rights / privileges

Thanks in advance for any advice
Reuben

and Connor said...

Your client sounds like they're seeing things too much in black and white.

Yes, they are correct that you should not have DBA access if you are not the DBA's.

No, they are wrong to say that means no access to DBA or V$ views whatsoever.

They could easily create some views on top of the standard ones that show you (for example) just metrics and perhaps the SQL_ID, and maybe the first 20 chars of the text etc....Lots of options here to give you the information you need whilst still protecting the security of the database. They are just being either lazy or have fallen back on "no" when really it means "we don't know how to do it"

I can't really help you here (besides you showing them this answer).

As a friend of mine says: "90% of technical problems are political"

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reuben Lifschitz, June 18, 2018 - 7:31 am UTC

Thanks Connor for the response.

It's pretty much what I expected.

I am going to try taking up with them the possibility of us getting either of SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY to be able to view what we need to

Thanks again

Connor McDonald
June 18, 2018 - 8:59 am UTC

Both of those are perhaps higher privs than really needed - but if you can get them fine.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database