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.