SQL Bible [Electronic resources] نسخه متنی

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.

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