ToolLook up

Init parameter reference

What common initialization parameters control, what to know before changing them, and the exact statements to check and set each one: SCOPE=SPFILE for static parameters, SCOPE=BOTH for dynamic ones, and DEFERRED where only new sessions pick up the change.

Open the tool

The tool runs entirely in your browser. Nothing you type into it is sent anywhere.

Parameters covered

• MEMORY_TARGETTotal memory for SGA and PGA together under Automatic Memory Management.
• MEMORY_MAX_TARGETUpper limit for MEMORY_TARGET.
• SGA_TARGETSize of the SGA under Automatic Shared Memory Management. Oracle moves memory between the buffer cache, shared pool and other pools as load changes.
• SGA_MAX_SIZEThe most memory the SGA can grow to without a restart.
• PGA_AGGREGATE_TARGETTarget for the total private memory of all server processes, used for sorts, hash joins and bitmap operations.
• PGA_AGGREGATE_LIMITHard limit on total PGA (12c and later). Above it, Oracle ends the calls or sessions using the most PGA with ORA-04036.
• SHARED_POOL_SIZESize of the shared pool: parsed SQL, PL/SQL and the dictionary cache. Under SGA_TARGET it's a minimum.
• DB_CACHE_SIZESize of the default buffer cache. Under SGA_TARGET it's a minimum.
• USE_LARGE_PAGESWhether the SGA is placed in Linux HugePages.
• INMEMORY_SIZESize of the In-Memory column store. On 12.2 and later it can be increased while the instance is running.
• PROCESSESThe most operating system processes that can connect to the instance, including background processes.
• SESSIONSThe most sessions the instance allows.
• OPEN_CURSORSThe most cursors one session can have open at once.
• SESSION_CACHED_CURSORSHow many closed cursors each session keeps cached, which saves repeated soft parses.
• JOB_QUEUE_PROCESSESThe most job slave processes for Scheduler and DBMS_JOB jobs.
• RESOURCE_LIMITWhether profile resource limits such as idle time and CPU per call are enforced.
• DDL_LOCK_TIMEOUTSeconds a DDL statement waits for a lock before failing with ORA-00054.
• UNDO_RETENTIONSeconds of committed undo Oracle tries to keep, for long queries and Flashback Query.
• UNDO_TABLESPACEWhich undo tablespace the instance uses. Each RAC instance has its own.
• LOG_ARCHIVE_DEST_NWhere archived redo goes: a local location or a Data Guard standby service. Numbered 1 to 31.
• LOG_ARCHIVE_FORMATFile name pattern for archived logs written to a plain directory.
• ARCHIVE_LAG_TARGETForces a log switch after this many seconds, even when the database is quiet.
• LOG_BUFFERSize of the redo log buffer.
• FAST_START_MTTR_TARGETTarget seconds for crash recovery. Oracle writes dirty blocks early enough to meet it.
• DB_RECOVERY_FILE_DESTLocation of the Fast Recovery Area, for archived logs, backups and flashback logs.
• DB_RECOVERY_FILE_DEST_SIZEThe most space the Fast Recovery Area may use.
• DB_FLASHBACK_RETENTION_TARGETMinutes of flashback logs to keep for Flashback Database.
• CONTROL_FILE_RECORD_KEEP_TIMEDays that reusable backup and archived log records stay in the control file.
• DB_BLOCK_CHECKSUMWhether blocks carry a checksum that's verified on read.
• DB_BLOCK_CHECKINGLogical consistency checks on blocks when they change.
• DB_LOST_WRITE_PROTECTRecords block versions in redo so lost writes can be detected.
• DG_BROKER_STARTStarts the Data Guard broker process (DMON).
• LOG_ARCHIVE_CONFIGThe DB_UNIQUE_NAMEs of every database in the Data Guard configuration.
• FAL_SERVERNet service names a standby asks for missing archived logs.
• STANDBY_FILE_MANAGEMENTWhether datafiles added on the primary are created automatically on the standby.
• DB_UNIQUE_NAMEA name that's unique to each database in a Data Guard configuration, used by the broker, services and file naming.
• OPTIMIZER_FEATURES_ENABLEMakes the optimizer behave like an earlier release.
• OPTIMIZER_ADAPTIVE_STATISTICSAdaptive statistics features such as SQL plan directives and dynamic statistics (12.2 and later).
• OPTIMIZER_ADAPTIVE_PLANSLets the optimizer switch join methods during the first execution (12.2 and later).
• CURSOR_SHARINGWhether Oracle replaces literals with bind variables before parsing.
• STATISTICS_LEVELHow much performance data Oracle collects.
• OPTIMIZER_MODEWhether the optimizer aims for total throughput or fast first rows.
• DB_FILE_MULTIBLOCK_READ_COUNTBlocks read in one I/O during full scans.
• PARALLEL_MAX_SERVERSThe most parallel execution processes per instance.
• PARALLEL_DEGREE_POLICYWhether Oracle chooses the degree of parallelism itself.
• RESULT_CACHE_MAX_SIZEMemory for the server result cache.
• DB_CREATE_FILE_DESTDefault location for Oracle-managed datafiles and tempfiles.
• DB_CREATE_ONLINE_LOG_DEST_NLocations for Oracle-managed online redo logs and control files. Numbered 1 to 5; each one holds a member of every group.
• CONTROL_FILESThe control file copies the instance opens at startup.
• DB_FILESThe most datafiles the database can have open.
• FILESYSTEMIO_OPTIONSAsynchronous and direct I/O for datafiles on file systems.
• DEFERRED_SEGMENT_CREATIONWhether new tables and indexes get space only when the first row arrives.
• RECYCLEBINWhether dropped tables go to the recycle bin so they can be flashed back.
• MAX_STRING_SIZEEXTENDED raises VARCHAR2, NVARCHAR2 and RAW limits from 4000 to 32767 bytes.
• LOCAL_LISTENERThe listener the instance registers its services with.
• REMOTE_LISTENERRemote listeners the instance also registers with, such as the RAC SCAN listeners.
• SERVICE_NAMESService names the instance registers.
• GLOBAL_NAMESRequires a database link's name to match the remote database's global name.
• AUDIT_TRAILWhere traditional auditing records go.
• AUDIT_SYS_OPERATIONSAudits top-level statements run as SYS, SYSDBA and SYSOPER to OS files.
• AUDIT_FILE_DESTDirectory for OS audit files, including SYS audit records.
• REMOTE_LOGIN_PASSWORDFILEWhether a password file is used for remote SYSDBA connections.
• SEC_MAX_FAILED_LOGIN_ATTEMPTSFailed attempts on one connection before the server drops it.
• CONTROL_MANAGEMENT_PACK_ACCESSWhich management packs are enabled: the Diagnostics Pack (AWR, ASH, ADDM) and the Tuning Pack.
• DIAGNOSTIC_DESTRoot of the Automatic Diagnostic Repository: alert log, traces and incidents.
• MAX_DUMP_FILE_SIZEThe largest trace file a process may write.
• COMPATIBLEThe release whose on-disk format and features the database uses.
• ENABLE_PLUGGABLE_DATABASEWhether the database is a multitenant container database.
• NLS_LENGTH_SEMANTICSWhether VARCHAR2 lengths in new columns default to bytes or characters.