Non DBA user identifying locks
I have an application where users with only Connect & Resource roles lock elements using 'select for update.' Is it possible for these users to see any info (as a debug tool for our development) on what rows are locked in the schema without being DBA or having privs added? All the queries I can find on locks and sessions require more than Connect & Resource.
doubt it, even as a DBA it's not trivial to know exactly which rows are locked, I do NOT recommend anyways querying dynamic views in an application, they are special and they are for special people. The problem with dynamic views are:
1. They are not consistent, i.e the data they returns might no be the data you are looking for
2. They are dangerous, querying views such as v$sql may hangs an instance. You may say you wont use it but the developers are like this, if they are allowed to use some v$ then they think they can use more and asks for more and more
3. They may change with versions
I'm wondering why a plain user might be interested in knowing what rows are locked. Locking is what makes rdbms possible, they shouldn't be concerned about it.
Going back to your original question Is it possible for these users to see any info (as a debug tool for our development) on what rows are locked... the answer is: No.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width