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:
- Using data dictionary components
- Listing useful views
- Managing control files
- Using OMF with control files
- Creating new control files
- 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
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
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
- V$ - dynamic performance views of current activity in the database
- GV$ - dynamic performance views of current activity in multiple
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
- 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:
- Shut down the database.
- Find the control file.
- 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
c. If you are moving the file to a new location, do so.
- 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.
- Save the init.ora file.
- 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
- V$CONTROLFILE_RECORD_SECTION - shows the data in the record section
of your control file