Oracle 9i Data Dictionary

Oracle 9i Data Dictionary Oracle uses the term "Data Dictionary" for its system catalogs. Each Oracle database has its own set of system tables and views that store information about both the physical and logical database structure. The data dictionary objects are read-only, meaning that no database user ever manually modifies them; however, Oracle RDBMS itself automatically updates data in these objects in response to specific actions. For example, when user ACME creates a new object (table, view, stored procedure, etc.), adds a column or a constraint to a table, and so forth, the appropriate data dictionary tables are updated behind the scenes at once, and the corresponding changes are visible through the system views (discussed later in this chapter). Oracle's data dictionary consists of hundreds of different views and tables that logically belong to different categories, but most of them are only of interest to the database administrators and are beyond the scope of this book. We list only the main object groups in the information schema and briefly describe the most common objects in each category.

Oracle data dictionary structure Generally, the data dictionary consists of base tables and user-accessible views. The base tables contain all database information that is dynamically updated by Oracle RDBMS. Oracle strictly discourages using those tables even for selects; the database users normally have no access to them, and even DBAs do not typically query these tables directly. The information stored in the base tables is cryptic and difficult to understand. The user-accessible views summarize and display the information stored in the base tables; they display the information from the base tables in readable and/or simplified form using joins, column aliases, and so on. Different Oracle users can have SELECT privileges on different database views. Note All Oracle data dictionary objects belong to a special user called SYS. Oracle creates public synonyms to simplify user access to these objects (see Chapter 4). That means you do not have to, for example, refer to SYS.DBA_TABLES with the fully qualified name; simply DBA_TABLES will do, assuming you have appropriate privileges to access the view. The data dictionary views, in turn, consist of static and dynamic views. The name "static" denotes that the information in this group of views only changes when a change is made to the data dictionary (a column is added to a table, a new database user is created, etc.). The dynamic views are constantly updated while a database is in use; their contents relate primarily to performance and are not relevant to this book. Note The dynamic data dictionary views can be distinguished by the prefix V_$, and the public synonyms for these views start with V$. The static views can be divided into three groups. The views in each group are prefixed USER_, ALL_, or DBA_, as shown in Table 13-2. Table 13-2: Static View Prefixes Prefix

Scope

USER

User's view (objects in the user's schema).

Table 13-2: Static View Prefixes Prefix

Scope

ALL

Expanded user's view (all objects that the user can access).

DBA

Database administrator's view (all objects in all users' schemas).

The set of columns is almost identical across views, that is, USER_TABLES, ALL_TABLES, and DBA_TABLES have the same columns, except USER_TABLES does not have column OWNER (which is unnecessary because that view only has information about tables that belong to the user who queries the view). Table 13-3 contains information about the most commonly used static views. Table 13-3: Selected Oracle Data Dictionary views Data Dictionary View

Contains Information About:

ALL_ALL_TABLES

All object and relational tables accessible to the user.

ALL_CATALOG

All tables, views, synonyms, sequences accessible to the user.

ALL_COL_PRIVS

Grants on columns accessible by the user.

ALL_CONSTRAINTS

Constraint definitions on accessible tables.

ALL_CONS_COLUMNS

Information about columns in constraint definitions accessible by the user.

ALL_DB_LINKS

Database links accessible to the user.

ALL_INDEXES

Indexes on tables accessible to the user.

ALL_OBJECTS

All objects accessible to the user.

ALL_SEQUENCES

Database sequences accessible to the user.

ALL_SYNONYMS

All synonyms accessible to the user.

ALL_TABLES

Relational tables accessible to the user.

ALL_TAB_COLUMNS

Columns of tables, views, and clusters accessible to the user.

ALL_TRIGGERS

Triggers accessible to the current user.

ALL_USERS

Information about all users of the database visible to the current user.

ALL_VIEWS

Views accessible to the user.

DBA_ALL_TABLES

All object and relational tables in the database.

DBA_CATALOG

All database tables, views, synonyms, and sequences.

DBA_COL_PRIVS

All grants on columns in the database.

DBA_CONSTRAINTS

Constraint definitions on all tables.

DBA_CONS_COLUMNS

Information about all columns in constraint definitions in the database.

Table 13-3: Selected Oracle Data Dictionary views Data Dictionary View

Contains Information About:

DBA_DB_LINKS

All database links in the database.

DBA_INDEXES

All indexes in the database.

DBA_OBJECTS

All database objects.

DBA_SEQUENCES

All sequences in the database.

DBA_SYNONYMS

All synonyms in the database.

DBA_TABLES

All relational tables in the database.

DBA_TAB_COLUMNS

Description of columns of all tables, views, and clusters in the database.

DBA_TRIGGERS

All triggers in the database.

DBA_USERS

Information about all users of the database.

DBA_VIEWS

All views in the database.

USER_ALL_TABLES

All object and relational tables owned by the user.

USER_CATALOG

Tables, views, synonyms, and sequences owned by the user.

USER_COL_PRIVS

Grants on columns for which the user is the owner, grantor, or grantee.

USER_CONSTRAINTS

Constraint definitions on user's own tables.

USER_CONS_COLUMNS

Information about columns in constraint definitions owned by the user.

USER_DB_LINKS

Database links owned by the user.

USER_INDEXES

The user's own indexes.

USER_OBJECTS

Objects owned by the user.

USER_SEQUENCES

The user's own database sequences.

USER_SYNONYMS

The user's private synonyms.

USER_TABLES

The user's own relational tables.

USER_TAB_COLUMNS

Columns of user's tables, views, and clusters.

USER_TRIGGERS

Triggers owned by the user.

USER_USERS

Information about the current user.

USER_VIEWS

The user's own views.

The select privilege for USER_ and ALL_ views (as well as for selected V$ views) is granted to PUBLIC by default; DBA_ views are visible to privileged users only.

Oracle data dictionary and SQL99 standards

We already mentioned that Oracle is the least compliant of our three databases with SQL99 INFORMATION_SCHEMA standards. Historically, Oracle has its own naming conventions for the system catalog objects that do not match the standards. However, most of the "SQL99 standardized" information (at least regarding the objects implemented by Oracle) can be retrieved from Oracle's data dictionary. Table 13-4 shows a rough correspondence between SQL99 INFORMATION_SCHEMA views and Oracle data dictionary objects. Table 13-4: Oracle Data Dictionary Views Correspondence to SQL99 INFORMATION_SCHEMA. INFORMATION_SCHEMA View

Oracle Data Dictionary View

CHECK_CONSTRAINTS

USER_CONSTRAINTS USER_OBJECTS

COLUMNS

USER_TAB_COLUMNS

COLUMN_PRIVILEGES

USER_COL_PRIVS

CONSTRAINT_COLUMN_USAGE

USER_CONS_COLUMNS

CONSTRAINT_TABLE_USAGE

USER_CONSTRAINTS

KEY_COLUMN_USAGE

USER_CONS_COLUMNS

REFERENTIAL_CONSTRAINTS

USER_CONSTRAINTS

TABLES

USER_TABLES USER_OBJECTS

TABLE_CONSTRAINTS

USER_CONSTRAINTS

TABLE_PRIVILEGES

USER_COL_PRIVS

USAGE_PRIVILEGES

USER_COL_PRIVS

VIEWS

USER_VIEWS USER_OBJECTS

VIEW_COLUMN_USAGE

USER_TAB_COLUMNS

The following query (when issued by user ACME in the ACME sample database) retrieves the names and creation dates of all tables that belong to the current user: SELECT object_name, created FROM user_objects WHERE object_type = 'TABLE'; OBJECT_NAME CREATED -------------------------------------------------------- ADDRESS 27-OCT-02 CUSTOMER 27-OCT-02 DISCOUNT 27-OCT-02 ORDER_HEADER 27-OCT-02 ORDER_LINE 27-OCT-02 ORDER_SHIPMENT 27-OCT-02 PAYMENT_TERMS 27OCT-02 PHONE 27-OCT-02 PRODUCT 27-OCT-02 RESELLER 27-OCT-02 SALESMAN 27OCT-02 SHIPMENT 27-OCT-02 STATUS 27-OCT-02 13 rows selected.

The query results tell us that there are currently 13 tables in the ACME database that belong to user ACME and that all of them were created on October 27, 2002. The system catalog views can be joined just as any other views or tables in Oracle to produce some combined output. The query below joins USER_TABLES and USER_TAB_COLS data dictionary views to produce the list of all columns in ADDRESS table that belongs to user ACME: SELECT table_name, column_name FROM user_tables JOIN user_tab_cols USING (table_name) WHERE table_name = 'ADDRESS'; TABLE_NAME COLUMN_NAME ----------------------------------------------------------- ADDRESS ADDR_ID_N ADDRESS ADDR_CUSTID_FN ADDRESS ADDR_SALESMANID_FN ADDRESS ADDR_ADDRESS_S ADDRESS ADDR_TYPE_S ADDRESS ADDR_CITY_S ADDRESS ADDR_STATE_S ADDRESS ADDR_ZIP_S ADDRESS ADDR_COUNTRY_S 9 rows selected.

One more level deep: Data about metadata The whole idea of Oracle's data dictionary is to hold data about data that are used both internally by the RDBMS and by Oracle users. However, unlike the SQL99 INFORMATION_SCHEMA, which only contains a handful of views, the Oracle 9i data dictionary consists of over a thousand objects, with dozens of columns in each. That raises a question — where to look for certain information within the data dictionary. Fortunately, Oracle provides a few objects that contain the information about the system objects. The two main views are DICTIONARY, which contains a description of the data dictionary tables and views, and DICT_COLUMNS, which describes these objects' columns. You can use a simple SQL query to look for objects that contain the information you need. For example, if you want to know which columns in which tables you have permission to modify, a query similar to one below can help you to find out: SELECT * FROM dictionary WHERE UPPER(comments) LIKE '%UPDAT%'; TABLE_NAME COMMENTS ---------------------------------------------------------ALL_UPDATABLE_COLUMNS Description of all updatable columns USER_UPDATABLE_COLUMNS Description of updatable columns Querying either ALL_UPDATABLE_COLUMNS or USER_UPDATABLE_COLUMNS will provide you with the information you are looking for. The other view, DICT_COLUMNS, gives you information about the individual columns of the data dictionary objects. The query below displays all the columns in the USER_OBJECTS view along with comments for these columns:

SELECT * from dict_columns WHERE table_name = 'USER_OBJECTS'; TABLE_NAME COLUMN_NAME COMMENTS ---------------- -----------------------------------------USER_OBJECTS OBJECT_NAME Name of the object USER_OBJECTS SUBOBJECT_NAME Name of the sub-object (for example, partition) USER_OBJECTS OBJECT_ID Object number of the object USER_OBJECTS DATA_OBJECT_ID Object number of the segment which contains the object USER_OBJECTS OBJECT_TYPE Type of the object USER_OBJECTS CREATED Timestamp for the creation of the object USER_OBJECTS LAST_DDL_TIME Timestamp for the last DDL change (including GRANT and REVOKE) to the object USER_OBJECTS TIMESTAMP Timestamp for the specification of the object USER_OBJECTS STATUS Status of the object USER_OBJECTS TEMPORARY Can the current session only see data that it place in this object itself? USER_OBJECTS GENERATED Was the name of this object system generated? USER_OBJECTS SECONDARY Is this a secondary object created as part of icreate for domain indexes? 12 rows selected. Tip

You can use the DESCRIBE command to obtain some minimal information about the data dictionary views and tables in exactly the same way that you would use it to inquire about any other database objects, for example: DESCRIBE user_sequences Name Null? Type ---------------- -------------------- SEQUENCE_NAME NOT NULL VARCHAR2(30) MIN_VALUE NUMBER MAX_VALUE NUMBER INCREMENT_BY NOT NULL NUMBER CYCLE_FLAG VARCHAR2(1) ORDER_FLAG VARCHAR2(1) CACHE_SIZE NOT NULL NUMBER LAST_NUMBER NOT NULL NUMBER