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
which displays the names of all tables in the database. To see the names of all views, write
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
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
or to see what information is available about all the tables you have access to, write
Then you can write a query about specific catalog columns for tables, such as
If you want to learn what information is available about constraints in the USER view, write
Then you can then write a query using the names of the columns that are displayed, such as
The DESCRIBE command can also be used to see the structure of an individual table, such as the Student table
or to see the structure of a view such as Histmaj
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.
18.217.211.92