5.2. Dictionary Views: An Overview

There are some implicit relationships among the data dictionary views. Finding these relationships is often a result of experimenting with SQL queries against the data dictionary. Fortunately, there is a good deal of constancy with names used in the data dictionary.

For example, the view USER_TABLES contains a single row for each table. Each row contains detailed information about a table such as the physical storage parameters of that table. This data provides extent information telling you how the table will grow physically.

The USER_TAB_COLUMNS view contains one row for each column of each table in USER_TABLES. If a table has 10 columns, then you will find 10 rows in USER_TAB_COLUMNS, detailing information about the attributes of each column, such as the column data type. The column name TABLE_NAME is common between USER_TABLES and USER_TAB_COLUMNS so it is easy to join these views.

The information you obtain from data dictionary views is useful throughout all phases of a project. You can't possibly maintain familiarity with all data dictionary views; this is where the SQL*Plus DESCRIBE becomes most valuable. We may not be sure what columns are in a view, which makes it difficult to write a meaningful query, but we can first describe the view. This will show the column names.

If your account has the DBA role, then you can start discovering by first listing all view names with the query:

SELECT view_name FROM dba_views ORDER BY 1;

The DBA views query will return the full set of data dictionary views including the USER, ALL, and DBA views, as well as the v$ performance views (Chapter 2, Section 2.12, illustrates a sample query using the v$instance view). If you don't have the DBA role, you still have considerable access to the data dictionary. Start listing the views with:

SELECT view_name FROM all_views ORDER BY 1;

The view USER_OBJECTS is a reasonable starting point for looking into the data dictionary. There is a row in this view for everything we create. A partial description is shown next:

SQL> desc user_objects
 Name                           Null?    Type
 ------------------------------ -------- ---------------
 OBJECT_NAME                    VARCHAR2(128)
 OBJECT_TYPE                    VARCHAR2(18)
 CREATED                        DATE
 STATUS                         VARCHAR2(7)
 . . . .

We can select a full list of object names and their types from this view. The ALL and DBA OBJECTS views includes an OWNER column. This permits us to see who owns what. To see all objects to which you have access and who owns those objects, you can execute the following query—this is a lengthy output and includes all data dictionary objects at your disposal.

SELECT object_type, object_name, owner, created, status
  FROM all_objects ORDER BY 1;

The DDL in Chapter 4 creates objects that we can see with the following:

					SQL> SELECT object_type, object_name
  2  FROM user_objects ORDER BY 1 DESC;

OBJECT_TYPE        OBJECT_NAME
------------------ -----------------------
TABLE              COURSES
TABLE              PARKING_TICKETS
TABLE              PROFESSORS
TABLE              STUDENTS
TABLE              STUDENT_VEHICLES
TABLE              STUDENTS_COURSES
TABLE              STATE_LOOKUP
INDEX              PK_COURSES
INDEX              UK_STUDENTS_LICENSE
INDEX              PK_PARKING_TICKETS
INDEX              PK_PROFESSORS
INDEX              PK_STATE_LOOKUP
INDEX              PK_STUDENTS
INDEX              PK_STUDENTS_COURSES
INDEX              PK_STUDENT_VEHICLES

Conceptually, the USER_OBJECTS view is a parent to other views that contain specific information about the objects we create. Two views of particular interest are: USER_TABLES and USER_SEQUENCES.

• A row in USER_OBJECTS, for an object of type TABLE, means there is a row in USER_TABLES. The USER_TABLES row will have more specific information about that table. The following query joins these two views. It selects the column CREATED from USER_OBJECTS plus information from USER_TABLES.

  SELECT user_objects.created,
         user_tables.table_name,
         user_tables.tablespace_name
    FROM user_objects,
         user_tables
   WHERE user_objects.object_name =
         user_tables.table_name;
  

  The result from this query, for the STUDENTS schema, is shown next.

  CREATED         TABLE_NAME             TABLESPACE_NAME
  --------------- ---------------------- -------------------
  18-jul-03 17:45 COURSES                STUDENT_DATA
  18-jul-03 17:45 PARKING_TICKETS        STUDENT_DATA
  18-jul-03 17:45 PROFESSORS             STUDENT_DATA
  18-jul-03 17:45 STATE_LOOKUP           STUDENT_DATA
  18-jul-03 17:45 STUDENTS               STUDENT_DATA
  18-jul-03 17:45 STUDENTS_COURSES       STUDENT_DATA
  18-jul-03 17:45 STUDENT_VEHICLES       STUDENT_DATA
  

• A row in USER_OBJECTS, for an object of type SEQUENCE, means there is a row in USER_SEQUENCES. The following query joins these two views where the OBJECT_TYPE = SEQUENCE.

  SELECT <columns-you-choose>
    FROM user_objects,
         user_sequences
   WHERE user_objects.object_name =
         user_sequences.sequence_name;