Author: Jonathan Gennick

You've just taken on responsibility for a new database. New to you, that is -- the database has actually been running for some years. What's the first thing you want to do? If you're like me, you most likely want to get a grip on just what it is that you have. What's in the database? How big is it? Who are the users and how many of them are there? The answers to all of these questions lie in the Oracle data dictionary.

Oracle Data Dictionary for the metadata:

The data dictionary is the repository for database metadata means the data describing the database. When you create a table, your description of that table is considered metadata, and Oracle stores that metadata in its data dictionary. Similarly, Oracle stores the definitions for other objects you create, such as views, PL/SQL packages, triggers, synonyms, indexes, and so forth. The database software uses this metadata to construe and execute SQL statements, and to properly manage stored data. You can use the metadata as your window into the database. Whether you're a DBA or a developer, you need a way to learn about the objects and data within your database.

Oracle Database Metadata:

Codd's fourth rule for relational database systems states that database metadata must be stored in relational tables just like any other type of data. Oracle exposes database metadata through a large collection of data dictionary views. Does this violate Codd's rule? By no means! Oracle's data dictionary views are all based on tables, but the views provide a much more user-friendly presentation of the metadata. For example, to find out the names of all of the relational tables that you own, you can issue the following query:

Oracle Data Dictionary Views:

Oracle divides data dictionary views into the three families, as indicated below:

USER View: USER_ views return information about objects owned by the currently-logged-on database user. For example, a query to USER_TABLES returns a list of all of the relational tables that you own.

ALL View: ALL_ views return information about all objects to which you have access, despite of who owns them. For example, a query to ALL_TABLES returns a list not only of all of the relational tables that you own, but also of all relational tables to which their owners have specifically granted you access (using the GRANT command).

DBA View: DBA_ views are generally accessible only to database administrators, and return information about all objects in the database, regardless of ownership or access privileges. For example, a query to DBA_TABLES will return a list of all relational tables in the database, whether or not you own them or have been granted access to them. Occasionally, database administrators will grant developers access to DBA views. Usually, unless you yourself are a DBA, you won't have access to the DBA views.

Analogs in Oracle Data Dictionary Views:

Many views have analogs in all three groups. For example, you have USER_TABLES , ALL_TABLES , and DBA_TABLES . A table is a schema object, and thus owned by a user, hence the need for USER_TABLES . Table owners can grant specific users access to their tables, hence the need for ALL_TABLES . Database administrators need to be aware of all tables in the database, hence the need for DBA_TABLES . In some cases, it doesn't make sense for a view to have an analog in all groups. There is no USER_DIRECTORIES view, for example, because directories are database objects not owned by any one user. However, you will find an ALL_DIRECTORIES view to show you the directories to which you have access, and you will find a DBA_DIRECTORIES view to show the database administrator a list of all directories defined in the database.

Mapping of Oracle Data Dictionary Views:

Oracle's data dictionary views are mapped onto underlying base tables, but the views form the primary interface to Oracle's metadata. Unless you have specific reasons to go around the views directly to the underlying base tables, you should use the views. The views return data in a much more understandable format than you'll get from querying the underlying tables. In addition, the views make up the interface that Oracle documents and supports. Using an undocumented interface, i.e. the base tables, is a risky practice.

The primary source of information on Oracle's many data dictionary views is the Oracle9i Database Reference manual. You can access that manual, and many others, from the Oracle Technology Network (OTN). You have to register with OTN in order to view Oracle's documentation online, but registration is free. If you prefer a hardcopy reference, Oracle In A Nutshell, published by O'Reilly & Associates, is another source of Oracle data dictionary information.

Self-documentation of Oracle Data Dictionary:

In addition to the sources of information just cited, Oracle's data dictionary is itself self-documenting. When working with a properly created Oracle database, you can query the DICTIONARY and DICT_COLUMNS views for descriptions of the data dictionary views and their columns, a sort of meta-metadata. I refer to these two views as the master key to Oracle's data dictionary.

Oracle Data Dictionary: Query the DICTIONARY view

The following example shows how you can query the DICTIONARY view from SQL*Plus, Oracle's standard command-line client, to get descriptions of all of the data dictionary views:

SQL> COLUMN table_name FORMAT A30
SQL> COLUMN comments FORMAT A45 WORD_WRAP
SQL> SELECT table_name, comments
2 FROM dictionary
3 ORDER BY table_name;

TABLE_NAME                                                         COMMENTS
-----------------                                                   -------------------------------------------------------------------------------------------------------------------------------
ALL_ALL_TABLES                                         Description of all object and relational tables accessible to the user  
ALL_APPLY                                                     Details about each apply process that dequeues from the queue visible to the current user
ALL_APPLY_CONFLICT_COLUMNS         Details about conflict resolution on tables   visible to the current user

Oracle Data Dictionary: Unqualified query the DICTIONARY view

An unqualified query against DICTIONARY will return a large amount of output. By using a WHERE clause, you can focus on a smaller set of views. The following query returns all views containing the word TABLE :

Be aware that views aren't always named the way you might think. The view describing a table's columns is DBA_TAB_COLUMNS ; the word TABLE has been abbreviated to TAB .

Oracle Data Dictionary: Retrieving descriptions

Once you've isolated a view of interest, you can query the DICT_COLUMNS view for a description of the data returned by the columns that make up the view. The following example retrieves descriptions for the columns in ALL_TAB_COLUMNS :

SQL> COLUMN comments FORMAT A45 WORD_WRAP
SQL> SELECT column_name, comments  
2 FROM dict_columns
3 WHERE table_name = 'ALL_TAB_COLUMNS';  

        COLUMN_NAME                                 COMMENTS
        ----------------------------                 -----------------------------------------------------------------------------------------
        OWNER
        TABLE_NAME                              Table, view or cluster name
        COLUMN_NAME                         Column name
        DATA_TYPE                                  Datatype of the column
        DATA_TYPE_MOD                       Datatype modifier of the column
        DATA_TYPE_OWNER                 Owner of the datatype of the column
        DATA_LENGTH                             Length of the column in bytes
        DATA_PRECISION                       Length: decimal digits (NUMBER) or binary digits (FLOAT)

Oracle Data Dictionary: Interrelated Views

Data dictionary views are often interrelated, and these relationships are generally quite evident from the column names. Look at ALL_TABLES and ALL_TAB_COLUMNS , and you'll see that you can join those two views on OWNER and TABLE_NAME . Thus, once you've used ALL_TABLE to identify a table of interest, you can use the corresponding TABLE_NAME and OWNER values from ALL_TABLE to query ALL_TAB_COLUMNS in order to retrieve the column definitions for the table.

Final Thoughts:

Sometimes it takes a bit of experimenting and research to be certain you have correctly identified the relationship between two views.

You can learn a lot about Oracle from the data dictionary views. They are also your primary source of information about objects you create in the database. If you do a lot of work with Oracle, it pays to become familiar with these views because, while GUI tools to retrieve and display metadata are in fact available, they are not universally available at all

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Useful Tips for Designing Oracle Data warehouse
   Great Tips on Oracle Applications Security!
   Speed Up Oracle Data Loading!!
   The power of Oracle10g Remote Stored Functions!!