java oracle big5_List of all Oracle Server Parameters

List of all Oracle Server Parameters

for Oracle 9i and 10g, Green = New in 10g

(//z 2012-5-11 14:07:27 PM is2120@csdn)

Grey = Valid in 9i but dropped (or hidden) in 10g

Bold = Static Parameter - change in Pfile/SPfile

Normal= Dynamic Parameter - change in Pfile/SPfile/ALTER SYSTEM/SESSION

PARAMETER DESCRIPTION

------------------------------ ----------------------------------------

ACTIVE_INSTANCE_COUNT = int Active instances in the cluster

AQ_TM_PROCESSES = int Number of AQ Time Managers to start

ARCHIVE_LAG_TARGET =int Max no. seconds of redos the standby could lose

asm_diskgroups = string Disk groups to mount automatically

asm_diskstring = string Disk set locations for discovery

asm_power_limit = int Number of processes for disk rebalancing

AUDIT_FILE_DEST ='directory' Directory in which auditing files are to resideAUDIT_SYS_OPERATIONS= {TRUE|FALSE}AUDIT_TRAIL= {NONE | FALSE | DB | TRUE | OS} Enable system auditing 9i

AUDIT_TRAIL= {NONE | DB | DB_EXTENDED| OS}Enable system auditing 10g

BACKGROUND_CORE_DUMP= {PARTIAL | FULL}

BACKGROUND_DUMP_DEST = 'path or directory'

BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} DEFERRED

BITMAP_MERGE_AREA_SIZE= int Memory for BITMAP MERGE

BLANK_TRIMMING = {TRUE|FALSE}

CIRCUITS= int

CLUSTER_DATABASE = {TRUE|FALSE} If TRUE startup in cluster database modeCLUSTER_DATABASE_INSTANCES = int

CLUSTER_INTERCONNECTS = ipaddr [:ipaddr…] Interconnects for RAC use COMMIT_POINT_STRENGTH= int

COMPATIBLE = release_number [CHAR: 9.2.0.0.0]

CONSTRAINT[S] = { IMMEDIATE | DEFERRED | DEFAULT }

CONTROL_FILE_RECORD_KEEP_TIME = int Time in Days

CONTROL_FILES=filename [,filename […] ]

CORE_DUMP_DEST = 'text'

CPU_COUNT = int

CREATE_BITMAP_AREA_SIZE= int

CREATE_STORED_OUTLINES = {TRUE | FALSE | 'category_name' } [NOOVERRIDE]

CURSOR_SHARING = {SIMILAR | EXACT | FORCE}

CURSOR_SPACE_FOR_TIME= {TRUE|FALSE}

CURRENT_SCHEMA = schema Change the current schema of the session

DB_2k_cache_size = intbytes Size of cache for 2K buffers

DB_4k_cache_size = intbytes Size of cache for 4K buffers

DB_8k_cache_size = intbytes Size of cache for 8K buffers

DB_16k_cache_size = intbytes Size of cache for 16K buffers

DB_32k_cache_size = intbytes Size of cache for 32K buffers

DB_BLOCK_BUFFERS= int Deprecated in favour of DB_CACHE_ SIZE

DB_BLOCK_CHECKING = {TRUE | FALSE} DEFERRED

DB_BLOCK_CHECKSUM = {TRUE | FALSE}

DB_BLOCK_SIZE= int [bytes] Do not alter after db creation

DB_CACHE_ADVICE= {ON | READY | OFF}

DB_CACHE_SIZE = int [bytes]

DB_CREATE_FILE_DEST = directory

DB_CREATE_ONLINE_LOG_DEST_n = directory (where n = 1-5)

Default locn for Oracle-managed control files and online redo logs.

DB_DOMAIN= domain_name Directory part of global database name

DB_FILE_MULTIBLOCK_READ_COUNT = int

DB_FILE_NAME_CONVERT= [(]'dbfile1' , 'dbfile2'…[)]

Datafile name convert patterns and strings

for standby/clone db [old string, new string]

DB_FILES= int

db_flashback_retention_target = int Max Flashback Database log retention (minutes)

DB_KEEP_CACHE_SIZE = int [bytes]

DB_NAME = database_name

db_recovery_file_dest = string Default database recovery file location

db_recovery_file_dest_size = int Database recovery files size limit

DB_RECYCLE_CACHE_SIZE = int [bytes]

db_unique_name= string Database Unique Name

DB_WRITER_PROCESSES= int Number of background database writer

processes to start

DBLINK_ENCRYPT_LOGIN= {TRUE|FALSE} Enforce password encryption for distributed login

DBWR_IO_SLAVES= int

DDL_WAIT_FOR_LOCKS = {TRUE|FALSE} Disable NOWAIT DML lock acquisitions

DG_BROKER_CONFIG_FILEn = filename (where n = 1 or 2)

DG_BROKER_START = {TRUE|FALSE}

DISK_ASYNCH_IO= {TRUE|FALSE}

DISPATCHERS = 'dispatch_clause' (see SQL ref manual for detail)(MTS_Dispatchers in Ora 8/9)

DISTRIBUTED_LOCK_TIMEOUT= int

DML_LOCKS= int One for each table modified in a transaction

DRS_START = {TRUE|FALSE} Start DG Broker monitor (DMON process)

ERROR_ON_OVERLAP_TIME = {TRUE | FALSE}

ENQUEUE_RESOURCES= int Resources for enqueues

EVENT= debug_string Debug event control

FAL_CLIENT = string Fetch archive log Client

FAL_SERVER = string Fetch archive log Server

FAST_START_IO_TARGET = int Upper bound on recovery reads(Deprecated)

FAST_START_MTTR_TARGET = int

FAST_START_PARALLEL_ROLLBACK = {FALSE | LOW | HIGH}

Max number of parallel recovery slaves

FILE_MAPPING = {TRUE|FALSE}

FILEIO_NETWORK_ADAPTERS = char Network Adapters for File I/O

FILESYSTEMIO_OPTIONS = {none | setall | directIO | asynch}

FIXED_DATE = {'YYYY_MM_DD_HH24_MI-SS' | 'date in default format'}

Fix SYSDATE value for debugging

GC_FILES_TO_LOCKS= '{file_list=lock_count[!blocks][EACH][:…]}'

RAC/OPS - lock granularity number of

global cache locks per file (DFS)

GCS_SERVER_PROCESSES = int Number of background gcs server processes to start

GLOBAL_CONTEXT_POOL_SIZE= {1 MB | int MB}

GLOBAL_NAMES = {TRUE | FALSE} Enforce that database links have same

name as remote database

HASH_AREA_SIZE = int Size of in-memory hash work area (Shared Server)

HASH_JOIN_ENABLED = {TRUE|FALSE}

HI_SHARED_MEMORY_ADDRESS = int SGA starting address (high order 32-bits

on 64-bit platforms)

HS_AUTOREGISTER = {TRUE | FALSE} Enable automatic server DD updates in HS

agent self-registration

IFILE= parameter_file_name Include file in init.ora

INSTANCE = int Connect to a different RAC instanceINSTANCE_GROUPS = group_name [,group_name … ]

INSTANCE_NAME=instance_id

INSTANCE_NUMBER= int

INSTANCE_TYPE = {RDBMS|ASM} Type of instance to be executed

RDBMS or Automated Storage Management

ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED}

JAVA_MAX_SESSIONSPACE_SIZE= int[bytes]

JAVA_POOL_SIZE = int [bytes]JAVA_SOFT_SESSIONSPACE_LIMIT= int

JOB_QUEUE_PROCESSES = int

LARGE_POOL_SIZE = int [bytes]

LICENSE_MAX_SESSIONS = int Maximum number of non-system user sessions

(concurrent licensing)

LICENSE_MAX_USERS = int Maximum number of named users that can be created

(named user licensing)

LICENSE_SESSIONS_WARNING = int Warning level for number of non-system

user sessions

LOCAL_LISTENER = network_name Define which listeners instances register with

LOCK_NAME_SPACE = namespace Used for generating lock names for standby/primary database

assign each a unique name spaceLOCK_SGA= {TRUE | FALSE}

LOG_ARCHIVE_CONFIG = [SEND|NOSEND] [RECEIVE|NORECEIVE] [ DG_CONFIG]

LOG_ARCHIVE_DEST = string

LOG_ARCHIVE_DEST_n = {null_string |

{LOCATION=local_pathname | SERVICE=tnsnames_service}

[MANDATORY | OPTIONAL] [REOPEN[=integer]]}

LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER} (n = 1-10)

LOG_ARCHIVE_DUPLEX_DEST = string

LOG_ARCHIVE_FORMAT= string [CHAR: "MyApp%S.ARC"]

LOG_ARCHIVE_LOCAL_FIRST = {TRUE|FALSE} Establish EXPEDITE attribute default value

LOG_ARCHIVE_MAX_PROCESSES = int

LOG_ARCHIVE_MIN_SUCCEED_DEST = int Minimum number of archive destinations

that must succeed

LOG_ARCHIVE_START= {TRUE | FALSE}

LOG_ARCHIVE_TRACE = int Tracing level for Archive logs

LOG_BUFFER = int bytes Redo circular buffer size

LOG_CHECKPOINT_INTERVAL = int Checkpoint threshold, # redo blocks

LOG_CHECKPOINT_TIMEOUT = int Checkpoint threshold, maximum time interval between

checkpoints in seconds

LOG_CHECKPOINTS_TO_ALERT = {TRUE|FALSE} Log checkpoint begin/end to alert file

LOG_FILE_NAME_CONVERT = ['old string','new string']

Convert patterns/strings for standby/clone db

LOG_PARALLELISM = int Number of log buffer strands

LOGMNR_MAX_PERSISTENT_SESSIONS = int Maximum no of threads to mine

MAX_DISPATCHERS = int Max number of dispatchers

MAX_DUMP_FILE_SIZE = {size bytes|UNLIMITED} [DEFERRED]

MAX_ENABLED_ROLES = int Max number of roles a user can have enabled

MAX_ROLLBACK_SEGMENTS= int Max number of rollback segments in SGA cache

MAX_SHARED_SERVERS = int Max number of shared servers]

mts_circuits = int Max number of circuits (10g see CIRCUITS)

mts_dispatchers Specifications of dispatchers (10g see DISPATCHERS)

MTS_LISTENER_ADDRESS Address(es) of network listener [CHAR]

mts_max_dispatchers Max number of dispatchers (10g see MAX_DISPATCHERS)

mts_max_servers Max number of shared servers (10g see MAX_SHARED_SERVERS)

MTS_MULTIPLE_LISTENERS = {TRUE|FALSE} Are multiple listeners enabled?

MTS_SERVERS = int Number of shared servers to start up [NUMBER]

mts_service = string Service supported by dispatchers [CHAR]

mts_sessions = int max number of shared server sessions [NUMBER]

nls_calendar ='string' NLS calendar system name (Default=GREGORIAN)

nls_comp = {BINARY | ANSI} NLS comparison, Enterprise Edition

nls_currency ='string' NLS local currency symbol

nls_date_format ='format' NLS Oracle date format

nls_date_language =language NLS date language name (Default=AMERICAN)]

nls_dual_currency = currency_symbol

nls_iso_currency =territory Override the default set by NLS_TERRITORY

nls_language =language NLS language name (session default)

nls_length_semantics = {BYTE|CHAR}} Default when creating new columns

nls_nchar_conv_excp = {TRUE|FALSE} Raise an exception instead of

allowing an implicit conversion

nls_numeric_characters ="decimal_character group_separator"

nls_sort = {BINARY |linguistic_def} Case-sensitive or insensitive sort

linguistic_def may be BINARY, BINARY_CI, BINARY_AI,

GERMAN, GERMAN_CI, etc

nls_territory =territory Territory name (country settings)

nls_time_format =time_format Time format

nls_time_tz_format = time_format Time with timezone format

nls_timestamp_format = time_format Timestamp format

nls_timestamp_tz_format = time_format Timestamp with timezone format

O7_DICTIONARY_ACCESSIBILITY = {TRUE | FALSE} Allow Dictionary Access (as in Ora V7 )

OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED Space for application objects Max

OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED Space for application objects Min

OLAP_PAGE_POOL_SIZE =intbytes

OPEN_CURSORS = int Max # cursors per session

OPEN_LINKS = int Max # open links per Session

OPEN_LINKS_PER_INSTANCE = int Max # open links per instance

OPTIMIZER_DYNAMIC_SAMPLING = int

OPTIMIZER_FEATURES_ENABLE = {8.0.0|8.0.3|8.0.4|8.0.5|8.0.6|8.0.7|8.1.0|8.1.3|8.1.4|8.1.5|8.1.6|8.1.7|9.0.0|9.0.1|9.2.0}

Configure qry optimiser based on an Oracle release No.

OPTIMIZER_INDEX_CACHING = int Percent to cache (favour nested loop joins & IN-list)

OPTIMIZER_INDEX_COST_ADJ = int Adjust the cost of index vs FTS

OPTIMIZER_MAX_PERMUTATIONS = int Max join permutations per qry block

OPTIMIZER_MODE = [RULE | CHOOSE | FIRST_ROWS | ALL_ROWS]

oracle_trace_collection_name =collection Name for use by Oracle TRACE oracle_trace_collection_path =path Path to .cdf & .dat files (ORACLE_HOME/otrace/admin/cdf)

oracle_trace_collection_size =int bytes Max trace file size

oracle_trace_enable = {TRUE|FALSE} Enable Oracle Trace

oracle_trace_facility_name ={ORACLED | ORACLEE | ORACLESM | ORACLEC} TRACE event set

oracle_trace_facility_path =path TRACE definition files: ORACLE_HOME/otrace/admin/fdf/

OS_AUTHENT_PREFIX = prefix Prefix for auto-logon accounts [string]

OS_ROLES = {TRUE|FALSE} Retrieve roles from the operating system

PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE} Tune degree of parallelism

PARALLEL_AUTOMATIC_TUNING = {TRUE|FALSE} Automatic tuning

PARALLEL_EXECUTION_MESSAGE_SIZE = int bytes Message buffer size

PARALLEL_INSTANCE_GROUP = 'group' RAC: Limit instances used

PARALLEL_MAX_SERVERS = int

PARALLEL_MIN_PERCENT = int Min percent of threads required for

parallel query

PARALLEL_MIN_SERVERS = int

PARALLEL_SERVER = [TRUE | FALSE] Startup in parallel server mode

PARALLEL_SERVER_instances = int No. of instances (used for sizing SGA)

PARALLEL_THREADS_PER_CPU = int

PARTITION_VIEW_ENABLED = {TRUE|FALSE} Deprecated (use partition TABLES)

PGA_AGGREGATE_TARGET = int bytes Automatically size the SQL working area

plsql_code_type ={INTERPRETED | NATIVE} Code-type

PLSQL_COMPILER_FLAGS = { [DEBUG | NON_DEBUG] [INTERPRETED | NATIVE] }

plsql_debug ={TRUE | FALSE}

plsql_native_c_compiler

plsql_native_library_dir = ['Path_to_directory']

plsql_native_library_subdir_count = int

plsql_native_linker=path Path to linker

plsql_native_make_file_name =path Pathname of make file

plsql_native_make_utility =path Pathname of make utility

plsql_optimize_level Optimize level

PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} [DEFERRED]

plsql_warnings =string Compiler warnings settings

See also DBMS_WARNING and DBA_PLSQL_OBJECT_SETTINGS

PRE_PAGE_SGA = {TRUE|FALSE} Pre-page sga for process

PROCESSES = int User processes

QUERY_REWRITE_ENABLED = {FORCE | TRUE | FALSE} [DEFERRED | NOOVERRIDE]

QUERY_REWRITE_INTEGRITY = {ENFORCED | TRUSTED | STALE_TOLERATED}

RDBMS_SERVER_DN = Distinguished Name

READ_ONLY_OPEN_DELAYED = {TRUE | FALSE} Delay opening read_only files until first access

RECOVERY_PARALLELISM = int Server processes to use for parallel recovery

REMOTE_ARCHIVE_ENABLE = [RECEIVE[,SEND] | FALSE | TRUE]

Enable or disable sending archived redo logs to/from remote destinations

REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}

Remote-procedure-call dependencies mode

REMOTE_LISTENER =network_name

REMOTE_LOGIN_PASSWORDFILE ={NONE | SHARED | EXCLUSIVE} Use a password file

REMOTE_OS_AUTHENT = {TRUE | FALSE} Allow non-secure remote clients to use

auto-logon accounts

REMOTE_OS_ROLES = {TRUE | FALSE} Allow non-secure remote clients to use os roles

REPLICATION_DEPENDENCY_TRACKING = {TRUE | FALSE}

RESOURCE_LIMIT = {TRUE | FALSE} Master switch for resource limit

RESOURCE_MANAGER_PLAN = plan_name Turn on Resource Manager plan

resumable_timeout =seconds Set resumable_timeout

ROLLBACK_SEGMENTS = (rbs1 [, rbs2] … )

ROW_LOCKING = [ALWAYS | DEFAULT | INTENT]

SERIAL_REUSE = {DISABLE | SELECT | DML | PLSQL | ALL} Cursor memmory management

SERVICE_NAMES = db_service_name [,db_service_name [,…] ]

SESSION_CACHED_CURSORS = int Session cursors to cache

SESSION_MAX_OPEN_FILES = int Max no. of BFiles (LOB) each session can open

SESSIONS = int Max no. of user and system sessions

SGA_MAX_SIZEsga_target = int bytes Target size of SGA

SHADOW_CORE_DUMP = {PARTIAL | FULL | NONE} Include SGA in core file

SHARED_MEMORY_ADDRESS = int SGA starting address (platform specific)

SHARED_POOL_RESERVED_SIZE = int bytes Reserved area of shared pool

SHARED_POOL_SIZE =int Size of shared pool

SHARED_SERVERS = int Number of shared servers to start up (MTS)

SHARED_SERVER_SESSIONS = int Max number of shared server sessions

SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}

smtp_out_server = server_clause utl_smtp server and port configuration parameter

SORT_AREA_RETAINED_SIZE =SPFILE =spfile_name Parameter file

sp_name =name Service Provider Name

SQL92_SECURITY = {TRUE | FALSE} Require select privilege for update/delete

SQL_TRACE = {TRUE | FALSE} Enable SQL trace

sqltune_category =category Qualifier for applying hintsets

SQL_VERSION =version Sql language version, for compatibility

STANDBY_ARCHIVE_DEST = 'filespec' Standby database archivelog destination

STANDBY_FILE_MANAGEMENT = {MANUAL | AUTO}

Automate file mmanagement on standby DB

STAR_TRANSFORMATION_ENABLED = {TEMP_DISABLE | TRUE | FALSE}

STATISTICS_LEVEL = {ALL | TYPICAL | BASIC} Collect Statistics

streams_pool_size = TAPE_ASYNCH_IO = {TRUE | FALSE} Allow I/O requests to tape devices at the same time as CPU processing

THREAD =int Redo thread to use (RAC)

TIMED_OS_STATISTICS = int Gather OS statistics every x seconds

TIMED_STATISTICS = {TRUE | FALSE} Collect time statistics

TIME_ZONE = '[+ | -] hh:mm'| LOCAL | DBTIMEZONE | 'time_zone_region'

TRACE_ENABLED = {TRUE | FALSE} Trace execution path (Internal use only-Oracle support services)

TRACEFILE_IDENTIFIER = "traceid" Trace file custom identifier

TRANSACTION_AUDITING = {TRUE | FALSE} [DEFERRED]

TRANSACTIONS = int Max. number of concurrent active transactions

TRANSACTIONS_PER_ROLLBACK_SEGMENT = int

UNDO_MANAGEMENT = {MANUAL | AUTO} Undo space management mode (Manual=rollback segs)

UNDO_RETENTION = int Undo retention in second

UNDO_SUPPRESS_ERRORS = {TRUE |FALSE} Suppress RBU errors in SMU mode

UNDO_TABLESPACE =undoname Select an undo tablespace

USE_INDIRECT_DATA_BUFFERS = {TRUE|FALSE} Configure SGA Memory cache for >4Gb RAM

USE_PRIVATE_OUTLINES = {TRUE |FALSE |category_name }

USE_STORED_OUTLINES = { TRUE |FALSE |category_name} [NOOVERRIDE]

USER_DUMP_DEST = 'directory_name' User process dump directory

UTL_FILE_DIR Utl_file accessible directories list

UTL_FILE_DIR ='Path1', 'Path2'..

or

UTL_FILE_DIR ='Path1' # Must be

UTL_FILE_DIR ='Path2' # consecutive entries

WORKAREA_SIZE_POLICY = {AUTO | MANUAL} Policy used to size SQL working areas

Session & other Parameters

The following parameters are not initialization parameters:

CONSTRAINT, CREATE_STORED_OUTLINES, CURRENT_SCHEMA, ERROR_ON_OVERLAP_TIME, FLAGGER, INSTANCE, ISOLATION_LEVEL , SQL_TRACE?, SKIP_UNUSABLE_INDEXES, TIME_ZONE, USE_PRIVATE_OUTLINES, USE_STORED_OUTLINES.

You cannot set values for these in the parameter file (pfile/spfile)

SQL_TRACE is an initialization parameter, but when changed in a user session, does not update V$PARAMETER.

TRANSACTIONS can be changed in the parameter file or using ALTER SYSTEM…SCOPE=SPFILE

(//z 2012-5-11 14:07:27 PM is2120@csdn)

NotesAll Byte values can also be specified in K or M or G

e.g. you can enter 8388608 or 8192 K or 8M

All directory paths follow standard notation i.e UNIX 'quotes' or Windows "doublequotes"

The default value for many of these parameters does vary across Operating System platforms.

New parameters in 9.2

audit_sys_operations, dg_broker_start, dg_broker_config_file_n, file_mapping, filesystem_io_options, log_parallelism, olap_page_pool_size, optimizer_dynamic_sampling, statistics_level.

New parameters in 10G

asm_diskgroups, asm_diskstring, asm_power_limit, db_flashback_retention_target, db_recovery_file_dest, db_recovery_file_dest_size, db_unique_name, ddl_wait_for_locks, fileio_network_adapters, gcs_server_processes, instance_type, ldap_directory_access, log_archive_config,

log_archive_local_first, plsql_code_type, plsql_debug, plsql_optimize_level, plsql_warnings, resumable_timeout, sga_target, smtp_out_server, sp_name, sqltune_category, streams_pool_size.

New parameters in 11G

asm_preferred_read_failure_groups,client_result_cache_lag,client_result_cache_size,commit_logging,commit_wait,control_management_pack_access,db_lost_write_protect,db_securefile,db_ultra_safe,ddl_lock_timeout,diagnostic_dest,global_txn_processes,java_jit_enabled,ldap_directory_sysauth,memory_max_target,memory_target,optimizer_capture_sql_plan_baselines,optimizer_use_invisible_indexes,optimizer_use_pending_statistics,optimizer_use_sql_plan_baselines,parallel_io_cap_enabled,plscope_settings,redo_transport_user,resource_manager_cpu_allocation,result_cache_max_result,result_cache_max_size,

result_cache_mode,result_cache_remote_expiration,sec_case_sensitive_logon,sec_max_failed_login_attempts,sec_protocol_error_further_action,sec_protocol_error_trace_action,sec_return_server_release_banner,xml_db_events

(//z 2012-5-11 14:07:27 PM is2120@csdn)