Oracle – Data Dictionary Views – USER vs ALL vs DBA Views – 100 Level Basics

Most of us use data dictionary views on an everyday basis to find things like:

  • In which schema does a table with a certain name exist?
  • What tables use a certain column name?
  • Get the line of code from any and all packages where a string named “EMPLOYEE_NUMBER” is used
  • What privileges does a role have?
  • …and even “What are all the data dictionary views?”

Data dictionary views come in three forms

  • USER_ [View] – Eg USER_TABLES
  • ALL_[View] – Eg ALL_TABLES
  • DBA_[View] – Eg DBA_TABLES

Although they serve the same purpose – to list information about tables (in the case of [*]_TABLES), they are not intended for the same audience and certainly MAY NOT return the same information!

USER* Views

USER_*: Views that start with USER_ list only the objects owned by the currently logged in user

ALL* Views

ALL_*: Views that start with ALL_ list only the objects the currently logged in user has permissions to access

DBA* Views

DBA_*: Views that start with DBA_ list all objects unless restricted by the WHERE clause

While the USER_* and ALL_* views are always accessible, DBA_* views may or may not be, based on the privileges granted to specific users. Now that you know the difference between the different dictionary view types, you can use them smartly.

Try the DBA views first. Fall back on corresponding ALL views next if you don’t have access. Finally use the USER views if the other two options are unavailable.

Examples:

To find the list of tables in current schema that have the column “EMPLOYEE_NUMBER”

SELECT *
FROM user_tab_columns
WHERE column_name = 'EMPLOYEE_NUMBER'

To find the list of tables in schema accessible to the current user that have the column “EMPLOYEE_NUMBER”

SELECT *
FROM all_tab_columns
WHERE column_name = 'EMPLOYEE_NUMBER'

To find the list of tables across schemas that have the column “EMPLOYEE_NUMBER”

SELECT *
FROM dba_tab_columns
WHERE column_name = 'EMPLOYEE_NUMBER'

To learn more about data dictionary views (which are vast in Oracle), please take a look at Oracle documentation here (requires registration). Information on a subset of the commonly used views can be found here.

DICT (short for DICTIONARY) – View of All Views – THE MOTHER

If you take away one thing from this post, it is the DICT view. You can find information about any other view (or all other views) using this view.

SELECT * FROM DICT
TABLE_NAME COMMENTS
DBA_TAB_COLS Columns of user’s tables, views and clusters
DBA_OBJECT_TABLES Description of all object tables in the database
DBA_ALL_TABLES Description of all object and relational tables in the database
DBA_TABLES Description of all relational tables in the database
DBA_TAB_COLUMNS Columns of user’s tables, views and clusters
DBA_TAB_COMMENTS Comments on all tables and views in the database
DBA_TAB_PRIVS All grants on objects in the database
DBA_VIEWS Description of all views in the database
DBA_VIEWS_AE Description of all views in the database
DBA_CONSTRAINTS Constraint definitions on all tables
DBA_LOG_GROUPS Log group definitions on all tables
DBA_CLUSTER_HASH_EXPRESSIONS Hash functions for all clusters
DBA_UPDATABLE_COLUMNS Description of dba updatable columns
DBA_UNUSED_COL_TABS All tables with unused columns in the database
DBA_PARTIAL_DROP_TABS All tables with partially dropped columns in the database
….more rows

Finding your view using DICT

Let us say your want to find information about “constraints”. You could run a query like below (note TABLE_NAME has all upper case data while COMMENTS has mixed case data)

SELECT * FROM DICT
WHERE TABLE_NAME LIKE '%CONSTRAINT%'
OR UPPER(COMMENTS) LIKE '%CONSTRAINT%'

The results are:

TABLE_NAME COMMENTS
DBA_CONSTRAINTS Constraint definitions on all tables
DBA_LOGSTDBY_NOT_UNIQUE List of all the tables with out primary or unique key not null constraints
DBA_CONS_COLUMNS Information about accessible columns in constraint definitions
DBA_IAS_CONSTRAINT_EXP
USER_CONSTRAINTS Constraint definitions on user’s own tables
USER_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
DBA_APPLY_OBJECT_DEPENDENCIES Synonym for _DBA_APPLY_OBJECT_CONSTRAINTS
DBA_APPLY_VALUE_DEPENDENCIES Synonym for _DBA_APPLY_CONSTRAINT_COLUMNS

Practical Use: Searching PL/SQL code

A practical example of using a dictionary view is to search through all the source code (that is not obfuscated). Let us say you follow your company’s best practices and marked your code with comments like “Ticket#: 563231” to say that you made changes as part of that ticket number. You could search for all the changes for that ticket using this query:

SELECT *
FROM DBA_SOURCE
WHERE UPPER(TEXT) LIKE '%TICKET%'
    AND UPPER(TEXT) LIKE '%563231%'

If you don’t have access:

At our shop, as a DBA, I don’t have any reservations in granting privileges to dictionary views for developers as it helps them become better developers. Your shop and DBA may be different. It still does not hurt to ask your DBA for access – “SELECT ANY DICTIONARY” privilege and/or the “SELECT_CATALOG_ROLE”

Conclusion:

As an Oracle person, you ought to know these basics. We did not even talk about the v$ views which I will save for a future post.

Advertisements

2 thoughts on “Oracle – Data Dictionary Views – USER vs ALL vs DBA Views – 100 Level Basics

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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