example init.ora file

To view hidden parameters:
SELECT KSPPINM name,KSPPDESC description
FROM X$KSPPI WHERE substr(KSPPINM,1,1) = '_';

WARNING - These are unsupported, do not use on a production database.
Some hidden parameters are listed at the bottom of the list below.


COMPATIBLE
Default: Current release
Values: 7.0.0 to current release
Allows the use of a new release while maintaining backward compatibility with an earlier release. It may cause some functions on the current relase to be restricted.

CONTROL_FILES
Values:1 to 8 filenames.

LOG_BUFFER
Default: Op sys dependent
Specifies the amount of memory, in bytes, used to buffer redo entries to a redo log file.In general large values reduce redo log file I/O. In a busy system set to 65536 or higher.

MAX_ROLLBACK_SEGMENTS
Default 30, Values 2 to 65535.
Limits the max number of rollback segments allowed to be kept online by one instance.

NLS_DATE_FORMAT
NLS_DATE_FORMAT is set at database creation (select * from nls_database_parameters;). If it is not set to DD-MON-RR then problems may be found when inserting dates, an insert of a 6 figure date such as '01-01-99' will result in a date when queried back of '01-01-2099' instead of '01-01-1999'. This can be got around by either recreating the database or setting the init.ora parameter NLS_DATE_FORMAT = DD-MON-RR and setting the parameter NLS_DATE_FORMAT on the client (either the pc or the server (.profile)). One of these options will not work on thier own.

OPEN_CURSORS
Default: 50 Values: 1 to Op sys dependent
Sets max number of cursors that a session can have open at any one time. If a session does not open this number of cursors no overhead is added.

OPTIMIZER_MODE
Default: CHOOSE Values: RULE, CHOOSE, FIRST_ROWS or ALL_ROWS.

OS_AUTHENT_PREFIX
Default:OPS$
Prefix concatenated to the beginning of a users operating system username for users connecting to Oracle. If an Oracle account setup as IDENTIFIED EXTERNALLY exists as this then the user can log in with no password.

REMOTE_LOGIN_PASSWORDFILE
Default:NONE Values: NONE, SHARED or EXCLUSIVE
Specifies whether Oracle checks for a password file and how many databases can use the password file.When NONE, Oracle ignores any password file (therefore privileged users must be authenticated by the op system). When EXCLUSIVE, the password file can only be used for one database and it can contain names other than SYS and INTERNAL. When it is set to SHARED it can be used by more than one database but it can only contain the users SYS and INTERNAL.

ROLLBACK_SEGMENTS
Specifies rollback segments to acquire at instance startup. If this is not set PUBLIC rollback segments are acquired at startup. The parameters TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT are used to calculate the minimum number of rollback segments brought online.

SHARED_POOL_RESERVED_SIZE
Default:SHARED_POOL_SIZE * 0.5
Specifies portion of shared pool to be reserved for large contiguous requests for memory.

SHARED_POOL_SIZE Default: 300 Kbytes to op sys dependent Values: 3500000
Sets size of shared pool in bytes. Can use bytes or specify K or M.

SORT_AREA_RETAINED_SIZE
Default: SORT_AREA_SIZE Values: DB_BLOCK_SIZE to SORT_AREA_SIZE
Sets max amount, in bytes, of user memory retained after a sort run

SORT_AREA_SIZE
Default: Op sys dependent Values: 0 to op sys dependant
Specifies the max amount, in bytes, of the PGA memory used for a sort. If MTS is enabled this is in the SGA. Following completion of the sort (prior to fetching) the memory is released down to the size of SORT_AREA_RETAINED_SIZE. After fetching all memory is released to the PGA (not the Operating system).

SQL_TRACE
Default: False Values: TRUE or FALSE
Disables or enables SQL Trace facility, value can also be changed by using DBMS_SYSTEM built-in package.

TRANSACTIONS
Default: 1.1 * SESSIONS Values: Op sys dependent
Max number of concurrent transactions, greater values increase size of SGA.

TRANSACTIONS_PER_ROLLBACK_SEGMENT
Default: 21 Values: 1 to op system dependent
Sets the number of concurrent transactions specified per rollback segment.

UTL_FILE_DIR
Specifies a directory permitted for PL/SQL file I/O. Each directory requires a separate parameter. All occurences of this parameter must be next to each other in the init.ora file. All users can read or write to all files in these directories.


_TRACE_FILES_PUBLIC
This makes trace files readable by all, not just the DBA group. It is useful for development databases where you want developers to make extensive use of TKPROF & SQL_TRACE.