Data dictionary quiz question

This is a totally unfair quiz question (for anyone who isn’t intimately acquainted with the Oracle data dictionary views). There, I’ve warned you. I would have got this one wrong, myself.

Which of the following queries (if any) will run without error?

SELECT * FROM dba_tab_privs WHERE owner = 'SCOTT';
SELECT * FROM all_tab_privs WHERE owner = 'SCOTT';
SELECT * FROM user_tab_privs WHERE owner = 'SCOTT';

Now, don’t go and try running these in your database until after you’ve written down what you think the answers are. That would be cheating πŸ™‚





Ok, now to break the suspense for all the other readers (both of you), who couldn’t be bothered testing it out for yourself.

Statement 1. The query on DBA_TAB_PRIVS will succeed – assuming you have the necessary privileges on the DBA* views. This view does include the column OWNER.

Statement 3. The query on USER_TAB_PRIVS, unlike what I and several others might assume, Β will succeed. Β Many data dictionary views, such as USER_TABLES, omit the OWNER column – which makes sense, since it is expected that it will be simply the currently-logged-in user. But for user_tab_privs, this column is provided, for good reason – because the table you have a privilege on may very well be owned by another schema.

Statement 2. The query on ALL_TAB_PRIVS, in order to be consistent with DBA_TAB_PRIVS and USER_TAB_PRIVS, should have the OWNER column, by rights. But, just to make things interesting, the column is called TABLE_SCHEMA instead. So, my query would fail.

APEX Tip: How to fix the Date Picker icon position
Handling unique constraint violations by Hibernate


  1. Mette Stephansen
    3 March 2011 - 2:47 pm

    1 & 2 will do OK

  2. Niall Litchfield
    3 March 2011 - 4:38 pm

    I’m pretty certain that the column isn’t OWNER in those views. probably is TABLE_OWNER.. I’ll have to go look now.

  3. Niall Litchfield
    3 March 2011 - 4:43 pm

    Ah Ha! Wouldn’t have expected that. You should take up a job writing OCP questions πŸ™‚

  4. Didn’t know it and tried out the statements.

  5. I was totally wrong. Had to do a DESC to find out why. Thanks for reminding me that I don’t know Oracle as well as I think I do.

  6. Didn’t get that. Its about time they actually deprecated some of of those views and moved to an INFORMATION_SCHEMA model and made it all a bit more consistent.

    Oh, and your assumptions should specify that the user has select access to DBA_TAB_PRIVS πŸ™‚

    • πŸ™‚ well, this ain’t the PL/SQL Challenge, so I get to make up the rules: Rule #1: assume nothing. Rule #2: Assume. Absolutely. Nothing. πŸ™‚

Leave a Reply

Your email address will not be published / Required fields are marked *