'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += '
'; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 1 - Introducing Relational Databases and Oracle8

Using Oracle8

Chapter 1

Introducing Relational Databases and Oracle8

What's a Database Management System?

A database can be defined as a collection of information organized in such a way that it can be retrieved and used. A database management system (DBMS) can further be defined as the tool that enables us to manage and interact with the database.

Most DBMSs perform the following functions:

Several different types of DBMSs have been developed to support these requirements. These systems can broadly be classified in the following classes:

Oracle8 stores objects in relational tables
Oracle8 is an object relational database management system, which allows objects to be stored in tables, in a manner similar to numbers and words being stored in an RDBMS system.

Oracle Database Files

An Oracle database physically resides in various files. Figure 1.1 shows the physical structure of an Oracle database.

Figure 1.1 : An Oracle database system.

The Initialization Parameter File

The parameter file, commonly known as INIT.ORA, contains initialization parameters that control the behavior and characteristics of the database and the instance that accesses the database. You can edit this text file in your favorite editor.

Changing the parameter file
The initialization parameter file is read by the instance only during startup. Any changes made in the initialization file take effect only after you shut down and restart the instance.

Oracle supplies a sample INIT.ORA file in the $ORACLE_HOME/dbs directory. $ORACLE_HOME is the top-level directory under which Oracle software is installed; it doesn't need to be the user Oracle's home directory. The default name of an instance's parameter file is initSID.ora, in which SID (System IDentifier) is a character string that uniquely identifies the instance on the system.

You can override the defaults by using the PFILE parameter of the Server Manager's startup command. The IFILE parameter in this file allows you to nest multiple initialization files for the same instance.

The Control File

The control file contains information about the database's physical structure and status. It has information about several things: the total number of data files; log files; redo log groups; redo log members; current redo log to which the database is writing; name and location of each data file and online redo log files; archived log history; and so on. Starting from Oracle8, the control file also contains information about the backup of the database.

Oracle updates the control file
Oracle automatically records any structural changes in the database- for example, addition/deletion of a data file-in the control file(s). An Oracle instance updates the control file(s) with various status information during its operation.

The control_file initialization parameter specifies the name and location of a database's control file. It's strongly recommended that you specify multiple files in the control_file initialization parameter to mirror the control file to multiple locations. The V$CONTROL_FILE data dictionary view contains information about the database's control file.

The V$CONTROLFILE_RECORD_SECTION dynamic performance view contains detailed structure information about the control file. This view gives the information about all records contained in the control file.

The Data File

An Oracle database stores user information in physical data files. A data file can contain tables, indexes, clusters, sequences, data dictionary, rollback segments, temporary segments, and so on. At the logical level, Oracle manages space in terms of tablespace (a group of one or more data files). When an Oracle database is created, it has only one tablespace: SYSTEM. Other tablespaces and the associated data files are added later, as needed.

You can specify the name, location, and size of a data file while creating the tablespace to which the data file belongs. Oracle uses control files to store the name and location of the data files. Use the data dictionary views V$DATAFILE and DBA_DATA_FILES to retrieve the information about a database's data files.

Redo Log Files

Oracle records all changes against the database in the redo log file and uses the contents of the redo log file to regenerate the transaction changes in case of failure. An Oracle database has two or more redo log files. Oracle allows you to mirror the redo log files, thus a redo log group contains one or more files (members). Oracle writes to all the members of a redo log group simultaneously. An Oracle instance writes to redo log groups in cyclical order-that is, it writes to one redo log group and then to the next when the earlier one is filled up. When the last available redo log group is filled, it switches over to the first one.

For detailed information about redo log files,

You can specify the name, location, and size of the redo log files during database creation. The V$LOGFILE data dictionary view contains redo log files' information. You can also add, delete, and relocate redo log files by using the ALTER DATABASE command.

Archived Redo Log Files

The archived log file contains a copy of the redo log file. Archived redo log files are useful in recovering the database and all committed transactions in case of failures (such as disk failure). When an Oracle database is operating in archive log mode, it needs to archive the recently filled redo log file before it can reuse it.

You can enable automatic archiving by setting the initialization parameter LOG_ARCHIVE_START to TRUE or by issuing the archive log start command after the instance startup. When automatic archiving is enabled, the ARCH (archiver) process copies the filled redo log files to the directory specified by LOG_ARCHIVE_DEST. The LOG_ARCHIVE_FORMAT parameter defines the default names of archived log files.

Understanding Database Instances

An Oracle database stores data in physical data files and allows controlled user-access to these files through a set of operating system processes. These processes are started during the instance startup. Because they work silently, without direct user interaction, they're known as background processes. To enable efficient data manipulation and communication among the various processes, Oracle uses shared memory, known as Shared Global Area (SGA). These background processes and the shared memory segment together are referred as an Oracle instance. In a parallel server environment, a database can be accessed by multiple instances running on different machines.

An Oracle instance consists of the following background processes:

Oracle background processes that are always started
The LMON, PMON, DBWR, and LGWR processes are always present for an instance. Other processes are started by setting up a related initialization parameter.

If you're running Oracle's parallel server option, you also see the following background processes on each instance:

Figure 1.2 shows the components of an Oracle instance: the SGA and background processes.

Figure 1.2 : An Oracle instance consists of the SGA and background processes.

Starting and Stopping Instances

An Oracle database isn't accessible to users until it's opened by an Oracle instance. In an Oracle parallel server environment, an Oracle database is accessed by more than one instance. Each instance has its own set of background processes and the SGA. An instance startup operation involves starting all the background processes and allocating the shared memory area (see Figure 1.3).

Who can start up and shut down an Oracle instance?
An instance startup operation can be done only by users with the requisite OS privileges or who have been assigned an OSOPER or OSDBA role.

Figure 1.3 : Oracle instance startup consists of three steps.

How Oracle starts instances

  1. Oracle starts all the background processes and allocates the SGA. Oracle reads the initialization parameter file during this step.
  2. Oracle reads the control file and associates the control with the instances. It detects the conditions of the database from the last shutdown/crash.
  3. Oracle reads the file headers of all the data files and redo log files. It ensures consistency among all data files. If the instance is being started after a crash or shutdown abort, Oracle also applies all the redo log since the last successful checkpoint. Oracle database is accessible to users after completing this step.

If the instance is started after a crash or shutdown abort, Oracle needs to perform rollback operations for the uncommitted transaction. This operation is performed by SMON in the background while the database is open and available for use.

An Oracle instance shutdown closes the database, dismounts it, and then removes the SGA and the background processes. Shutdown offers three modes: normal, immediate, and abort. Shutdown normal and shutdown immediate are used most often, whereas shutdown abort should be used with caution. During shutdown normal, Oracle waits for all users to disconnect, writes all modified data to the data files, and then updates files headers, online redo log files, and control files. Shutdown immediate disconnects all users and then proceeds similarly to shutdown immediate. Shutdown abort just removes all the background processes and the SGA; all cleanup work is done during the next startup.

Table 1.1 lists Server Manager commands to start and stop an Oracle instance.

Table 1.1  Server Manager startup and shutdown commands
startup andUses the default parameter file to start startup open the instance, mount the database, and open it
startup pfile=file Starts the instance by using the specified parameter file
startup nomountAllocates the SGA and starts the background processes; doesn't mount and open the database
startup mountAllocates the SGA, starts the background process, and mounts the database; doesn't open the database
alter database mountMounts the database after the instance is started with startup nomount command
alter database open Opens the database after it's mounted by the startup mount command
shutdownCloses the instance after all users disconnect (normal shutdown)
shutdown immediateDoesn't allow any new transactions to start; rolls back uncommitted transactions and closes the instance
shutdown abortImmediately removes the SGA and the background processes

To learn how to start and stop database instances with Oracle Enterprise Manager,

Oracle8's Tools

Oracle provides various tools for application development and for performing administrative functions:

Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager is a graphical system management tool that allows you to perform multiple tasks in a complicated database environment. OEM comes with several components. Some components, such as Oracle Expert and Performance Manager, are priced separately. Chapter 4 "Managing with Oracle Enterprise Manager (OEM)," explains how to use these components. OEM's major components are as follows (see Figure 1.4):

Figure 1.4 : Oracle Enterprise Manager consists of several modules.

Backup Manager Software Manager
Security Manager Oracle Expert
Data Manager Lock Manager
Storage Manager TopSession Monitor
Instance Manager Performance Manager
Schema Manager Tablespace Manager
SQL Worksheet


The only interface available between end users and an RDBMS is Structured Query Language (SQL). All other applications and tools that users utilize to interact with the RDBMS act as translators/interpreters. These tools generate SQL commands based on a user's request and pass the generated SQL commands on to the RDBMS.

SQL*Plus can't start or stop an instance
A database administrator can't start and shut down an Oracle instance by using SQL*Plus.

SQL*Plus, Oracle's version of SQL, is one of the most commonly used Oracle tools. SQL*Plus enables users to instruct the Oracle instance to perform the following SQL functions:

In addition to these basic SQL functions, SQL*Plus also provides several editing and formatting functions that enable users to print query results in report format.

Setting Up the SQL*Plus Environment

SQL*Plus has many advanced functions that you can use to present data in a visually pleasing format. You can set various environment variables in order to control the way SQL*Plus outputs a query. Table 1.2 lists some of the most common commands to set up the environment, which you can enter at the SQLPLUS> prompt.

Table 1.2  SQL*Plus environment commands
set pagesizeSets the number of lines per page
set linesizeSets the number of characters in a line
set newpageSets the number of blank lines between pages
set pauseCauses SQL*Plus to pause before each page
set arraySets the number of rows retrieved at a time
set feedbackDisplays the number of records processed by a query
set headingPrints a heading at the beginning of the report
set serveroutputAllows output from DBMS_OUTPUT.PUT_LINE stored procedure to be displayed
set timeDisplays timing statistics
set termAllows you to suppress output generated by a command executed from a file

Set up the environment automatically
You also can use the LOGIN.SQL and GLOGIN.SQL files to set up the environment for the current session while invoking SQL*Plus.


PL/SQL stands for Procedural Language/Structured Query Language. It allows a user to utilize structured programming constructs similar to third-generation languages such as C, Fortran, and COBOL. PL/SQL enhances SQL by adding the following capabilities:

PL/SQL is embedded in Oracle8 tools
Although you can use PL/SQL as a programming language, it's also available as part of Oracle tools such as Oracle Forms and Oracle Reports. The PL/SQL engine embedded in these tools acts as the preprocessor.

With PL/SQL, you can use SQL commands to manipulate data in an Oracle database and also use structured programming constructs to process the data.


Net8, formerly known as SQL*Net, is Oracle's networking interface. It allows communication between various Oracle products residing on different machines. It enables communication among client, server, and Oracle databases in a distributed environment. At the client end, the client application code passes messages on to the Net8 residing locally, and the local Net8 transfers messages to the remote Net8 via the underlying transport protocol. These messages are received by Net8 at the server, which sends them to the database server for execution. The server executes the request and responds to the client following the same path. Figure 1.5 shows the communication between client and server using Net8.

Figure 1.5 : The client and the server communicate with each other through Net8.

Net8 has many enhancements over its predecessor SQL*Net, such as connection pooling, multiplexing, listener load balancing, and caching the network addresses at the client end. Net8 is backward-compatible and can coexist with SQL*Net version 2.


A third-generation language compiler doesn't recognize the SQL needed to interface with the RDBMS. Therefore, if you need power and flexibility of a language such as C, C++, Fortran, or COBOL and also want it to interface with the Oracle8 RDBMS, you need a tool that can convert the SQL statements to the calls that a language compiler can understand. As Figure 1.6 shows, a precompiler program reads structured source code and generates a source file that a language compiler can process. Oracle provides several precompilers, such as Pro*C, Pro*Cobol, Pro*Fortran, and Pro*Pascal.

Figure 1.6 : You develop programs by using a precompiler.

You might want to use precompilers to get better performance while developing long-running batch programs and time-critical programs. You can do the following by using precompilers:


Developer/2000 provides the complete set of tools to develop applications that access an Oracle database. It consists of tools for creating forms, reports, charts, queries, and procedures. It also enables you to deploy existing and new applications on the Web. Developer/2000 consists of the following component tools:

Traditionally, Developer/2000 supported the client/server architecture, where the client tools and the application reside on one machine (usually the end-user PC) and the database server resides on another machine. With the proliferation of the Web, however, Oracle has introduced a three-tier architecture in which an additional server that runs the application code has been introduced.

Client/server, or the three-tier, architecture for installing Developer/2000 is highly recommended because the workload is distributed among the client, database server, and application servers in this structure. In addition, the application, Developer/2000, and the database software are independent of each other, thus making maintenance easier. SQL*Net or Net8 needs to be installed on the client and the database server to enable the connectivity between the two.

The Oracle8 Data Dictionary

Oracle stores information about all the objects defined by the users, structural information about the database, and so on in its internal tables. These Oracle internal tables and associated objects are collectively referred as the data dictionary. The data dictionary is owned by the user SYS and always resides in the SYSTEM tablespace.

Data dictionary tables are created when the database is created
Oracle automatically updates these tables whenever it needs to. Users should never update any table in the data dictionary. Several Oracle and non-Oracle tools also create some objects in the data dictionary that are used for storing operational, reference, and configuration information.

Information stored in the data dictionary is available to users through data dictionary views. A database administrator or a user can use the data dictionary to view the following information:

Oracle's data dictionary views can broadly be defined in the following classes:

Table 1.3 lists important Oracle8 data dictionary views. Similar views with DBA and ALL prefixes are available.

Table 1.3  Important data dictionary views
View Name:
USER_ALL_TABLESContains descriptions of all tables available to the user
USER_CLUSTERSContains information about clusters created by the user
USER_CONSTRAINTSContains information about the constraint defined by the user
USER_DB_LINKSContains information about the database link created by the user
USER_ERRORSGives all current errors on all stored objects for the user
USER_EXTENTSLists all the extents used by the objects owned by the user
USER_FREE_SPACELists all free extents in the tablespaces on which the user has privilege
USER_INDEXESGives information about indexes created by the user
USER_IND_COLUMNSGives the name of all the columns on which the user has created indexes
USER_JOBSGives all jobs in the job queue owned by the user
USER_RESOURCE_LIMITSGives resource limits applicable for the user
USER_SEGMENTSGives information about all segments owned by the user
USER_SEQUENCESLists information about all sequences owned by the user
USER_SNAPSHOTSGives information about all snapshots the user can view
USER_SYNONYMSGives the name of all private synonyms for the user
USER_TAB_COLUMNSGives the name of all columns in all tables the user owns
USER_TAB_PARTITIONSGives information about all table partitions owned by the user
USER_TABLESGives information about all tables the user owns
USER_TRIGGERSGives information for all triggers created by the user

Statistics and the Data Dictionary

Several data dictionary views contain columns with statistics information for the object. For example, the USER_TABLES view contains columns NUM_ROWS (number of rows in the table), BLOCKS (number of data blocks used in the table), AVG_ROW_LEN (average row length of a row in the table), and so on. These columns are populated only when you analyze the object by using the ANALYZE command. You should analyze the objects at regular intervals to keep the statistics up-to-date.

Dynamic Performance Tables

An Oracle instance maintains comprehensive information about its current configuration and activity. These statistics are accessible to the database administrator through dynamic performance views. Most of these views are based on in-memory table-like structures known as virtual tables (because they aren't real tables). The majority of these views have names starting with V$. These virtual tables don't require disk storage space and aren't stored in any tablespace. By default, the dynamic performance views are accessible to the SYS user or to the users having a SYSDBA role. Contents of these views are updated continuously while the instance is active.

Use TIMED_STATISTICS to gather timing information
Many dynamic performance views contain columns, such as WAIT_TIME and TOTAL_WAITS, that contain timing information. Such columns are populated by Oracle only when the TIMED_STATISTICS parameter is set to TRUE.

Table 1.4 describes important dynamic performance views. These views are for Oracle8; some may not exist in Oracle7.

Table 1.4  Dynamic performance views
View Name:
V$ACCESSDisplays information about locked database objects and the sessions accessing them
V$CONTROLFILELists names of the database control files
V$DATABASEContains miscellaneous database information such as database name creation date, archive/no archive log mode, and so on
V$DATAFILEContains information about the data files that are part of the database (This information is from the control file.)
V$DATAFILE_HEADERSimilar to V$DATAFILE, except that information is based on the contents of each data file header
V$DB_LINKLists information about all active database links
V$FILESTATDisplays read/write statistics for each database data file
V$FIXED_TABLEContains names of all fixed tables in the database
V$FIXED_VIEW_DEFINITIONLists definitions of all the dynamic performance views; you can see how Oracle creates dynamic performance views based on its internal x$ tables; these x$ tables are known as fixed tables
V$LICENSELists license-related information
V$LOCKShows the locks held and requested; information in this view useful while tuning the database performance or hanging issues
V$LOCKED_OBJECTLists all the objects locked in the database and the sessions that are locking the objects
V$LOGLists information about the online redo logs
V$LOG_HISTORYContains information about the archived redo log file
V$MYSTATLists statistics about the current session
V$PARAMETERLists current values of the initialization parameters; the ISDEFAULT column indicates whether the parameter value is the default
V$PROCESSLists all Oracle processes; a value of 1 in the BACKGROUND column indicates that the process is an Oracle background process; a NULL value in this column indicates a normal user process
V$RECOVER_FILEUsed to query the information about the files needing media recovery; this view can be queried after the instance mounts the database
V$ROLLNAMELists names of all the online rollback segments
V$ROLLSTATLists statistics for all online rollback segments
V$SESSIONContains information about all the current sessions; this view, one of the most informative, has about 35 columns
V$SESSION_EVENTContains information about waits each session has incurred on events; use this view if you're experiencing slow performance
V$SESSION_WAITLists the events and resources Oracle is waiting on; information in this view can be used to detect performance bottlenecks
V$SESSTATContains performance statistics for each active session
V$SESS_IOLists I/O statistics about each active session
V$STATNAMEGives names of Oracle statistics displayed in V$SESSTAT and V$SYSSTAT
V$SYSSTATContains performance statistics for the whole instance
V$SYSTEM_EVENTContains information for various Oracle events
V$TABLESPACELists names of all tablespaces in the database
V$TRANSACTIONLists statistics related to transactions in the instance
V$WAITSTATContains block contention statistics

Global dynamic performance views
In a parallel server environment, every V$ view has a corresponding GV$ view. These views, known as global dynamic performance views, contain information about all active instances of an Oracle parallel server environment. The INST_ID column displays the instance number to which the information displayed in the GV$ view belongs.

Use fixed tables with caution!
Oracle doesn't encourage the use of fixed tables listed in V$FIXED_TABLE because their structure isn't published and can be changed.

© Copyright, Macmillan Computer Publishing. All rights reserved.