When you purchased your Oracle database software, the salesperson very likely told you that Oracle is preconfigured and should work perfectly right out of the box, with no tuning or configuration necessary. You might then have attended a local user group meeting, where a grizzled veteran Oracle database administrator told you that Oracle requires constant tuning and attention, and that you need to hire a consultant (maybe that same grizzled DBA!) to keep your database tuned and operating properly. Well, the truth lies somewhere between these positions.

Oracle is designed to be very flexible and configurable. This is actually an absolute necessity for a database that can be run on dozens of different hardware platforms in a multitude of configurations, supporting an almost infinite variety of applications and users. In order to achieve this flexibility, Oracle must provide the DBA with a method of specifying certain operational characteristics of the database in a clear and consistent manner. In Oracle, this is done through initialization parameters. Each of these parameters controls a specific aspect of the Oracle server. There are numerous initialization parameters—some documented, some not—and they are powerful and must be used with care. As the DBA, you will frequently specify or change initialization parameters in order to get your particular database running at peak performance. This chapter provides a comprehensive list of initialization parameters, along with some practical guidelines for their use.

Initialization parameters are specified in what is known as the Oracle INIT.ORA file, which we discussed in Chapter 11. This file is usually named INITsid.ORA, where sid is the SID (name) for the Oracle instance. For example, for an instance called TEST, the initialization parameters would be found in the file INIT TEST.ORA. The default location of this file varies with operating system. Table 12.1 shows the location of this file for some popular operating systems.

There are two categories of initialization parameters:

The next section lists the dynamically modifiable parameters. At the end of this chapter, you will also find listings of those parameters that may only be specified for a certain version of Oracle (Oracle7 or Oracle8).