5.2 Dictionary Views: An Overview

[ Team LiB ]

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;

[ Team LiB ]