Oracle 9i Data Dictionary

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.

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.

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.

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.

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.

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 27-OCT-02 PHONE 27-OCT-02
		  PRODUCT 27-OCT-02 RESELLER 27-OCT-02 SALESMAN 27-OCT-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.

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