Skip to Main Content


Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

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

You Asked

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.

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.

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