7. Data Catalogs

Data catalogs store metadata for database objects such as relations, attributes, domains, constraints, authorization and security information, owners, indexes.

The catalog is stored as a set of read-only relations and views (system tables).

The catalog is used:

• By the DDL and SDL compilers
• By the query and DML parsers
  • These convert high-level queries and DML statements into low-level file access commands
  • They use the internal schema to determine an optimal way to execute the query/command
• By the DBA for authorization and security checking, who has privileges to update the catalog
• For external-to-conceptual mapping of queries/DML commands

The catalog consists of base tables and user-accessible views. The latter consists of:

• USER views (objects owned by user)
  • e.g. USER_CATALOG, USER_TAB_COLUMNS, USER_CONSTRAINTS
• ALL views (objects the user has privileges to view)
  • e.g. ALL_CATALOG, ALL_OBJECTS
• DBA views (all objects)

Examples

Some interesting catalog tables:

ALL_TABLES

Contains information about all tables you have view permissions for. To view the number of rows for a specific table:

SELECT NUM_ROWS FROM ALL_TABLES WHERE OWNER = 'owner_name' AND TABLE_NAME = 'table_name'

Number of blocks used is also stored.

The catalog is updated regularly, so it may contain stale data. The LAST_ANALYZED row contains the time it was last updated. To force an update, use the command ANALYZE TABLE table_name COMPUTE_STATISTICS.

ALL_USERS

Contains information about all users you have view permissions for. The password is not viewable.

USER_USERS contains information about your own user.

ALL_INDEXES

Contains information about indexes: metadata such as the number of distinct keys, if it is primary/secondary etc.

ALL_VIEWS

Contains the definition (SQL query) for the view and other metadata. The definition cannot be modified directly as the catalog is read-only.

ALL_CONS_COLUMNS

Check constraints for all tables: constraint name, table name, owner, name of the column the constraint is for etc.

ALL_CONSTRAINTS

Contains more information about constraints including the search condition (SQL constraint definition) and constraint type (stored as a char - can be check constraint, primary/unique key, referential integrity constraint etc.).

TABLE_PRIVILEGES

Grantee, owner, grantor, table name and privileges (select, insert etc.) for each grantee.