Parameters are used to adjust memory strucutures and optimise performance, set global defaults, limitations and physical naming.
| Name | Description |
|---|---|
| always_anti_join | Always use this anti-join when possible |
| always_semi_join | Always use this semi-join when possible |
| aq_tm_processes | Number of AQ Time Managers to start |
| audit_trail | Enable system auditing |
| background_dump_dest | Detached process dump directory |
| backup_tape_io_slaves | BACKUP Tape I/O slaves |
| bitmap_merge_area_size | Maximum memory allow for BITMAP MERGE |
| blank_trimming | Blank trimming semantics parameter |
| buffer_pool_keep | Number of database blocks/latches in keep buffer pool |
| buffer_pool_recycle | Number of database blocks/latches in recycle buffer pool |
| commit_point_strength | Bias this node has toward not preparing in a two-phase commit |
| compatible | Database will be completely compatible with this software version |
| control_file_record_keep_time | Control file record keep time in days |
| control_files | Control file names list |
| cpu_count | Number of cpu's for this instance |
| create_bitmap_area_size | Size of create bitmap buffer for bitmap index |
| cursor_space_for_time | Use more memory in order to get faster execution |
| db_block_buffers | Number of database blocks cached in memory |
| db_block_checking | Data and index block checking |
| db_block_checksum | Store checksum in db blocks and check during reads, forces Oracle to use checksums to verify data block contents. Setting DB_BLOCK_CHECKSUM = TRUE does cause a performance overhead. |
| db_block_lru_latches | Number of lru latches |
| db_block_max_dirty_target | Upper bound on modified buffers/recovery reads |
| db_block_size | Size of database block in bytes |
| db_domain | Directory part of global database name stored with CREATE DATABASE command |
| db_file_direct_io_count | Sequential I/O block count |
| db_file_multiblock_read_count | Database block to be read each IO |
| db_file_name_convert | Datafile name convert pattern and string for standby/clone database |
| db_files | Maximum allowable number of db files |
| db_name | Database name specified in CREATE DATABASE |
| db_writer_processes | Number of background database writer processes to start |
| dblink_encrypt_login | Enforce password for distributed login always be encrypted |
| dbwr_io_slaves | DBWR I/O slaves |
| disk_asynch_io | Use asynch I/O for random access devices |
| distributed_transactions | Maximum number of concurrent distributed transactions |
| dml_locks | One for each table modified in a transaction |
| enqueue_resources | Resources for enqueues |
| ent_domain_name | Enterprise Domain Name |
| event | Debug event control - default null string |
| fast_start_io_target | Upper bound on recovery reads |
| fast_start_parallel_rollback | Maximum number of parallel recovery slaves that may be used |
| fixed_date | Fixed SYSDATE value |
| gc_defer_time | How long to defer down converts for hot buffers (DFS) |
| gc_files_to_locks | Mapping between file numbers and lock buckets (DFS) |
| gc_releasable_locks | Number of releasable locks (DFS) |
| gc_rollback_locks | Locks for the rollback segments (DFS) |
| global_names | Enforce that database links have same name as remote database |
| hash_area_size | Size of in-memory hash work area |
| hash_join_enabled | Enable/disable hash join |
| hash_multiblock_io_count | Number of blocks hash join will read/write at once |
| hi_shared_memory_address | SGA starting address (high order 32-bits on 64-bit platforms) |
| hs_autoregister | Enable automatic server DD updates in HS agent self-registration |
| ifile | Include file in init.ora |
| instance_groups | List of instance group names |
| instance_name | Instance name supported by the instance |
| instance_number | Instance number |
| java_max_sessionspace_size | Maximum allowed size in bytes of a Java sessionspace |
| java_pool_size | Size in bytes of the Java pool |
| java_soft_sessionspace_limit | Warning limit on size in bytes of a Java sessionspace |
| job_queue_interval | Wakeup interval in seconds for job queue processes |
| job_queue_processes | Number of job queue processes to start |
| large_pool_size | Size in bytes of the large allocation pool |
| license_max_sessions | Maximum number of non-system user sessions allowed |
| license_max_users | Maximum number of named users that can be created in the database |
| license_sessions_warning | Warning level for number of non-system user sessions |
| lm_locks | Number of locks configured for the lock manager |
| lm_procs | Number of client processes configured for the lock manager |
| lm_ress | Number of resources configured for the lock manager |
| local_listener | Local listener |
| lock_name_space | Lock name space used for generating lock names for standby/clone |
| lock_sga | Lock entire SGA in physical memory |
| log_archive_dest | Archival destination text string |
| log_archive_dest_1 | Archival destination #1 text string |
| log_archive_dest_2 | Archival destination #2 text string |
| log_archive_dest_3 | Archival destination #3 text string |
| log_archive_dest_4 | Archival destination #4 text string |
| log_archive_dest_5 | Archival destination #5 text string |
| log_archive_dest_state_1 | Archival destination #1 state text string |
| log_archive_dest_state_2 | Archival destination #2 state text string |
| log_archive_dest_state_3 | Archival destination #3 state text string |
| log_archive_dest_state_4 | Archival destination #4 state text string |
| log_archive_dest_state_5 | Archival destination #5 state text string |
| log_archive_duplex_dest | Duplex archival destination text string |
| log_archive_format | Archival destination format |
| log_archive_max_processes | Maximum number of active ARCH processes |
| log_archive_min_succeed_dest | Minimum number of archive destinations that must succeed |
| log_archive_start | Start archival process on SGA initialization |
| log_buffer | Redo circular buffer size |
| log_checkpoint_interval | Number of redo blocks checkpoint threshold |
| log_checkpoint_timeout | Maximum time interval between checkpoints in seconds |
| log_checkpoints_to_alert | Log checkpoint begin/end to alert file |
| log_file_name_convert | Logfile name convert pattern and string for standby/clone database |
| max_commit_propagation_delay | Maximum age of new snapshot in .01 seconds |
| max_dump_file_size | Maximum size (blocks) of dump file |
| max_enabled_roles | Maximum number of roles a user can have enabled |
| max_rollback_segments | Maximum number of rollback segments in SGA cache |
| mts_dispatchers | Specifications of dispatchers |
| mts_listener_address | Address(es) of network listener |
| mts_max_dispatchers | Maximum number of dispatchers |
| mts_max_servers | Maximum number of servers |
| mts_multiple_listeners | Are multiple listeners enabled? |
| mts_servers | Number of servers to start up |
| mts_service | Service supported by dispatchers |
| nls_calendar | NLS calendar system name |
| nls_comp | NLS comparison |
| nls_currency | NLS local currency symbol |
| nls_date_format | NLS Oracle date format |
| nls_date_language | NLS date language name |
| nls_dual_currency | Dual currency symbol |
| nls_iso_currency | NLS ISO currency territory name |
| nls_language | NLS language name |
| nls_numeric_characters | NLS numeric characters |
| nls_sort | NLS linguistic definition name |
| nls_territory | NLS territory name |
| nls_time_format | Ttime format |
| nls_time_tz_format | Time with timezone format |
| nls_timestamp_format | Time stamp format |
| nls_timestamp_tz_format | Timestampe with timezone format |
| O7_DICTIONARY_ACCESSIBILITY | Version 7 Dictionary Accessibility Support |
| object_cache_max_size_percent | Percentage of maximum size over optimal of the user session's object |
| object_cache_optimal_size | Optimal size of the user session's object cache in bytes |
| open_cursors | Maximum number of cursors per process |
| open_links | Maximum number of open links per session |
| open_links_per_instance | Maximum number of open links per instance |
| optimizer_features_enable | Optimizer plan compatibility parameter |
| optimizer_index_caching | Optimizer percent index caching |
| optimizer_index_cost_adj | Optimizer index cost adjustment |
| optimizer_max_permutations | Optimizer maximum join permutations per query block |
| optimizer_mode | Optimizer mode |
| optimizer_percent_parallel | Optimizer percent parallel |
| optimizer_search_limit | Optimizer search limit |
| oracle_trace_collection_name | Oracle TRACE default collection name |
| oracle_trace_collection_path | Oracle TRACE collection path |
| oracle_trace_collection_size | Oracle TRACE collection file max. size |
| oracle_trace_enable | Oracle TRACE instance wide enable/disable |
| oracle_trace_facility_name | Oracle TRACE default facility name |
| oracle_trace_facility_path | Oracle TRACE facility path |
| os_authent_prefix | Prefix for auto-logon accounts |
| os_roles | Retrieve roles from the operating system |
| parallel_adaptive_multi_user | Enable adaptive setting of degree for multiple user streams |
| parallel_automatic_tuning | Enable intelligent defaults for parallel execution parameters |
| parallel_broadcast_enabled | Enable broadcasting of small inputs to hash and sort merge joins |
| parallel_execution_message_size | Message buffer size for parallel execution |
| parallel_instance_group | Instance group to use for all parallel operations |
| parallel_max_servers | Maximum parallel query servers per instance |
| parallel_min_percent | Minimum percent of threads required for parallel query |
| parallel_min_servers | Minimum parallel query servers per instance |
| parallel_server | If TRUE startup in parallel server mode |
| parallel_server_instances | Number of instances to use for sizing OPS SGA structures |
| parallel_threads_per_cpu | Number of parallel execution threads per CPU |
| partition_view_enabled | Enable/disable partitioned views |
| plsql_load_without_compile | PL/SQL load without compilation flag |
| plsql_v2_compatibility | PL/SQL version 2.x compatibility flag |
| pre_page_sga | Pre-page sga for process |
| processes | User processes |
| query_rewrite_enabled | Allow rewrite of queries using materialized views if enabled |
| query_rewrite_integrity | Perform rewrite using materialized views with desired integrity |
| rdbms_server_dn | RDBMS's Distinguished Name |
| read_only_open_delayed | If TRUE delay opening of read only files until first access |
| recovery_parallelism | Number of server processes to use for parallel recovery |
| remote_dependencies_mode | Remote-procedure-call dependencies mode parameter |
| remote_login_passwordfile | Password file usage parameter |
| remote_os_authent | Allow non-secure remote clients to use auto-logon accounts |
| remote_os_roles | Allow non-secure remote clients to use os roles |
| replication_dependency_tracking | Tracking dependency for Replication parallel propagation |
| resource_limit | Master switch for resource limit |
| resource_manager_plan | Resource mgr top plan |
| rollback_segments | Declared ONLINE rollback segments list |
| row_locking | Row-locking |
| serial_reuse | Reuse the frame segments |
| serializable | Serializable |
| service_names | Service names supported by the instance |
| session_cached_cursors | Number of cursors to save in the session cursor cache |
| session_max_open_files | Maximum number of open files allowed per session |
| sessions | User and system sessions |
| shared_memory_address | SGA starting address (low order 32-bits on 64-bit platforms) |
| shared_pool_reserved_size | Size in bytes of reserved area of shared pool |
| shared_pool_size | Size in bytes of shared pool |
| sort_area_retained_size | Size of in-memory sort work area retained between fetch calls |
| sort_area_size | Size of in-memory sort work area |
| sort_multiblock_read_count | Multi-block read count for sort |
| sql_trace | Enable SQL trace |
| sql92_security | Require select privilege for searched update/delete |
| standby_archive_dest | Standby database archivelog destination text string |
| star_transformation_enabled | Enable the use of star transformation |
| tape_asynch_io | Use asynch I/O requests for tape devices |
| text_enable | Enable text searching |
| thread | Redo thread to mount |
| timed_os_statistics | Internal os statistic gathering interval in seconds |
| timed_statistics | Maintain internal timing statistics |
| transaction_auditing | Transaction auditing records generated in the redo log |
| transactions | Maximum number of concurrent active transactions |
| transactions_per_rollback_segment | Number of active transactions per rollback segment |
| use_indirect_data_buffers | Enable indirect data buffers (very large SGA on 32-bit platforms |
| user_dump_dest | User process dump directory |
| utl_file_dir | Accessible directories list |
All the above values can be obtained by selecting from the V$PARAMETER dynamic performance view in Oracle SQL. The V$PARAMETER view has columns as shown below. All the entries in the V$PARAMETERS view can appear in the init.ora file. Note that some parameters are changeable at run-time (whilst the database is open) using the ALTER SYSTEM command.
In Oracle7 on the parameter file is stored by default in the Oracle Home\Database directory and in Oracle8i in the Oracle Home\Admin\Pfile directory. These files can be moved. The IFILE parameter can be used to point to a parameter file stored in another location. Some characters are reserved in parameter values and must be escaped in order to be included. These characters are #, (, ), ", ', =, ', - and \.