Skip to content

Data dictionary quiz question

March 3, 2011

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.

From → Oracle

  1. Mette Stephansen permalink

    1 & 2 will do OK

    • Nope – try again :)

  2. 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. Ah Ha! Wouldn’t have expected that. You should take up a job writing OCP questions :)

  4. poelger permalink

    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.

    • My condolences :)

  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. :)

Comments are closed.


Get every new post delivered to your Inbox.

Join 284 other followers

%d bloggers like this: