Hour 24
Working with the System Catalog
What You’ll Learn in This Hour:
▶ Defining the system catalog
▶ Creating the system catalog
▶ Understanding what data the system catalog contains
▶ Working with examples of system catalog tables
▶ Querying the system catalog
▶ Updating the system catalog
In this hour, you learn about the system catalog, commonly referred to as the data dictionary in some relational database implementations. By the end of this hour, you will understand the purpose and contents of the system catalog and will be able to query it to find information about the database based on commands that you have learned in previous hours. Each major implementation has some form of a system catalog that stores information about the database. This hour shows examples of the elements contained in a few of the different system catalogs for the implementations discussed in this book.
Defining the System Catalog
The system catalog is a collection of tables and views that contain important information about a database. A system catalog is available for each database. Information in the system catalog defines the structure of the database and information on the data contained therein. For example, the Data Definition Language (DDL) for all tables in the database is stored in the system catalog.
Figure 24.1 shows an example of the system catalog within the database. You can see that the system catalog for a database is actually part of the database. Within the database are objects, such as tables, indexes, and views. The system catalog is basically a group of objects that contain information that defines other objects in the database, the structure of the database, and various other significant information.
FIGURE 24.1
The system catalog
The system catalog for your implementation might be divided into logical groups of objects to provide tables that are accessible by the database administrator (DBA) and any other database user. For example, users might need to view the particular database privileges that they have been granted but don’t care how this is internally structured in the database. Users typically query the system catalog to acquire information on their own objects and privileges, whereas the DBA needs to inquire about any structure or event within the database. In some implementations, system catalog objects are accessible only by the DBA.
The system catalog is crucial to the DBA or any other database user who needs to know about the database’s structure and nature. It is especially important when the database user is not presented with a graphical user interface (GUI). The system catalog ensures that orders are kept not only by the DBA and users, but also by the database server.
Tip
Database System Catalogs Vary
Each implementation has its own naming conventions for the system catalog’s tables and views. The naming is not important; however, learning what the system catalog does is important, as is knowing what it contains and how and where you can retrieve the information.
Creating the System Catalog
The system catalog is created either automatically with the creation of the database or by the DBA immediately following the creation of the database. For example, a set of predefined, vendor-provided SQL scripts in Oracle is executed, which builds all the database tables and views in the system catalog that are accessible to a database user.
The system catalog tables and views are system owned and are not specific to any one schema. In Oracle, for example, the system catalog owner is a user account called SYS that has full authority in the database. In Microsoft SQL Server, the system catalog for the SQL server is located in the master database. Check your specific vendor documentation to find where the system catalogs are stored.
Determining What Is Contained in the System Catalog
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 database server maintains the system catalog. 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 updated. The following sections describe the types of data that the system catalog contains.
User Data
The system catalog stores all information about individual users: the system and object privileges a user has been granted, the objects a user owns, and the objects that the user does not own but can still access. The user tables and views are accessible for the individual to query for information. See your implementation documentation on the system catalog objects.
Security Information
The system catalog also stores security information, such as user identifications, encrypted passwords, and various privileges and groups of privileges that database users use to access the data. Audit tables exist in some implementations for tracking actions that occur within the database, as well as tracking by whom and when. Database user sessions can be closely monitored through 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, object 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 an 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. You can use this performance information 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.
Identifying System Catalog Tables by Implementation
Each implementation has several tables and views that comprise the system catalog, some of which are categorized by user level, system level, and DBA level. For your particular implementation, you should query these tables and read your implementation’s documentation for more information on system catalog tables. Table 24.1 has examples from the Oracle implementation, which is used for the examples in this book.
TABLE 24.1 Major Implementation System Catalog Objects
Oracle Table Name |
Information On… |
|
All tables in the database |
|
Segment storage |
|
All indexes |
|
All users of the database |
|
Roles granted |
|
Roles in the database |
|
System privileges granted |
|
Database free space |
|
The creation of the database |
|
Current sessions |
|
Tables accessible by a user |
|
Tables owned by a user |
|
All columns in tables accessible by a user |
|
All columns in tables owned by a user |
|
All table privileges granted to a role |
These are just a few of the system catalog objects from the Oracle relational database implementations that have been used in the book for examples. Many system catalog objects are similar between implementations, but they do vary and a plethora of information is stored in the system catalog. This hour strives to provide a brief overview of the system catalog and show some examples of how you can use the system catalog to get information about the BIRDS database and your users. Just keep in mind that each implementation is specific to the organization of the system catalog’s contents.
Querying the System Catalog
The system catalog tables or views are queried as any other table or view in the database using SQL. A user can usually query the user-related tables but might be denied access to various system tables that are accessible only by privileged database user accounts, such as the DBA.
The first example that follows uses the Oracle show user command to verify the current user account by which you are connected to the database. After that, a simple query is performed to select the name from the V$ database system catalog view. This confirms the name of the database in Oracle that you are connected to.
SQL> show user; USER is "RYAN" SQL> select name from v$database; NAME --------- XE 1 row selected.
The next example accesses the USER_TABLES system catalog table, which tells you information about any table that you own—in other words, any table that your user has created.
SQL> select table_name 2 from user_tables 3 where table_name not like 'DMRS_%'; TABLE_NAME -------------------------------------------------- LOCATIONS2 MIGRATION_TEST_DELETE BIG_BIRDS SMALL_BIRDS BIRDS_NESTS MIGRATION BIRDS_MIGRATION PREDATORS BIRDS_PREDATORS PHOTO_STYLES PHOTO_LEVELS CAMERAS PHOTOGRAPHERS FAVORITE_BIRDS PHOTOGRAPHER_STYLES PHOTOGRAPHER_CAMERAS OLD_CAMERAS COLORS BIRDS_COLORS FORMER_PHOTOGRAPHERS BIRDS LOCATIONS PHOTOS FOOD BIRDS_FOOD NESTS SHORT_BIRDS 27 rows selected.
Note
A Word About the Examples
These examples use the Oracle system catalog, simply because this implementation is used for the examples in this book.
The following query gets information from the ALL_TABLES system catalog table. ALL_TABLES returns not only any tables that you own, but also any tables to which you have been granted access. This query is actually the same as a previous query because you qualified the owner as the current connected user—in this case, RYAN.
SQL> select table_name 2 from all_tables 3 where table_name not like 'DMRS_%' 4 and owner = 'RYAN'; TABLE_NAME -------------------------------------------------- LOCATIONS2 MIGRATION_TEST_DELETE BIG_BIRDS SMALL_BIRDS BIRDS_NESTS MIGRATION BIRDS_MIGRATION PREDATORS BIRDS_PREDATORS PHOTO_STYLES PHOTO_LEVELS CAMERAS PHOTOGRAPHERS FAVORITE_BIRDS PHOTOGRAPHER_STYLES PHOTOGRAPHER_CAMERAS OLD_CAMERAS COLORS BIRDS_COLORS FORMER_PHOTOGRAPHERS BIRDS LOCATIONS PHOTOS FOOD BIRDS_FOOD NESTS SHORT_BIRDS 27 rows selected.
Caution
Manipulating System Catalog Tables Can Be Dangerous
Never directly manipulate tables in the system catalog in any way. (Only the DBA has access to manipulate system catalog tables.) Doing so might compromise the database’s integrity. Remember that information concerning the structure of the database, as well as all objects in the database, is maintained in the system catalog. The system catalog is typically isolated from all other data in the database. Some implementations, such as Microsoft SQL Server, do not allow the user to manipulate the system catalog directly, to maintain the integrity of the system.
The next query uses the COUNT aggregate function to return the total number of tables that the current user owns.
SQL> select count(table_name) "MY TABLES"
2 from user_tables
3 where table_name not like 'DMRS_%';
MY TABLES
----------
27
1 row selected.
The following two SQL statements are examples of a query against the USER_TAB_COLUMNS table. This table has information about all the columns in a given table.
SQL> select column_name 2 from user_tab_columns 3 where table_name = 'BIRDS'; COLUMN_NAME ------------------------ BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN EGGS BROODS INCUBATION FLEDGING NEST_BUILDER BEAK_LENGTH 11 rows selected. SQL> select data_type 2 from user_tab_columns 3 where table_name = 'BIRDS' 4 and column_name = 'WINGSPAN'; DATA_TYPE ---------------- NUMBER 1 row selected.
In the following examples, you are creating a role called SEE_BIRDS. Study these SQL statements as the role is created, SELECT access is granted to the role on the BIRDS table, and then information about the role is selected from the table ROLE_TAB_PRIVS.
SQL> alter session set "_ORACLE_SCRIPT"=true; Session altered. SQL> drop role see_birds; Role dropped. SQL> create role see_birds; Role created. SQL> grant select on birds to see_birds; Grant succeeded. SQL> select role from role_tab_privs where owner = 'RYAN'; ROLE --------------------- SEE_BIRDS 1 row selected. SQL> select role, owner, table_name, privilege 2 from role_tab_privs 3 where role = 'SEE_BIRDS'; ROLE OWNER TABLE_NAME PRIVILEGE ----------------- --------- --------------- ------------------- SEE_BIRDS RYAN BIRDS SELECT 1 row selected.
Note
These Are Just a Few of the System Catalog Tables Available
The examples shown in this section represent a small sampling of the information that is available from any system catalog. You might find it extremely helpful to dump data dictionary information using queries to a file that you can print and use as a reference. Refer to your implementation documentation for specific system catalog tables and columns within those available tables.
Updating System Catalog Objects
The system catalog is used only for query operations—even when the DBA is using it. The database server makes updates to the system catalog automatically. For example, a table is created in the database when a database user issues a CREATE TABLE statement. The database server then places the DDL that created the table in the system catalog under the appropriate system catalog table.
You never need to manually update a table in the system catalog, even though you might have the power to do so. The database server for each implementation performs these updates according to actions that occur within the database, as Figure 24.2 shows.
FIGURE 24.2
Updates to the system catalog
Summary
In this hour, you learned about the system catalog for a relational database. In a sense, the system catalog is a database within a database; it contains all the information about the database in which it resides. The system catalog serves as a way of maintaining the database’s overall structure, tracking events and changes that occur within the database, and providing the vast pool of information necessary for overall database management. The system catalog is used only for query operations; database users should not make changes directly to system tables. However, changes are implicitly made each time a change is made to the database structure itself, such as during the creation of a table. The database server makes these entries in the system catalog automatically.
Q&A
Q. As a database user, I realize that I can find information about my objects. How can I find information about other users’ objects?
A. Users can employ sets of tables and views to query in most system catalogs. One set of these tables and views includes information on the objects you have access to. To find out about other users’ access, you need to check the system catalogs that contain that information. For example, in Oracle, you can check the DBA_TABLES and DBA_USERS system catalogs.
Q. If a user forgets a password, can the DBA query a table to get that password?
A. Yes and no. The password is maintained in a system table, but it is typically encrypted so that even the DBA cannot read the password. The password must be reset if the user forgets it, which the DBA can easily accomplish.
Q. How can I tell which columns are in a system catalog table?
A. You can query the system catalog tables as you query any other table. Simply query the table that holds that particular information.
Workshop
The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
Exercises
1. At the prompt, type in queries to bring up each of the following:
▶ Information on all your tables
▶ Information on all columns in your tables
2. Show the name of the current database.
3. Show the name of the user by which you are connected to the database.
4. Create a role to update your
FOODtable.5. Select information about the role you created from the system catalog.
6. Explore the system catalog on your own. The information contained is nearly endless. Remember that, in Oracle, you can use the
DESCRIBE table_namecommand to show the columns that comprise a table.