Parameter Name | Description |
RESOURCE_LIMIT | Must be set to TRUE to enforce the checking of resource limits if system resources are controlled through database profiles assigned to Oracle users. To set this parameter online, use the ALTER SYSTEM command. |
ROLLBACK_SEGMENTS | Contains the names of private rollback segments that the instance should use for storing the before image of any changes made to the database. You still can activate rollback segments not mentioned here by using the ALTER ROLLBACK SEGMENT command. If no rollback segments are activated, the SYSTEM rollback segment ”normally reserved for recording changes made to the Oracle data dictionary ”is used for recording the before image of the Oracle data blocks. If more than one instance is using the Oracle database, those instances cannot use the same rollback segments. |
SEQUENCE_CACHE_ENTRIES | Specifies the number of sequence objects to cache in the SGA for generating unique numbers using sequences. The sequences must have been created with the CACHE parameter specified as part of the CREATE SEQUENCE statement. |
SHARED_POOL_SIZE | Specifies the size of the shared pool area in the SGA used to hold the data dictionary cache, cursors for SQL statements, and PL/SQL code units. |
SMALL_TABLE_THRESHOLD | Sets the low threshold for the data blocks in an Oracle table. Tables with Oracle data blocks below this limit are read into the SGA completely when you perform a full table scan on them. If a table has more data blocks than this limit, the full table access of the table uses only a portion of the lower part of the database buffers cache of the SGA. This ensures that active blocks in the SGA from other tables are not removed from memory when a full table scan is performed on a large table. |
SQL_TRACE | Turns on the trace facility for the instance when set to TRUE. With tracing on, execution plans and statement performance can be determined by formatting the trace files produced with the TKPROF utility. Timing information is provided only if the TIMED_STATISTICS parameter also is set to TRUE. |
Page 433
Parameter Name | Description |
TIMED_STATISTICS | Determines whether timing information is recorded in the trace files when tracing is enabled for the instance or for user sessions. |
USER_DUMP_DEST | Determines the location of user process trace files produced when tracing is enabled for the instance or for user sessions. |
Table 19.1 does not describe all the parameters in the INIT.ORA file. Refer to Chapter 1 of the Oracle7 Server Reference Manual for descriptions of all the parameters.
TIP |
Use the IFILE parameter to name a file that contains a common set of parameters for all the instances running on the machine. |
To see the parameters the current instance is using, enter the following in the SQL Worksheet of Enterprise Manager:
SHOW PARAMETERS
You also can see only those parameters that contain a given string. This code shows all the parameters that contain the string buffer, for example:
SHOW PARAMETERS buffer
To see a list of the parameters when the instance first comes up, include the word LIST in the first line of the INIT.ORA file.
To access the parameters from any Oracle tool, run a SELECT operation against the v$parameter table, as in this example:
SQLPLUS> SELECT * FROM v$parameter;
You must have access to the v$parameter table, which is owned by the Oracle user SYS.
The Oracle system identifier (SID) identifies the Oracle instance on the machine. It usually is set up as an operating system variable ” the $ORACLE_SID environment variable in UNIX or the ORA_SID symbol in VMS ”that is used to name the Oracle background processes and to identify the SGA area in memory. When a user process wants to connect to a database, the SID set up in that operating system account determines to which instance and therefore to
Page 434
which database the user will connect. The same username can exist in different databases. The Oracle system username SYS exists in all Oracle databases, for example. With the SID, you can connect to the correct instance.
Many instances might be running on the same machine, each doing the processing for a different database. By looking at the processes running on the machine, you see many occurrences of the DBWR (database writer) background process. The name of the process usually has the SID somewhere in it.
For client/server setups, the client machine identifies the network protocol and machine address to which the client machine wants to attach, as well as the name of the SID of the connection. Requests then are processed against the database for which the instance is processing.
When you create a database, you create files on a disk that Oracle has formatted and prepared. The structure of the database does not have to remain the same as the initial structure. You can add other files later.
On some platforms, you can create a new database when the Oracle software is installed. To create a database manually after that, follow the steps in this section. This section assumes that the software has been installed correctly on your machine. The steps provided here are general steps that apply to most platforms. Creating a database involves operating system-specific steps, of course. Refer to the Oracle8 Installation and User's Guide for specific information on your platform.
The crux of creating a database lies in running the CREATE DATABASE SQL statement. You must perform other steps, however, before you can run it. The syntax of the CREATE DATABASE SQL statement follows :
CREATE DATABASE mydb LOGFILE file1, file2 SIZE nM MAXLOGFILES n MAXLOGMEMBERS n MAXLOGHISTORY n DATAFILE file1 SIZE nM MAXDATAFILES n MAXINSTANCES n ARCHIVELOG NOARCHIVELOG
For example, the following statement shows how to create a new database:
CREATE DATABASE mydb DATAFILE `/disk03/mydb/system1.dbf' SIZE 20M LOGFILE `/disk04/mydb/log1.log', `disk05/mydb/log2.log' SIZE 512K;
The DATAFILE parameter will physically create the first database file on the disk specified and with the size specified. If the file already exists, the command fails (unless the REUSE option is specified as well, in which case the file is overwritten). The same is true with the LOGFILE parameter, which creates the first two redo log files.