0431-0434

Previous Table of Contents Next

Page 431


Parameter Name Description LOG_BUFFERS Specifies the size of the redo log buffer in the SGA into which all changes are recorded from all transactions on the instance. LOG_CHECKPOINT_INTERVAL Specifies whether a checkpoint should occur earlier than the default time of a redo log file switch. Generally, this should not be set for most systems, because more frequent checkpointing incurs extra system overhead by writing out the changed database blocks in the database buffer cache in the SGA. LOG_CHECKPOINT_TIMEOUT Controls the amount of time to wait before issuing a checkpoint on the database. If this parameter is set to 1,800 seconds (30 minutes), for example, a checkpoint is performed every 30 minutes, even if no activity has occurred on the system during that period. MAX_DUMP_FILE_SIZE Specifies the maximum size of any trace files generated. If you use the trace facility to trace large amounts of work, use this parameter to limit how much trace information can be produced. OPTIMIZER_MODE Specifies the default mode for the Oracle optimizer, which determines the execution plan for a SQL statement. The Oracle6 method is RULE, which means that the optimizer does not take into account of any statistics gathered on tables by using the ANALYZE command. The default value, CHOOSE, uses statistics if they exist. Otherwise, OPTIMIZER_MODE defaults to the rule based on the optimization method. OS_AUTHENT_PREFIX Specifies the proxy logon. The Oracle username defaults to the operating system username prefixed by OPS$. You can control the password by using this parameter. The default on most platforms is OPS$, but you can change it to any prefix or even NULL. PROCESSES Controls the maximum number of operating system processes that can connect to the Oracle database through this instance, which includes the background processes. Four background processes are mandatory, and the instance cannot start without them.
 continues 

Page 432

Table 19.1. continued


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 SID

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.

Creating a New Database

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.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net