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.
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.
Prefix |
Scope |
---|---|
USER |
User's view (objects in the user's schema). |
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.
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. |
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.
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.
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
|
TABLE_CONSTRAINTS |
USER_CONSTRAINTS |
TABLE_PRIVILEGES |
USER_COL_PRIVS |
USAGE_PRIVILEGES |
USER_COL_PRIVS |
VIEWS |
USER_VIEWS
|
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 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.
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 |