22-12-2012, 03:11 PM
Data Dictionary Structure
Data Dictionary.ppt (Size: 260.5 KB / Downloads: 22)
Underlying base tables store information about the associated database. Only the Oracle server should write to and read these tables. You rarely access them directly.
There are several views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information (such as user or table names) using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.
The Oracle user SYS owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity.
How to Use the Dictionary Views
To familiarize yourself with the dictionary views, you can use the dictionary view named DICTIONARY. It contains the name and short description of each dictionary view to which you have access.
You can write queries to search for information on a particular view name, or you can search the COMMENTS column for a word or phrase. In the example shown, the DICTIONARY view is described. It has two columns. The SELECT statement retrieves information about the dictionary view named USER_OBJECTS. The USER_OBJECTS view contains information about all the objects that you own.
You can write queries to search the COMMENTS column for a word or phrase. For example, the following query returns the names of all views that you are permitted to access in which the COMMENTS column contains the word columns:
SELECT table_name
FROM dictionary
Column Information
You can query the USER_TAB_COLUMNS view to find detailed information about the columns in your tables. While the USER_TABLES view provides information on your table names and storage, detailed column information is found in the USER_TAB_COLUMNS view.
This view contains information such as:
Column names
Column data types
Length of data types
Precision and scale for NUMBER columns
Whether nulls are allowed (Is there a NOT NULL constraint on the column?)
Default value
Note: For a complete listing and description of the columns in the USER_TAB_COLUMNS view, see “USER_TAB_COLUMNS” in the Oracle Database Reference.
Views in the Data Dictionary
After your view is created, you can query the data dictionary view called USER_VIEWS to see the name of the view and the view definition. The text of the SELECT statement that constitutes your view is stored in a LONG column. The LENGTH column is the number of characters in the SELECT statement. By default, when you select from a LONG column, only the first 80 characters of the column’s value are displayed. To see more than 80 characters in SQL*Plus, use the command SET LONG: