5.6 The System Catalog

The system catalog or system data dictionary can be thought of as a database of information about a database. It contains, in table form, a summary of the structure of each database as it appears at a given time. Whenever a base table, view, index, constraint, stored module, or other item of a database schema is created, altered, or dropped, the DBMS automatically updates its entries in the catalog. The system also uses the catalog to check authorizations and to store information for access plans for applications. Users can query the data dictionary using ordinary SQL SELECT commands. However, because the data dictionary is maintained by the DBMS itself, the SQL UPDATE, INSERT, and DELETE commands cannot be executed in the data dictionary.

The Oracle data dictionary contains information about all schema objects, but access to it is provided through three different views, called USER, ALL, and DBA. In Oracle, each user is automatically given access to all the objects he or she creates. The USER view provides a user with information about all the objects in the schema of the current user. Users can be given access to objects that are created by others. The ALL view provides information about those objects in addition to the ones the user has created. The DBA view, which provides information about all database objects, is available to the database administrator using the SYSDBA privileges. Each of the views is invoked by using the appropriate term as a prefix for the object(s) named in the FROM clause in a query.

Queries can be written by using the appropriate prefix (USER_, ALL_, DBA_) in the FROM clause, followed by the name of one of the tables in the catalog. The tables include TABLES, VIEWS, TAB_COLUMNS (table columns), CONSTRAINTS, INDEXES, USERS, CONS_COLUMNS (columns that have constraints), DICTIONARY, IND_COLUMNS (columns that have indexes), OBJECTS, TRIGGERS, ROLES, PROFILES, SEQUENCES, SOURCE (source code for a module), SYS_PRIVS, TABLESPACES, and others. For all these tables, each of the three views (USER, ALL, DBA) has many columns.

Generally, you can assume that each catalog table has a column for the name of the object, which you can use to write a query such as

An S Q L command for modifying a view. CREATE OR REPLACE VIEW view name AS subquery.

which displays the names of all tables in the database. To see the names of all views, write

An S Q L query. Line 1. SELECT TABLE, underscore, NAME. Line 2. FROM USER, underscore, TABLES, semicolon.

The queries can include built-in functions, ORDER BY, GROUP BY, GROUP BY ... HAVING and so on because they are normal SQL queries on tables, as illustrated by the following query

An S Q L query. The query is as follows. Line 1. SELECT TABLE, underscore, NAME, comma, COUNT, open parentheses, COLUMN, underscore, NAME, close parentheses. Line 2. FROM USER, underscore, TAB, underscore, COLUMNS. Line 3. GROUP BY TABLE, underscore, NAME. Line 4. HAVING COUNT, open parentheses, COLUMN, underscore, NAME, close parentheses, less than 5, semicolon.

To learn what information is available about the objects in the catalog you can use the DESCRIBE (or DESC) command. Once you know the name of a catalog object (e.g., a table, view, column, constraint, and so on), you can ask for a description of it. For example, to see what information is available about your table columns, you can write

An S Q L query. Line 1. SELECT VIEW, underscore, NAME. Line 2. FROM USER, underscore, VIEWS, semicolon.

or to see what information is available about all the tables you have access to, write

An S Q L query using the DESCRIBE command. DESCRIBE USER, underscore, TAB, underscore, COLUMNS, semicolon.

Then you can write a query about specific catalog columns for tables, such as

A listing of an S Q L query. The query is as follows. Line 1. SELECT TABLE, underscore, NAME, comma, DEGREE, comma, NUM, underscore, ROWS. Line 2. FROM ALL, underscore, TABLES. Line 3. WHERE NUM, underscore, ROWS greater than 5, semicolon.

If you want to learn what information is available about constraints in the USER view, write

An S Q L query using the DESCRIBE command. DESCRIBE ALL, underscore, TABLES, semicolon.

Then you can then write a query using the names of the columns that are displayed, such as

A listing of an S Q L query. The query is as follows. Line 1. SELECT CONSTRAINT, underscore, NAME, 2 vertical bars, open single quote, blank space, close single quote, 2 vertical bars, CONSTRAINT, underscore, TYPE, 2 vertical bars, open single quote, blank space, close single quote, 2 vertical bars, TABLE, underscore, NAME. Line 2. FROM USER, underscore, CONSTRAINTS. Line 3. WHERE TABLE, underscore, NAME, equals, open single quote, ENROLL, close single quote, semicolon.

The DESCRIBE command can also be used to see the structure of an individual table, such as the Student table

An S Q L statement. DESCRIBE USER, underscore, CONSTRAINTS, semicolon.

or to see the structure of a view such as Histmaj

An S Q L statement. DESCRIBE Student, semicolon.

Chapter 7 will address use of the Java Database Connectivity (JDBC) Application Program Interface (API) for methods that can be used to query the database schema as well as the schema information of a result set from within a program.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.167.161