Oracle9i Database Administrator's Guide
Release 1 (9.0.1)

Part Number A90117-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Creating an Oracle Database

This chapter discusses the process of creating an Oracle database, and contains the following topics:

Considerations Before Creating a Database

Database creation prepares several operating system files to work together as an Oracle database. You need only create a database once, regardless of how many datafiles it has or how many instances access it. Creating a database can also erase information in an existing database and create a new database with the same name and physical structure.

The following topics can help prepare you for database creation.

Planning for Database Creation

Prepare to create the database by research and careful planning. The following are some recommended actions:

Action  For more information... 
  • Plan the database tables and indexes and estimate the amount of space they will require.


Part II, "Oracle Server Processes and Storage Structure"

Part III, "Schema Objects" 

  • Plan the layout of the underlying operating system files that are to comprise your database. Proper distribution of files can improve database performance dramatically by distributing the I/O for accessing the files. There are several ways to distribute I/O when you install Oracle and create your database. For example, placing redo log files on separate disks or striping; placing datafiles to reduce contention; and controlling density of data (number of rows to a data block).


Oracle9i Database Performance Guide and Reference

Your Oracle operating system specific documentation 

  • Consider using the Oracle Managed Files feature to create and manage the operating system files that comprise your database storage and ease their administration.


Chapter 3, "Using Oracle-Managed Files" 

  • Select the global database name, which is the name and location of the database within the network structure. Create the global database name by setting both the DB_NAME and DB_DOMAIN initialization parameters.


"Determining the Global Database Name" 

  • Familiarize yourself with the initialization parameters that comprise the initialization parameter file. Become familiar with the concept and operation of a server parameter file. A server parameter file allows you to store and manage your initialization parameters persistently in a server-side disk file.


"Initialization Parameters and Database Creation"

"What is a Server Parameter File?"

Oracle9i Database Reference 

  • Select the database character set.

    All character data, including data in the data dictionary, is stored in the database character set. You must specify the database character set when you create the database.

    If clients using different character sets will access the database, then choose a superset that includes all client character sets. This ensures that the system will not waste time using replacement characters to facilitate conversions. You can also specify an alternate character set.


Oracle9i Globalization and National Language Support Guide 

  • Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created.

    The SYSTEM tablespace and most other tablespaces use the standard block size. Additionally, you can specify up to four non-standard block sizes when creating tablespaces.


"Specifying Database Block Sizes" 

  • Use an undo tablespace to manage your undo records, rather than rollback segments.


Chapter 13, "Managing Undo Space" 

  • Develop a backup and recovery strategy to protect the database from failure. It is important to protect the control file by multiplexing, to choose the appropriate backup mode, and to manage the online and archived redo logs.


Chapter 7, "Managing the Online Redo Log"

Chapter 8, "Managing Archived Redo Logs"

Chapter 6, "Managing Control Files"

Oracle9i Backup and Recovery Concepts 

  • Familiarize yourself with the principles and options of starting up and shutting down an instance and mounting and opening a database.


Chapter 4, "Starting Up and Shutting Down" 

Meeting Creation Prerequisites

To create a new database, the following prerequisites must be met:

All of these are discussed in the Oracle installation guide specific to your operating system. Additionally, the Oracle Universal Installer will guide you through your installation and provide help in setting up environment variables, directory structure, and authorizations.

Deciding How to Create an Oracle Database

Creating a database includes the following operations:

You use the CREATE DATABASE statement to perform these operations, but other actions are necessary before you have an operational database. A few of these actions are creating users and temporary tablespaces, building views of the data dictionary tables, and installing Oracle built-in packages. This is why the database creation process involves executing prepared scripts. But, you do not necessarily have to prepare this script yourself.

You have the following options for creating your new Oracle database:

The Oracle Database Configuration Assistant

The Oracle Database Configuration Assistant (DBCA) is a graphical user interface (GUI) tool that interacts with the Oracle Universal Installer, or can be used standalone, to simplify the creation of a database. Online help is available to assist you in its use.

You can create or delete a database using the Database Configuration Assistant. You can configure database options so as to add options that have not been previously configured. Additionally, the Database Configuration Assistant enables you to create and manage database templates. You can create a template of a database definition and later modify that template, or you can modify templates supplied by Oracle. You can also create a template of an existing database and clone it.

The Oracle Database Configuration Assistant can be used to create single instance databases, or it can be used to create or add instances in an Oracle Real Application Clusters environment.

This section contains the following topics that introduce you to the Oracle Database Configuration Assistant:

Advantages of Using the Oracle Database Configuration Assistant

These are a few of the advantages of using the Oracle Database Configuration Assistant:

Creating a Database

You can create a database from predefined templates provided by Oracle or from templates that you or others have created. When you select a template, you can choose either to include datafiles or not. If you select a template with datafiles, you will be able to save the database creation information as a template or script. You can run the script later to create a new database.

This section does not discuss all of the choices available to you when you use the Oracle Database Creation Assistant to create a database. Rather, it is intended to provide an introduction to its use. Wizards will guide you in making choices for defining the database that you want to create.

Using Templates for Creating a Databases

Oracle provides templates for the following environments:

Environment  Description of Environment 

DSS (Data Warehousing) 

Users perform numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues.

These queries (typically read-only) range from a simple fetch of a few records to numerous complex queries that sort thousands of records from many different tables.  

OLTP (Online Transaction Processing) 

Many concurrent users performing numerous transactions requiring rapid access to data. Availability, speed, concurrence, and recoverability are key issues.

Transactions consist of reading (SELECT statements), writing (INSERT and UPDATE statements), and deleting (DELETE statements) data in database tables.  

New Database 

This template allows you maximum flexibility in defining a database. 

You have the option of viewing details for a template. The "show details" page displays specific information about the database defined by a template including:

You can save the details page as an HTML file.

Including Datafiles

When you select a template, you also specify whether the database definition is to include datafiles. The following types of databases are created accordingly:

Include Datafiles?  Database Structure 


This type of template contains only the structure of the database and gives you full control to specify and change all database parameters. If you select a template without datafiles, database creation will take longer since all scripts must be run to create the schema. 


This type of template contains both the structure and the physical datafiles of the existing database. In effect, this template copies a prebuilt seed, or starter, database. In the seed database, Oracle automatically includes features that result in a highly effective and easier to manage database.

When you select a template that includes datafiles, the database is created faster since the schema is present in the datafiles. Also, all log files and control files are automatically created for the database. You can change only the following:

  • Name of the database

  • Destination of the datafiles

  • Control files

  • Redo log groups

Other changes must be made using command line SQL statements or the Oracle Enterprise Manager after database creation. You can also use custom scripts for additional modification.  

Specifying the Global Database Name and Parameters

You are guided thorough a series of pages that allow you to further define your database or to accept default parameter values and file locations as recommended by Oracle. You provide a global database name, specify database options to include, determine mode (dedicated server of shared server), and ultimately you can specify initialization parameter.

When specifying initialization parameters, the first page presented is the "memory parameters" page. It is used to determine the values of initialization parameters that size the initial System Global Area (SGA). You select one of the following options:

Type of Database  Memory Initialization Parameters 


This creates a database with minimal user input. You do not specify specific initialization parameter values; instead, you specify the maximum number of concurrent users, the percentage of physical memory reserved for Oracle, and a database type (OLTP, Multipurpose or Data Warehousing). Oracle uses this information to create an efficient and effective database for your environment. 


Custom allows you to specify initialization parameter values that affect the size of the System Global Area (SGA). It can be used by very experienced database administrators who have specific tuning needs. Other areas that you will be allowed to customize include:

  • Data, control, and redo log file settings

  • Tablespace sizes

  • Extent sizes

  • Archiving formats and destinations

  • Trace file destinations

  • Character set specifications


Completing Database Creation

After you have completed the specification of the parameters that define your database you can:

Configuring Database Options

When you elect to configure database options, you can add Oracle options that have not previously been configured for use with your database

The following is a partial list of Oracle options or functionality that you can install in your database. Oracle provides a complete list from which you can select on the "configure database options" page. Some of the listed options might already be installed depending upon how you defined the database. Those options that are already installed are noted as such (grayed out).

Deleting a Database

The Oracle Database Configuration Assistant enables you to delete a database. When you do so, you delete the database instance and its control file(s), redo log files, and data files. The initialization parameter file is not deleted.

Managing Templates

A template is a definition of a database. Oracle provides some basic templates for you to use, as discussed earlier in "Creating a Database", or you have the option of saving database definitions that you create yourself. These saved definitions can then be used to create new databases in the future, without having to completely redefine them. Oracle saves templates in XML files.

The following are some of the advantages of using templates:

A "template management" page provides you with several options that enable you to modify existing definitions or to create definitions based upon existing databases:

You can view the "show details" page to see detail information about the templates you create or modify.

The "template management" page also allows you to delete existing templates.

Manually Creating an Oracle Database

This section presents the steps involved when you create a database manually. These steps should be followed in the order presented. You will previously have created your environment for creating your Oracle database, including most operating system dependent environmental variables, as part of the Oracle software installation process.

The examples shown in these steps are to create the database mynewdb.


At this point, you may not be familiar with all of the initialization parameters and database structures discussed in this section. These steps contain many cross references to other parts of this book to allow you to learn about and understand these parameters and structures. 

Step 1: Decide on Your Instance Identifier (SID)

Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to avoid confusion with other Oracle instances that you may create later and run concurrently on your system.

The following example sets the SID for the instance and database we are about to create:

% setenv ORACLE_SID mynewdb

The value of the DB_NAME initialization parameter should match the SID setting.

Step 2: Establish the Database Administrator Authentication Method

You must be authenticated and granted appropriate system privileges in order to create a database. You can use the password file or operating system authentication method. Database administrator authentication and authorization is discussed in the following sections of this book:

Step 3: Create the Initialization Parameter File.

The instance (System Global Area and background processes) for any Oracle database is started using an initialization parameter file. One way of getting started on your initialization parameter file is to edit a copy of the sample initialization parameter file that Oracle provides on the distribution media or the sample presented in this book.

For ease of operation, store your initialization parameter file in Oracle's default location, using the default name. That way, when you start your database, it is not necessary to specify the PFILE parameter because Oracle automatically looks in the default location for the initialization parameter file.

Default parameter file locations are shown in the following table:

Platform  Default Name  Default Location 



For example, the initialization parameter file for the mynewdb database is named:



For example, the initialization parameter file for the mynewdb database is stored in the following location:





The following is the initialization parameter file used to create the mynewdb database.

Sample Initialization Parameter File
# Cache and I/O

# Cursors and Library Cache
# Diagnostics and Statistics

# Control File Configuration
# Archive

# Shared Server
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL 
# (listener.ora and sqlnet.ora)
#               "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
# Miscellaneous

# Distributed, Replication and Snapshot

# Network Registration
# Pools
# Processes and Sessions
# Redo Log and Recovery

# Resource Manager

# Sort, Hash Joins, Bitmap Indexes

# System Managed Undo and Rollback Segments

See Also:


Step 4: Connect to the Instance

Step 5: Start the Instance.

Step 6: Issue the CREATE DATABASE Statement

To create the new database, use the CREATE DATABASE statement. When you execute a CREATE DATABASE statement, Oracle performs (at least) the following operations. Other operations are performed depending upon the clauses that you specify in the CREATE DATABASE statement or initialization parameters that you have set.

The following statement creates database mynewdb:

   DATAFILE '/vobs/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE 
   UNDO TABLESPACE undotbs DATAFILE '/vobs/oracle/oradata/mynewdb/undotbs01.dbf'
   LOGFILE GROUP 1 ('/vobs/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
           GROUP 2 ('/vobs/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
           GROUP 3 ('/vobs/oracle/oradata/mynewdb/redo03.log') SIZE 100M;

A database is created with the following characteristics:

See Also:

Oracle9i SQL Reference for more information about specifying the clauses and parameter values for the CREATE DATABASE statement 

Step 7: Create Additional Tablespaces

To make the database functional, you need to create additional files and tablespaces for users. The following sample script creates some additional tablespaces:

-- create a user tablespace to be assigned as the default tablespace for users
     DATAFILE '/vobs/oracle/oradata/mynewdb/users01.dbf' 
-- create a tablespace for indexes, separate from user tablespace
     DATAFILE '/vobs/oracle/oradata/mynewdb/indx01.dbf' 

For information about creating tablespaces, see Chapter 11, "Managing Tablespaces".

Step 8: Run Scripts to Build Data Dictionary Views

Run the scripts necessary to build views, synonyms, and PL/SQL packages:


The following table contains descriptions of the scripts:

Script  Description 


Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.  


Runs all scripts required for or used with PL/SQL. 

You may want to run other scripts. The scripts that you run are determined by the features and options you choose to use or install. Many of the scripts available to you are described in the Oracle9i Database Reference.

See your Oracle installation guide for your operating system for the location of these scripts.

Step 9: Run Scripts to Install Additional Options (Optional)

If you plan to install other Oracle products to work with this database, see the installation instructions for those products. Some products require you to create additional data dictionary tables. Usually, command files are provided to create and load these tables into the database's data dictionary.

See your Oracle documentation for the specific products that you plan to install for installation and administration instructions.

Step 10: Create a Server Parameter File (Recommended)

Oracle recommends you create a server parameter file as a dynamic means of maintaining initialization parameters. The server parameter file is discussed in "Managing Initialization Parameters Using a Server Parameter File".

The following script creates a server parameter file from the text initialization parameter file and writes it to the default location. The instance is shut down, then restarted using the server parameter file (in the default location).

-- create the server parameter file 
CREATE SPFILE='/vobs/oracle/dbs/spfilemynewdb.ora' FROM
-- this time you will start up using the server parameter file

Step 11: Back Up the Database.

You should make a full backup of the database to ensure that you have a complete set of files from which to recover if a media failure occurs. For information on backing up a database, see the Oracle9i Backup and Recovery Concepts.

Oracle9i Features that Simplify Database Creation and Management

In addition to using the Database Configuration Assistant for creating your database, Oracle9i offers you other options that can simplify the creation, operation, and management of your database. There are clauses, some shown in the above CREATE DATABASE statement, which are discussed in this section. Additionally, you can choose to use the Oracle Managed Files feature, which automatically creates and manages the underlying operating system files of your database.

Also discussed in this section is the management of the time zone files used to support the SET TIME_ZONE feature.

This section contains the following topics:

Creating an Undo Tablespace

Optionally, instead of using rollback segments in your database, you can use an undo tablespace. This requires the use of a different set of initialization parameters and, if creating the database for the first time, the UNDO TABLESPACE clause of the CREATE DATABASE statement. You also must include the following initialization parameter:


This initialization parameter tells Oracle that you want to operate your database automatic undo management mode. In this mode rollback information, referred to as undo, is stored in an undo tablespace rather than rollback segments and is managed by Oracle.

See Also:

Chapter 13, "Managing Undo Space" for information about the creation and use of undo tablespaces 

Creating a Default Temporary Tablespace

The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement specifies that a temporary tablespace is to be created at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.

Users can be explicitly assigned a default temporary tablespace in the CREATE USER statement. But, if no temporary tablespace is specified, they default to using the SYSTEM tablespace. It is not good practice to store temporary data in the SYSTEM tablespace. To avoid this problem, and to avoid the need to assign every user a default temporary tablespace at CREATE USER time, you can use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.

If you decide later to change the default temporary tablespace, or to create an initial one after database creation, you can do so. You do this by creating a new temporary tablespace (CREATE TEMPORARY TABLESPACE), then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new temporary default tablespace.

The following statement assigns a new default temporary tablespace:


The new default temporary tablespace must be an existing temporary tablespace.

You cannot drop a default temporary tablespace, but you can assign a new default temporary tablespace, then drop the former one. You are not allowed to change a default temporary tablespace to a permanent tablespace, nor can you take a default temporary tablespace offline.

Users can obtain the name of the current default temporary tablespace using the DATABASE_PROPERTIES view. The PROPERTY_NAME column contains the value "DEFAULT_TEMP_TABLESPACE" and the PROPERTY_VALUE column contains the default temporary tablespace name.

See Also:


Using Oracle-Managed Files

If you include the DB_CREATE_FILE_DEST or DB_CREATE_ONLINE_LOG_DEST_n initialization parameters in your initialization parameter file, you enable Oracle to create and manage the underlying operating system files of your database. Oracle will automatically create and manage the operating system files for the following database structures, dependent upon the initialization parameters you specify and how you specify clauses in your CREATE DATABASE statement:

Briefly, this is how the Oracle Managed Files feature works with the CREATE DATABASE statement presented earlier in this section and repeated here.

     UNDO TABLESPACE undotbs

Setting and Managing the Time Zone

Oracle9i enables you to set the time zone for your database using the SET TIME_ZONE clause of the CREATE DATABASE statement. This section provides information on the time zone files used to support this feature, specifically on Solaris platforms. Names of directories, filenames, and environment variables may differ for each platform but will probably be the same for all UNIX platforms.

The time zone files contain the valid time zone names and the following information is included for each zone (note that abbreviations are only used in conjunction with the zone names):

There are 2 time zone files under the Oracle installation directory:

To enable the use of the larger time zone data file, the DBA must do the following:

  1. Shutdown the database.

  2. Set the environment variable ORA_TZFILE to the full pathname of the location for the timezlrg.dat file.

  3. Restart the database.

Once the larger timezlrg.dat is used, it must continue to be used unless the user is sure that none of the nondefault zones are used for data that is stored in the database. Also, all databases that share information must use the same time zone data file.

To view the time zone names, use the following query:


Troubleshooting Database Creation

If for any reason database creation fails, shut down the instance and delete any files created by the CREATE DATABASE statement before you attempt to create it once again. After correcting the error that caused the failure of the database creation, try running the script again.

Dropping a Database

To drop a database, you must remove its datafiles, redo log files, and all other associated files (control files, initialization parameter files, archived log files). To view the names of the database's datafiles, redo log files, and control files, query the data dictionary views V$DATAFILE, V$LOGFILE, and V$CONTROLFILE, respectively.

If the database is in archive log mode, locate the archive log destinations by inspecting the initialization parameters LOG_ARCHIVE_DEST_n, or LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST.

If you used the Database Configuration Assistant to create your database, you can use that tool to delete your database and clean up the files.

See Also:

Oracle9i Database Reference for more information about these views and initialization parameters 

Considerations After Creating a Database

After you create a database, the instance is left running, and the database is open and available for normal database use. You may want to perform other actions, some of which are discussed in this section.

Some Security Considerations

A newly created database has least three users that are useful for administering your database: SYS, SYSTEM and OUTLN (owner of schema where stored outlines are stored).


To prevent unauthorized access and protect the integrity of your database, the default passwords for SYS and SYSTEM should be changed immediately after the database is created. 

Depending on the features and options installed, other users can also be present. Some of these users are:

To change the password for user DBSNMP refer to Oracle Intelligent Agent User's Guide.

Note Regarding Security Enhancements:

In this release of Oracle9i and in subsequent releases, several enhancements are being made to ensure the security of default database user accounts.

  • Beginning with this release, during initial installation with the Oracle Database Configuration Assistant (DCBA), all default database user accounts except SYS, SYSTEM, SCOTT, DBSNMP, OUTLN, AURORA$JIS$UTILITY$, AURORA$ORB$UNAUTHENTICATED and OSE$HTTP$ADMIN will be locked and expired. To activate a locked account, the DBA must manually unlock it and reassign it a new password.

  • In the next release of the database server, the Database Configuration Assistant will prompt for passwords for users SYS and SYSTEM during initial installation of the database rather than assigning default passwords to them. In addition, a CREATE DATABASE SQL statement issued manually will require you to specify passwords for these two users.

  • Oracle9i will be the last major release to support the user SYSTEM as a default database user created during any type of installation or by the CREATE DATABASE SQL statement.


See Also:


Installing Oracle's Sample Schemas

The Oracle server distribution media can include various SQL files that let you experiment with the system, learn SQL, or create additional tables, views, or synonyms.

Starting with Oracle9i, Oracle provides sample schemas that enable you to become familiar with Oracle functionality. Some Oracle documents and books use these sample schemas for presenting examples. There is an ongoing effort for most Oracle books to convert to the use of Sample Schemas based examples.

The following table briefly describes the sample schemas:

Schema  Description 

Human Resources 

The Human Resources (HR) schema is a basic relational database schema. There are six tables in the HR schema: Employees, Departments, Locations, Countries, Jobs, and Job_History. The Order Entry (OE) schema has links into HR schema 

Order Entry 

The Order Entry (OE) schema builds on the purely relational Human Relations (HR) schema with some object-relational and object-oriented features. The OE schema contains seven tables: Customers, Product_Descriptions, Product_Information, Order_Items, Orders, Inventories, and Warehouses. The OE schema has links into the HR schema and PM schema. This schema also has synonyms defined on HR objects to make access transparent to users.  

Product Media 

Product Media (PM) schema includes two tables, online_media and print_media, one object type, adheader_typ, and one nested table, textdoc_typ. The PM schema includes interMedia and LOB column types.

Note: To use interMedia Text you must create an interMedia Text index.  

Sales History 

The Sales History (SH) schema is an example of a relational star schema. It consists of one big range partitioned fact table sales and five dimension tables: times, promotions, channels, products and customers. The additional countries table linked to customers shows a simple snowflake.  

Queued Shipping 

The Queued Shipping (QS) schema is actually multiple schemas that contain message queues. 

Sample Schemas can be installed automatically for you by the Oracle Database Configuration Assistant or you can install it manually. The schemas and installation instructions are described in detail in Oracle9i Sample Schemas.

Initialization Parameters and Database Creation

Oracle has attempted to provide appropriate values in the starter initialization parameter file provided with your database software. You can edit these Oracle-supplied initialization parameters and add others, depending upon your configuration and options and how you plan to tune the database. For any relevant initialization parameters not specifically included in the initialization parameter file, Oracle supplies defaults.

If you are creating an Oracle database for the first time, it is suggested that you minimize the number of parameter values that you alter. As you become more familiar with your database and environment, you can dynamically tune many initialization parameters for the current instance with the ALTER SYSTEM statement. Later, you can choose to permanently add or change parameter values by updating them manually in the traditional text initialization parameter file. Or, you can create a binary server parameter file that enables you to use the ALTER SYSTEM statement to make initialization parameter changes that can persist across shutdown and startup. Both of these options are discussed in "Managing Initialization Parameters Using a Server Parameter File".

This section discusses some of the initialization parameters you may choose to add or edit before you create your new database.

The following topics are contained in this section:

Determining the Global Database Name

A database's global database name consists of the local database name that you assign and its location within a network structure. The DB_NAME initialization parameter determines the local name component of the database's name, while the DB_DOMAIN parameter indicates the domain (logical location) within a network structure. The combination of the settings for these two parameters must form a database name that is unique within a network.

For example, to create a database with a global database name of, edit the parameters of the new parameter file as follows:

DB_NAME = test

You can rename the GLOBAL_NAME of your database using the ALTER DATABASE RENAME GLOBAL_NAME statement, but you must also shut down and restart the database after changing the DB_NAME and DB_DOMAIN initialization parameters and re-creating the control file.

DB_NAME Initialization Parameter

DB_NAME must be set to a text string of no more than eight characters. During database creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and control file of the database. If during database instance startup the value of the DB_NAME parameter (in the parameter file) and the database name in the control file are not the same, the database does not start.

DB_DOMAIN Initialization Parameter

DB_DOMAIN is a text string that specifies the network domain where the database is created. This is typically the name of the organization that owns the database. If the database you are about to create will ever be part of a distributed database system, pay special attention to this initialization parameter before database creation.

See Also:

Part VI, "Distributed Database Management" for more information about distributed databases  

Specifying Control Files

Include the CONTROL_FILES initialization parameter in your new parameter file and set its value to a list of control filenames to use for the new database. When you execute the CREATE DATABASE statement, the control files listed in the CONTROL_FILES parameter will be created. If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a default operating system dependent filename.

If you want Oracle to create new operating system files when creating your database's control files, the filenames listed in the CONTROL_FILES parameter must not match any filenames that currently exist on your system. If you want Oracle to reuse or overwrite existing files when creating your database's control files, ensure that the filenames listed in the CONTROL_FILES parameter match the filenames that are to be reused.


Use extreme caution when setting this option. If you inadvertently specify a file that you did not intend and execute the CREATE DATABASE statement, the previous contents of that file will be overwritten. 

Oracle Corporation strongly recommends you use at least two control files stored on separate physical disk drives for each database.

See Also:

Chapter 6, "Managing Control Files" 

Specifying Database Block Sizes

The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used for the SYSTEM tablespace and by default in other tablespaces. Oracle can support up to 4 additional non-standard block sizes.

DB_BLOCK_SIZE Initialization Parameter

The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If not specified, the default data block size is operating system specific, and is generally adequate.

The block size cannot be changed after database creation, except by re-creating the database. If a database's block size is different from the operating system block size, make the database block size a multiple of the operating system's block size.

For example, if your operating system's block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter is valid:


In some cases, you may want to specify a block size larger than your operating system block size. A larger data block size provides greater efficiency in disk and memory I/O (access and storage of data). Such cases include the following scenarios:

Non-Standard Block Sizes

Tablespaces of non-standard block sizes can be created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE clause. These non-standard block sizes can have any power-of-two value between 2K and 32K: specifically, 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.

To use non-standard block sizes, you must configure sub-caches within the buffer cache area of the SGA memory for all of the non-standard block sizes that you intend to use. The initialization parameters used for configuring these sub-caches are described in the next section, "Setting Initialization Parameters that Affect the Size of the SGA".

The ability to specify multiple block sizes for your database is especially useful if you are transporting tablespaces between databases. You can, for example, transport a tablespace that uses a 4K block size from an OLTP environment to a datawarehouse environment that uses a standard block size of 8K.

See Also:


Setting Initialization Parameters that Affect the Size of the SGA

The initialization parameters discussed in this section affect the amount of memory that is allocated to the System Global Area. Except for the SGA_MAX_SIZE initialization parameter, they are dynamic parameters which values can be changed by the ALTER SYSTEM statement. The size of the SGA is dynamic, and can grow or shrink by dynamically altering these parameters.

See Also:


Setting the Buffer Cache Initialization Parameters

The buffer cache initialization parameters determine the size of the buffer cache component of the SGA. You use them to specify the sizes of caches for the various block sizes used by the database. These initialization parameters are all dynamic.

If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set. Oracle assigns an appropriate default value to the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size caches are configured.

The size of a buffer cache affects performance. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping.

DB_CACHE_SIZE Initialization Parameter

The DB_CACHE_SIZE initialization parameter replaces the DB_BLOCK_BUFFERS initialization parameter that was used in previous releases. The DB_CACHE_SIZE parameter specifies the size of the cache of standard block size buffers, where the standard block size is specified by DB_BLOCK_SIZE.

For backward compatibility the DB_BLOCK_BUFFERS parameter will still work, but it remains a static parameter and cannot be combined with any of the dynamic sizing parameters.

DB_nK_CACHE_SIZE Initialization Parameters

The sizes and numbers of non-standard block size buffers are specified by the following initialization parameters:

Each parameter specifies the size of the buffer cache for the corresponding block size. For example:



In the above example, the parameters specify that the standard block size of the database will be 4K. The size of the cache of standard block size buffers will be 12M. Additionally, 2K and 8K caches will be configured with sizes of 8M and 4M respectively.


These parameters cannot be used to size the cache for the standard block size. For example, if the value of DB_BLOCK_SIZE is 2K, it is illegal to set DB_2K_CACHE_SIZE. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE

Adjusting the Size of the Shared Pool

The SHARED_POOL_SIZE initialization parameter is a dynamic parameter (in previous releases it was not dynamic) that allows you to specify or adjust the size of the shared pool component of the SGA. Oracle selects an appropriate default value.

Adjusting the Size of the Large Pool

The LARGE_POOL_SIZE initialization parameter is a dynamic parameter (in previous releases it was not dynamic) that allows you to specify or adjust the size of the large pool component of the SGA. Oracle selects an appropriate default value.

Limiting the Size of the SGA

The SGA_MAX_SIZE initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance. You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, and large pool, but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified by SGA_MAX_SIZE.

If you do not specify SGA_MAX_SIZE, then Oracle selects a default value that is the sum of all components specified or defaulted at initialization time.

Specifying the Maximum Number of Processes

The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle concurrently. The value of this parameter must be 6 or greater (5 for the background processes plus 1 for each user process). For example, if you plan to have 50 concurrent users, set this parameter to at least 55.

Specifying the Method of Undo Space Management

Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo. Oracle allows you to store undo in an undo tablespace or in rollback segments.

See Also:

Chapter 13, "Managing Undo Space" 

UNDO_MANAGEMENT Initialization Parameter

The UNDO_MANAGEMENT initialization parameter determines whether an instance will start up in automatic undo management mode, where undo is stored in an undo tablespace, or rollback segment undo mode, where undo is stored in rollback segments. A value of AUTO enables automatic undo management mode, MANUAL enables rollback segment undo mode. For backward compatibility, the default is MANUAL.

UNDO_TABLESPACE Initialization Parameter

When the instance starts up in automatic undo management mode, it selects the first available undo tablespace in the instance for storing undo. A default undo tablespace named SYS_UNDOTBS is automatically created when you execute a CREATE DATABASE statement and the UNDO_MANAGEMENT initialization parameter is set to AUTO. This is the undo tablespace that Oracle will normally select whenever you start up the database.

Optionally, you can specify the UNDO_TABLESPACE initialization parameter. This causes the instance to use the undo tablespace specified by the parameter. The UNDO_TABLESPACE parameter can be used to assign a specific undo tablespace to an instance in an Oracle Real Application Clusters environment.

If there is no undo tablespace available, the instance will start, but uses the SYSTEM rollback segment. This is not recommended in normal circumstances, and an alert message is written to the alert file to warn that the system is running without an undo tablespace.

Oracle recommends using an undo tablespace rather than rollback segments. An undo tablespace is easier to administer and enables you to explicitly set an undo retention time.

ROLLBACK_SEGMENTS Initialization Parameter

The ROLLBACK_SEGMENTS parameter is a list of the non-system rollback segments an Oracle instance acquires at database startup if the database is to operate in rollback segment undo mode. List your rollback segments as the value of this parameter. If no rollback segments are specified, the system rollback segment is used.

The ROLLBACK_SEGMENTS initialization parameter is supported for backward compatibility. Oracle recommends using an undo tablespace rather than rollback segments.

Setting License Parameters

Oracle helps you ensure that your site complies with its Oracle license agreement. If your site is licensed by concurrent usage, you can track and limit the number of sessions concurrently connected to an instance. If your site is licensed by named users, you can limit the number of named users created in a database. To use this facility, you need to know which type of licensing agreement your site has and what the maximum number of sessions or named users is. Your site might use either type of licensing (session licensing or named user licensing), but not both.

The licenses initialization parameters are introduced here, but are discussed in greater detail in "Session and User Licensing".


You can set a limit on the number of concurrent sessions that can connect to a database. To set the maximum number of concurrent sessions for an instance, set the initialization parameter LICENSE_MAX_SESSIONS in the initialization parameter file that starts the instance, as shown in the following example:


In addition to setting a maximum number of sessions, you can set a warning limit on the number of concurrent sessions. Once this limit is reached, additional users can continue to connect (up to the maximum limit), but Oracle sends a warning to each connecting user. To set the warning limit for an instance, set the parameter LICENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICENSE_MAX_SESSIONS.

For running with Oracle Real Application Cluster instances, each instance can have its own concurrent usage limit and warning limit. However, the sum of the instances' limits must not exceed the site's session license.

LICENSE_MAX_USERS Initialization Parameter

You can set a limit on the number of users created in the database. Once this limit is reached, you cannot create more users.


This mechanism assumes that each person accessing the database has a unique user name and that no people share a user name. Therefore, so that named user licensing can help you ensure compliance with your Oracle license agreement, do not allow multiple users to log in using the same user name. 

To limit the number of users created in a database, set the LICENSE_MAX_USERS initialization parameter in the database's initialization parameter file, as shown in the following example:


For Oracle Real Application Cluster instances, all instances connected to the same database should have the same named user limit.

Managing Initialization Parameters Using a Server Parameter File

Oracle has traditionally stored initialization parameters in a text initialization parameter file. Starting with Oracle9i, you can choose to maintain initialization parameters in a binary server parameter file.

This section introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters. The following topics are contained in this section.

What is a Server Parameter File?

A server parameter file (SPFILE) can be thought of as a repository for initialization parameters that is maintained on the machine where the Oracle database server executes. It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. This eliminates the need to manually update initialization parameters to make changes effected by ALTER SYSTEM statements persistent. It also provides a basis for self tuning by the Oracle database server.

A server parameter file is initially built from a traditional text initialization parameter file using the CREATE SPFILE statement. It is a binary file that cannot be browsed or edited using a text editor. Oracle provides other interfaces for viewing and modifying parameter settings.


Although you can open the binary server parameter file with a text editor and view its text, do not manually edit it. Doing so will corrupt the file. You will not be able to start you instance, and if the instance is running, it could crash. 

At system startup, the default behavior of the STARTUP command is to read a server parameter file to obtain initialization parameter settings. The STARTUP command with no PFILE clause, reads the server parameter file from an operating system specific location. If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command. Explicit instructions for starting an instance using a server parameter file are contained in Starting Up a Database.

Migrating to a Server Parameter File

If you are currently using a traditional initialization parameter file, use the following steps to migrate to a server parameter file.

  1. If the initialization parameter file is located on a client machine, transfer the file (for example, FTP) from the client machine to the server machine.


    If you are using Oracle9i Real Application Clusters, you must combine all of your instance specific initialization parameter files into a single initialization parameter file. Instructions for doing this, and other actions unique to using a server parameter file for Oracle Real Application Cluster instances, are discussed in:

  2. Create a server parameter file using the CREATE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.

  3. Start up the instance using the newly created server parameter file.

Creating a Server Parameter File

The server parameter file must initially be created from a traditional text initialization parameter file. It must be created prior to its use in the STARTUP command. The CREATE SPFILE statement is used to create a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

The following example creates a server parameter file from initialization parameter file /u01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file is created in a platform-specific default location and is named spfile$ORACLE_SID.ora.

CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';

Another example, below, illustrates creating a server parameter file and supplying a name.

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
       FROM PFILE='/u01/oracle/dbs/test_init.ora';

The server parameter file is always created on the machine running the database server. If a server parameter file of the same name already exists on the server, it is overwritten with the new information.

Oracle recommends that you allow the database server to default the name and location of the server parameter file. This will ease administration of your database. For example, the STARTUP command assumes this default location to read the parameter file.

When the server parameter file is created from the initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the server parameter file. All other comments are ignored.

The CREATE SPFILE statement can be executed before or after instance startup. However, if the instance has been started using a server parameter file, an error is raised if you attempt to recreate the same server parameter file that is currently being used by the instance.


When you use the Database Configuration Assistant (DBCA) to create a database, it can automatically create a server parameter file for you. 

The SPFILE Initialization Parameter

The SPFILE initialization parameter contains the name of the current server parameter file. When the default server parameter file is used by the server (that is, you issue a STARTUP command and do not specify a PFILE), the value of SPFILE is internally set by the server. The SQL*Plus command SHOW PARAMETERS SPFILE (or any other method of querying the value of a parameter) displays the name of the server parameter file that is currently in use.

The SPFILE parameter can also be set in a traditional parameter file to indicate the server parameter file to use. You use the SPFILE parameter to specify a server parameter file located in a nondefault location. Do not use an IFILE initialization parameter within a traditional initialization parameter file to point to a server parameter file; instead, use the SPFILE parameter. See "Starting Up a Database" for details about:

Using ALTER SYSTEM to Change Initialization Parameter Values

The ALTER SYSTEM statement allows you to set, change, or delete (restore to default value) initialization parameter values. When the ALTER SYSTEM statement is used to alter a parameter setting in a traditional initialization parameter file, the change affects only the current instance, since there is no mechanism for automatically updating initialization parameters on disk. They must be manually updated in order to be passed to a future instance. Using a server parameter file overcomes this limitation.

Setting or Changing Initialization Parameter Values

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change as described in the following table:

SCOPE Clause  Description 


The change is applied in the server parameter file only. The effect is as follows:

  • For dynamic parameters, the change is effective at the next startup and is persistent.

  • For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.



The change is applied in memory only. The effect is as follows:

  • For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.

  • For static parameters, this specification is not allowed.



The change is applied in both the server parameter file and memory. The effect is as follows:

  • For dynamic parameters, the effect is immediate and persistent.

  • For static parameters, this specification is not allowed.


It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the server is not using a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a traditional initialization parameter file was used to start up the instance.

For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

A COMMENT clause allows a comment string to be associated with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

The following statement changes the maximum number of job queue processes allowed for the instance. It also specifies a comment, and explicitly states that the change is to be made only in memory (that is, it is not persistent across instance shutdown and startup).

                 COMMENT='temporary change on Nov 29'

Another example illustrates setting a complex initialization parameter that takes a list of strings. Specifically, the parameter value being set is the LOG_ARCHIVE_DEST_n initialization parameter. The case could be that either the parameter is being changed to a new value or a new archive destination is being added.

         COMMENT='Add new destimation on Nov 29'

Note that when a value consists of a list of strings, the syntax of the ALTER SYSTEM SET statement does not support editing each element of the list of values by the position or ordinal number. You must specify the complete list of values each time the parameter is updated and the new list completely replaces the old list.

Deleting Initialization Parameter Values

For initialization parameters whose values are string values you can restore a parameter to its default value (effectively deleting it), by using the following syntax:

ALTER SYSTEM SET parameter = '';

For numeric and boolean value parameters, you must specifically set the parameter back to its original default value.

Exporting the Server Parameter File

You can export a server parameter file to create a traditional text initialization parameter file. Reasons for doing this include:

The exported file can also be used to start up an instance using the PFILE option.

The CREATE PFILE statement is used to export a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement. The exported file is created on the database server machine. It contains any comments associated with the parameter in the same line as the parameter setting.

The following example creates a text initialization parameter file from the server parameter file:


Because no names were specified for the files, a platform-specific name is used for the initialization parameter file, and it is created from the platform-specific default server parameter file.

The following example creates a text initialization parameter file from a server parameter file where the names of the files are specified:

CREATE PFILE='/u01/oracle/dbs/test_init.ora'
       FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';

Errors and Recovery for the Server Parameter File

If an error occurs while reading the server parameter file (during startup or an export operation), or while writing the server parameter file during its creation, the operation terminates with an error reported to the user.

If an error occurs while reading or writing the server parameter file during a parameter update, the error is reported in the alert file and all subsequent parameter updates to the server parameter file are ignored. At this point, you have the following options:

Viewing Parameters Settings

You have several options for viewing parameter settings.

Method  Description 


This SQL*Plus command displays the currently in use parameter values. 


This SQL statement creates a text initialization parameter file from the binary server parameter file.  


This view displays the currently in effect parameter values. 


This view displays the currently in effect parameter values. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.  


This view displays the current contents of the server parameter file. The view returns NULL values if a server parameter file is not being used by the instance.  

See Also:

Oracle9i Database Reference for a complete description of views 

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index