CIS 421b: Database Administration I

Chapter 4: Data Dictionary Views and Control Files


This chapter introduces more components of an Oracle9i database. The objectives important to this chapter are:

  1. Using data dictionary components
  2. Listing useful views
  3. Managing control files
  4. Using OMF with control files
  5. Creating new control files
  6. Viewing control file data

Data dictionary views are based on tables owned by the SYS user, which means they are in that user's schema. They display information about the database. Like any other views, they may be queried like tables. These views should never be used to update data. The text warns that updating data using a data dictionary view could cause corruption in the database itself.

The text explains one method of getting a list of data dictionary views that illustrate the list of over a thousand views. (This seems irrational, but it is true. An online list of Oracle data dictionary views can be seen here.)

A discussion of each view is pointless, and would take too long. Be aware of the five categories that views can belong to:

  • USER - static views of a user's objects; limited to the schema of the user
  • ALL - static views of all objects a user owns or can query; the objects in these views will vary with the rights of the user
  • DBA - static views for the DBA; provide access to all database objects
  • V$ - dynamic performance views of current activity in the database
  • GV$ - dynamic performance views of current activity in multiple instances

Views in the first three categories will have similar appearances, with the exception that USER views do not list the owner of an object, since those views show only objects owned by the user running the query.

All users can use the USER and ALL views, by default. Only users with DBA rights can use the DBA views. Some frequently used static views are listed in the text:

  • USER_TABLES - tables the current user owns
  • USER_VIEWS - views the current user owns
  • ALL_DEPENDENCIES - dependencies between objects a user can see
  • USER_ERRORS - errors in views, procedures, or other executable objects the user owns
  • USER_TAB_COLUMNS - columns in all the tables the current user owns
  • USER_TAB_PRIVS - the rights that have been authorized to tables the current user owns
  • USER_INDEXES - indexes the current user owns
  • USER_IND_COLUMNS - columns in indexes that the current user owns
  • DBA_SOURCE - source code (program code) for all objects in the database
  • DBA_USERS - information for the DBA about all users

A short list of dynamic views for tuning the database is discussed:

  • V$SYSSTAT - statistics on changes in the database
  • V$SQL - shows actual SQL commands used in the system, how many times each is executed
  • V$SESSSTAT - shows system resources being used by each session
  • V$SESSION_WAIT - show information about sessions that had to wait for resources
  • V$FILESTAT - statistics on read and write activity for files
  • V$DATAFILE - shows a list of file numbers and names for all control files, log files, and data files.

The text has already described a database's control file as critical to operation of the database. It also has advised us that control files should be multiplexed. It turns out that this is no trick at all. The key to multiplexing is just to create a second copy of the control file, and store its location in the init.ora file.

The text give procedures to create additional control files, rename or move control files, replace damaged control files with copies of good ones, and finally, create a new control file from scratch. In each case, the procedure is similar:

  1. Shut down the database.
  2. Find the control file.
  3. a. If the control file you found is your only copy, copy it to another directory and give it a new name.
    b. If you are repairing a damaged file, delete the bad one, replace it with a copy of a good one, and give the copy the name of the deleted file.
    c. If you are moving the file to a new location, do so.
  4. Edit the init.ora file.
    a. If you created a new copy of the control file, add its description to the CONTROL FILES parameter in init.ora. Multiplexing is automatic, and all control files named here are assumed to be identical.
    b. If you repaired a damaged file with a copy of a good one, make its name and location match the information in the CONTROL FILES parameter.
    c. If you moved a control file, edit the information in the CONTROL FILES parameter to reflect that move.
  5. Save the init.ora file.
  6. Restart the database.

A lengthy procedure is listed for creating a new control file, in the event you have lost all good copies of yours. It includes making a list of all your datafiles, log files and their group numbers, and all their locations. This information is to be saved in a text file to be used as the basis for a CREATE CONTROLFILE command.

An alternative to the procedure above is to create and save the information needed for the CREATE CONTROLFILE command by issuing an SQL*Plus command that saves a backup copy of the control file. This can only be done if the database is functional, which is why the long, tedious procedure exists.

Several aspects of V$ views that apply to control files are listed. Here are some examples:

  • V$CONTROLFILE - shows the names of all control files used for your database
  • V$CONTROLFILE_RECORD_SECTION - shows the data in the record section of your control file