Thanks for the question, Mike.
Asked: April 15, 2014 - 4:00 pm UTC
Answered by: Tom Kyte - Last updated: April 15, 2014 - 10:29 pm UTC
Category: Database - Version: 11.0.6
Viewed 1000+ times
I recently joined a new medium sized company(IT dept has about 200 people).
Yesterday I was performing a deployment in our qa environment, and got an error that there was a locking. I wanted to run some query to find out info about the locking but realized that the standard login(not my personal login) I used did not have read access to ANY v$ views. I asked our DBAs for help and they granted me read access. Later the DBA removed such access after I was done. When I told them that I hope to keep such privilege, our DBA director/interim CTO said:
"This is not a standard priv that is given to developers. Help me understand you need to have this type of access and I am sure that we can provide you with what is needed. In the mean time we will need to back out these privs."
What is your stance on this?
I used these views almost everywhere I worked before.
Thank you very much.
and we said...
It really depends on the mindset at the company. My personal opinion is that DBA's in a test/qa environment would be "DBA/Developers" and would have full access to all of the diagnostic information (all that OEM/ASH/AWR has to provide) and that there would be "Developer/DBAs" as well with similar access (might not be able to do everything the DBA/developer could). Meaning that not every single developer would have carte blanche access to everything - but a knowledgeable set of people - DBA and developer alike, but with a lot of cross over knowledge - would.
Is this answer out of date? If it is, please let us know via a Review
Meaning, qualified people could have access to this.
Meaning - there are not just "DBAs" and "Developers", there is a triumvirate - DBAs, DBA/Developer-Developer/DBA, and Developers (coders). And in many cases - the "DBAs" don't work too much in test/qa, nor do the developers. A product DBA in test/qa is the wrong mindset, and a coder just wouldn't have the knowledge necessary.
But that is a perfect world, one in which you have developers that actually understand and can exploit the database - and one in which you have DBAs who feel it is not their job to protect the database from the developers.
In real life - this information should be made available to those that need it - when they need it. For example in your case, if you were not granted access as a developer/dba, you should have a dedicated resource during your testing/qa process that is - so you can, you know, test and qa your product and figure out what it is doing. If the DBA team cannot provide such a resource, then that is the basis for starting the discussion about having developer/dbas who do have such access (sort of a junior DBA if you will).
As an aside, I have found that 99.999% of all locking programs in today's Application Server applications is due to improper error handling. The program is leaking cursors, or even worse - leaking connections. If an application grabs a connection, does an update, hits and error and flies over the bit of code that would have released the connection back to the pool - you get your locking issues.
The way to discover these most readily is to run in test/qa with a connection pool set to a minimum of 1 and a MAXIMUM of 1. That way, when your code leaks a connection - bam, the entire thing shuts down. And since your coders have fully instrumented their code (hahahaha - well, maybe they have....), you'll be able to find out who the last one to grab it was and fix it.
Also, having the development DBAs set open_cursors to say 50 or say - and refuse to let them increase it (unless and until you can prove that your application really has 50 open cursors SIMULTANEOUSLY - how many developers could actually open (on purpose) and remember the state of 50 cursors....). That way you'll find your cursor leaks (due to bogus error handling) as well.