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;