What Is Contained in the System Catalog?

Posted by arlene

The system catalog contains a variety of information accessible to many users and is sometimes used for different specific purposes by each of those users.

The system catalog contains information such as the following:

• User accounts and default settings
• Privileges and other security information
• Performance statistics
• Object sizing
• Object growth
• Table structure and storage
• Index structure and storage
• Information on other database objects, such as views, synonyms, triggers, and stored procedures
• Table constraints and referential integrity information
• User sessions
• Auditing information
• Internal database settings
• Locations of database files

The system catalog is maintained by the database server. For example, when a table is created, the database server inserts the data into the appropriate system catalog table or view. When a table’s structure is modified, appropriate objects in the data dictionary are also updated. The following sections describe, by category, the types of data that are contained in the system catalog.

User Data

All information about individual users is stored in the system catalog: the system and object privileges a user has been granted, the objects a user owns, and the objects not owned by the user to which the user has access. The user tables or views are accessible to the individual to query for information.

Security Information

The system catalog also stores security information, such as user identifications, encrypted passwords, and various privileges and groups of privileges database users utilize to access the data. Audit tables exist in some implementations for tracking actions that occur within the database, as well as by whom, when, and so on. Database user sessions also can be closely monitored through the use of the system catalog in many implementations.

Database Design Information

The system catalog contains information regarding the actual database. That information includes the database’s creation date, name, objects sizing, size and location of data files, referential integrity information, indexes that exist in the database, and specific column information and column attributes for each table in the database.

Performance Statistics

Performance statistics are typically maintained in the system catalog as well. Performance statistics include information concerning the performance of SQL statements, both elapsed time and the execution method of a SQL statement taken by the optimizer. Other information for performance concerns memory allocation and usage, free space in the database, and information that allows table and index fragmentation to be controlled within the database. This performance information can be used to properly tune the database, rearrange SQL queries, and redesign methods of access to data to achieve better overall performance and SQL query response time.

Possibly related posts: (automatically generated)
What Is Contained in the System Catalog?

• Internet Commerce Virtual Warehouse
• Catalog Marketing: Taking the Best and Leaving the Rest
• A New Kind of "Shelf"
• Anatomy of the Online Store
• Benefits of Database Normalization
• The Active Directory and Dynamic DNS continue...
• Using SQL to Generate SQL
• Recommended! Internet Security Techniques and Software Tools
• Primary, Secondary, and Caching-Only Name Servers
• E-Building Blocks for Better Web Design