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.

9 thoughts on “Data dictionary quiz question

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s