Oracle Database 512 pages
Trace File 10053 to understand the Optimizer (CBO)(Access|Scan) (Paths|Method)Oracle Automatic Storage Management Cluster File System (ACFS)ACTIVE_INSTANCE_COUNT parameter(Actual|Final) Execution PlanActual (Runtime) Plan StatisticsAdaptive OptimizationAdaptive PlanADDM (Automated Database Diagnostic Monitor) - Automatic Performance Diagnostics ToolAnalyticsSQL - Analytic FunctionsMode Archive Log / ArchivingArchived Redo Log FilesARCHIVELOGHow to change the Database Archiving ModeHow to know the archivelog mode ?The effect of ArraySize on Logical I/OASM (Automatic Storage Management)Prepare Storage for Oracle Automatic Storage Management on LinuxAuthenticationAUTHIDAutomatic reoptimization / (statistics|cardinality) feedbackAutonomous TransactionsAutoTask (Automated maintenance tasks)AutotraceAWR (Advanced|Automatic) - Workload Repository - Performance Statistics Gathering ToolBackground ProcessOracle Databse - SQL - betweenBitmap IndexesBlevel Index Statistics(Data|Db|Logical|Oracle) Block or Page(B|Balanced) Tree - BTree indexesBuffer IO (Logical IO)Buffer cacheBuffer PoolHow can we load a large number of rows into an indexed existing table ?Bytes or Characters for VARCHAR2 and CHAROracle Database - bytes sent via SQL*Net to client(Memory) Cache (Tables, ...)Rows (of Card as Cardinality for 9i)Cartesian Joins OperationCascade(Consolidated|Root) Database (Cdb)ORACLE - Streams / Change Data CaptureHow to change the password for the account SYS/SYSTEM ?Character SetConfigure Database Options with the chopt toolClient ApplicationCluster Scan/AccessThe (index) Clustering Factor StatisticsCman.ora configuration fileColumns StatisticsCOMMIT (of a transaction)CompileCompressionConcurrency Wait ClassHow to connect SYS as SYSDBA without passwordConnect Descriptor (TNS Connect String)Connect IdentifierConnect String (Client Connection Initiation)Oracle Connection ManagerHow to retrieve the connect descriptor parameters? (host, port, service name and SID)Connection PoolRead ConsistencyConsistent (Read get|Buffer Mode) (CR)(Integrity) Constraints(Application) ContextControl FilesCostCPU_COUNT parameterCreate table as select (CTAS)CURRENT_SCHEMACURRENT_USER, SESSION_USERData DictionaryData FilesData Load(Data operations|Execution Plan Steps)Data Pump - (Export|Import) UtilitiesData Redistribution (Parallel)DatabaseOracle Enterprise Manager Database Control (Database Control)Database LinkDatabase ServiceOracle - DataTypeCharacter DataTypeOracle - Data type ConversionDatatype DatetimeNumeric DatatypeData WarehousingOracle - Date Datatype(Db Block Get|Current Get) Buffer Mode (CU)DB_BLOCK_SIZE initialization parameterDB_DOMAINDB_FILE_MULTIBLOCK_READ_COUNT Parameter'db file scattered read' wait event db file sequential read Wait EventDB_NAMEDB_UNIQUE_NAME parameterDatabase Administrator (Dba)DBCADBMS_APPLICATION_INFO to manage long-running operationDBMS_FLASHBACKDBMS_LOB PackageDBMS_LOCKDBMS_MONITORDbms_mview PackageDBMS_SPACE PackagePL/SQL Package DBMS_XDBDBMS_XPLANDatabase Writer Process (DBWn)DDL Locks (data dictionary lock)DeadlockDebug.f for debugging PL/SQL applicationDedicated Server ArchitectureDedicated Server ProcessDimensionsDirect (path insert|load) (/*+ APPEND */)"direct path read temp" and "direct path read" wait eventDirectory ObjectDirectory naming method (LDAP-compliant)DISK_ASYNCH_IO parameterASM - Disk groupDispatcher and Shared Server ProcessExplain Plan from Cursor Cache (DBMS_XPLAN.DISPLAY_CURSOR)Oracle SQL - DISTINCT (or UNIQUE)Distributed TransactionsDML Lock(Degree|Degree of Parallelism (DOP))Implications of the Driving TableDUALDynamic (Statistics|Sampling)PL/SQL - Dynamic SQLEasy Connect Method with host, port and service name (EZCONNECT)Entreprise ManagerEnvironment Variables / Registry ValuesOracle Database 11g: Administration I - 1Z0-052 PL/SQL - Execute ImmediateExecution(Execution|Query) PlanHow to read a query plan ?Export Utility (exp)Data Pump Export (expdp)Explain PlanEXPLAIN PLAN and Autotrace Comparison(Logical) Extent (Unit of Storage)External naming methodExternal TablesHow to load a CLOB field with an external tableOracle database - File (Common Specification for data file, control file,...)File SystemEffect of a filter on a outer join tableFIRST_ROWSSQL - First Value Analytic function_FIX_CONTROL Optimizer ParameterFlash CacheFlashbackFlashback QueryFloating-Point Number Datatype (BINARY_FLOAT, BINARY_DOUBLE)FOREIGN KEY Constraints(Fragmented) (Unused|Free) space (Reclaim|Shrink|resize)Full Table ScansOracle - Function-based IndexGateway (Heterogeneous services)Gateway ArchitectureGateway Data DictionaryGateway - Data Dictionary (from the remote data source) - The Translation ViewsGateway LogGateway Agent RegistrationGlobal Database Name (GLOBAL_NAME)GLOBAL_NAMES parameterGlossaryGolden Gate (OGG)Granule (Parallel Data Access)Grid ControlOperating System GroupHard Parse (Library cache miss)Oracle Database and HardwareHash Access / ScanHASH AREA memoryHASH_AREA_SIZE parameterHash joins(Level|Height) Index statisticsHigh Water Mark (HWM) - boundary between used and unused spaceData Loading HintHintsHistogram (Column statistics)Histogram AnalyticHow to create a time dimension table in Sql ?PLSQL - IF StatementImport (imp)Data Pump Import (impdp)IndexesIndex ScansIndex StatisticsINITRANS parameterInsert StatementInstallation of a database RAC 10gInstallation of 11gR1/R2 on WindowsInstallation 11g Release 2 (11.2) on Linux OEL 5 (X86)Oracle Database 11gR2 - Installation on Linux OEL 5 Update 5 (x86_64)12c Installation on Windows 64 bitInstallationInstallation Oracle Database 10gR2 on WindowsInstance (Database Start-up)INSTANCE_NAME parameterINTEGER DatatypeInterconnect Traffic (RAC)Internal LocksOracle SQL - IntervalInput/Output (I/O)Oracle - JavaOracle - Java/JSERVER - Debug Load / CompilationOracle - JSERVER - How to get the Java Version ?Procedure Stockee JavaJava Database Connectivity (JDBC) DriverJoin (Operation|Method|Mechanism|Algorithm)Join OrderSQL - Analytic Function LAG/LEADNLS_LANGUAGE parameterLatches (System Lock)Leaf (block|Node) of a Btree indexLog Writer (lgwr)Session (Library|Cursor) CacheLike SQL ConditionConfiguration of the Listener (listener.ora)ListenerOracle - LOB DatatypeLocal Naming MethodLocksLocks and Foreign Keys - Concurrency control of parent keysLogical StructureLogMinerLong and Long Raw DatatypeLRUMaterialized view (Snapshot)MemoryMERGE JOIN operationOracle OLAP - Methodology for Defining the Analytic WorkspaceMethodology for Designing and Building Materialized ViewsOracle Database SQL - MinusMemory manager (MMAN)SQL - Model ClauseOracle - MonitoringMultiblockMutexesNaming conventionNaming method (naming resolution method)Nested Loop JoinOperations Involved in NESTED LOOPSOracle Net (SQL*Net)Oracle Net ManagerNet Service AliasNet Service Name (tnsname)Oracle Net Configuration AssistantNLS_DATE_FORMATOracle - NLS_DATE_LANGUAGENLS_LANG (LOCALE)NLS_LENGTH_SEMANTICS parameterNLS_NUMERIC_CHARACTERS (decimal separator and thousands separator)Globalization (Localization, NLS parameter)NLS_SORT (Sorting Character parameter)NOARCHIVELOGNOLOGGINGNon-schema ObjectNull value (Missing Value)NUMBER Data TypeObjectsODBCOracle ODBC driverOptimal Flexible Architecture (OFA)Query optimizerOPTIMIZER_FEATURES_ENABLE ParameterOptimizer Goal (CBO/RBO)ORA-01720 - grant option does not exist for 'AnObject'Error (Ora-XXXXX)ORA_HASH FunctionORA_NLSOracle Validated Configuration RPM for a Linux InstallationBackupOracle base directory (ORACLE_BASE)Character Set Functions : CONVERT, UNISTRA Common Business Problem : Messages (AQ)AuditingOracle Database Is Function (IsDate, ...)Green/Grid ComputingOracle Home Directory (ORACLE_HOME|OH)ORACLE_HOSTNAME environment variableHow to read data through an ODBC connection with Gateway (Heterogeneous services)Oracle OlapORACLE_SID environment variableORACLE_UNQNAME environment variableDatatype difference between VARCHAR2 and CHARHow to reconfigure Enterprise Manager Database Control (with Emca)How to find the version informations of the Database ?How to detect read from the temporary tablespace ?Table Logging Mode (LOGGING, NOLOGGING)Oraenv and coraenv script to set Linux environment variableOracle Inventory directory (oraInventory)OUI-10044: The selected Oracle Home ... Oracle Universal Installer (OUI)Parallel OperationsPARALLEL_DEGREE_LIMITOracle Database 11g - PARALLEL_DEGREE_POLICY parameterParallel DMLHow to (enable|disable) parallel query and get (degree of parallelism|DOP) ?Parallel_execution_message_size parameterPARALLEL_MAX_SERVERS parameterPARALLEL_MIN_PERCENT parameterPARALLEL_MIN_SERVERS parameterParallel Execution MonitoringParallel execution with Oracle Partitioning (parallel partition-wise join)Parallel execution in an RAC environment(Parallel|PX) ServerPARALLEL_SERVER_TARGETPARALLEL_SERVERS_TARGET parameterPARALLEL_THREADS_PER_CPU Parameter(Initialization|System) ParametersParameter FilesSQL ParsingPartitionsPartition Exchange for Data Movement / Data LoadPartition Pruning (Elimination)Partition-Wise Join (PWJ)Partitioning and Materialized ViewsPATH environment VariablePattern matchingPCTFREE (Percentage Free)Pluggable Database (PDB)PerformancePerformance statistics (V$ views)Process global area (PGA) - Work AreaPGA_AGGREGATE_TARGET ParameterPhysical I/OPhysical ReadPhysical Database Structures (Files)(Pivot|Unpivot) StatementThe Query Plan EstimatorThe Query Plan GeneratorPMON processPooled Server ArchitecturePort AssignementPL/SQL - PragmaMaterialized Views (ROLAP) and Cubes (MOLAP) comparison (Pre Compute Operations)Execution Plan PredicatePrimary KeyPrivilegesProducer / Consumer ModelUser ProfileParallel SlavesThe Query Coordinator (QC)Oracle - Controlling the Behavior of the Query Optimizer with initialization parameters(Iterative|Query) PlanDatabase Oracle - Query RewriteOracle - The Query TransformerExample of query Tuning (to decrease the logical I/O)Oracle Real Application Clusters (RAC)SQL - RANK FunctionRDFReadRecoveryOracle - Recursive CallsOracle - Recursive call caused by Hard ParseOracle - Recursive call caused by PL/SQL functionOracle - Recursive calls caused by data modification (triggers, function based index, ...)Recursive call caused by space requestsRecycle Bin (Flashback Drop)(online table) RedefinitionWhy you have still a lot of redo and archive ? The Index side effect.Redo Log (Log Files)How to bypass the redo log generation ?Redo Size statisticsSQL - Regular expressionResourcesResource Manager (DBRM)Ressource ManagerResult SetReverse Key IndexesRoleROLLBACK (undo of a transaction)Row Locks (TX)SQL - ROW_NUMBER functionRow OperationRow SetRow source generatorRowidRowid Scans operationRownumRunstatsSample SQL Clause for Table Scans/AccessSample SchemasSAVEPOINT (of a transaction)SchedulerSchemaSchema ObjectSystem Change Numbers (SCNs)Session Data Unit (SDU) parameterUser, Security(Logical) Segment (Database Object Storage)Oracle SQL - SELECTSELECT_CATALOG_ROLESELECT ... FOR UPDATE statementSelectivitySequenceServer ProcessServer Result CacheService HandlerService nameSERVICE_NAMES parameterService RegistrationSessionSession Statistics (V$SESSTAT)SQL Plus - Set Null(Set|Set-based) OperationSystem Global Area - SGA (Shared Memory)Shared PoolShared server architectureShared sql areaSystem Identifier (SID)Skip_unusable_indexes parameterSoft parse (Library cache hit)(Software|Installation) Owner (oracle, grid)Sort Aggregate OperationSort Area memorySORT_AREA_SIZE parameterSORT JOIN (or SORT MERGE JOIN)Sorts (Disk)Sorts (memory) statisticsSpace (Size)Special Characters(initialization|server) parameter file (init.ora | spfile.ora)SQLSQL Access Advisor (Summary Advisor)SQL*Net roundtrips to from clientOracle Database - SQL*Net StatisticsSQL Parallel ExecutionSQL Plan ManagementSQL Processing - How Oracle Database processes SQL statements?Oracle - SQL_TRACESQL Tuning AdvisorSQL Loader - Lob FileSQLNET.ORAStandBy DatabaseStar TransformationAutomating Shutdown and Startup on LinuxOptimizer StatisticsSetting Up StatspackStattabStorage OptionsStored ProcedureJava Stored ProcedurePL/SQL - Result Sets from Stored ProceduresSynonym (Object Alias)SYSASM privilegesSysdateSYSDBA privilegeV$SYSMETRICSYSOPER privilegeSystem LockSYSTEM TablespaceTableTable Function (Pipelined)Table Lock (TM)Table Size(Optimizer) Table StatisticsTableSpaceTemporary Datafiles (tempfiles)Oracle Database 12c - Temporal ValidityTemporary TablespaceTerritory (NLS_TERRITORY)TIMED_STATISTICS parameterTIMESTAMP data typeOracle - TkprofTNS_ADMIN environement variableTnsnames.oraTNS Ping UtilityTO_NUMBER functionTrace ToolTrace File (.trc)TransactionsTransaction table - Interested transaction list (ITL)TriggerTwo-Task Common (TTC) protocolLOCAL or TWO_TASK Environment VariableUNDOUnionUNIQUE ConstraintsUpdateV$object_usageUser (Account/Client)Userenv (Current Session Informations)Utl_mail packagePL/SQL - Using utl_match functions to compare string similarityV$SQLV$SQL_PLANV$SQL_PLAN_STATISTICSV$SQL_PLAN_STATISTICS_ALLV$SQL_WORKAREAV$SQLAREAV$ tableVersionViewVirtual circuitVpd (Virtual Private Database)Wait EventWhat are the benefits to define a dimension in Oracle ?SQL - With clause(Query) Work AreaWORK_AREA_SIZE_POLICYXmlOracle XML DB


Oracle Database - (Initialization|System) Parameters


Parameter are global variable.

Their values are initialized from a file during the start of the Oracle Database.

If you start a database instance using spfile with an environment variable set, then its value is automatically stored in spfile. If you unset the environment variable subsequently and start the instance afresh, then database uses the parameter value of Oracle base stored in spfile.



The SCOPE of a parameter Scope depends on whether you started up the database using a client-side parameter file (pfile) or server parameter file (spfile).

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.


MEMORY MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.


SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.


BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.


ALTER system SET db_2k_cache_size=100m SCOPE=SPFILE;
ALTER system SET db_2k_cache_size=100m SCOPE=MEMORY;
ALTER system SET db_2k_cache_size=100m SCOPE=BOTH;


Not every parameter is changeable when the instance is running; parameters fall into three general categories:

Not changeable online

Not changeable online. The Oracle Database Reference describes initialization parameters and their properties, among other things. The documentation includes a “modifiable” property for each parameter, and if a parameter is not modifiable, it is not changeable online. AUDIT_TRAIL, for example, is not modifiable (not changeable online).

Changeable online, but only for future sessions

Changeable online, but only for future sessions. The change won’t affect any currently connected session, but it will affect all new sessions created after the ALTER SYSTEM was executed. For example, SORT_AREA_SIZE is changeable online, but only for future sessions:

SQL> ALTER system 
  2    SET sort_area_size =32765 
  3    deferred scope=memory;
System altered.
SQL> SHOW parameter sort_area_size
NAME             TYPE      VALUE
-------          -------   -----
sort_area_size   INTEGER   65536
SQL> SHOW parameter sort_area_size
NAME             TYPE      VALUE
-------          -------   -----
sort_area_size   INTEGER   32765

Changeable online and immediately reflected in all sessions

The change will connect all currently connected sessions. For example, USER_DUMP_DEST is changeable online and is immediately reflected in all sessions:

SQL> SHOW parameter user_dump_dest
NAME             TYPE      VALUE
-------          -------   -----
user_dump_dest   string   /tmp
SQL> ALTER system 
SET user_dump_dest = 
System altered.
SQL> SHOW parameter user_dump_dest
NAME             TYPE      VALUE
-------          -------   -----
user_dump_dest   string   /home/ora10...

Parameter Management


for the system

The server parameter file enables you to change initialization parameters with ALTER SYSTEM commands, and to carry the changes across a shutdown and startup.

Three ways:

  • By editing an initialization parameter file
  • By issuing an ALTER SYSTEM SET … SCOPE=SPFILE statement to update a server parameter file
  • By issuing an ALTER SYSTEM RESET … SCOPE=SPFILE statement to remove a parameter from a server parameter file, causing the default value to take effect the next time you start an instance of the database.

for a session



SELECT * FROM v$parameter:

Documentation / Reference

  • Bookmark "Oracle Database - (Initialization|System) Parameters" at
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Digg
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Ask
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Google
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at StumbleUpon
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Technorati
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Live Bookmarks
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Yahoo! Myweb
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Facebook
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Yahoo! Bookmarks
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at Twitter
  • Bookmark "Oracle Database - (Initialization|System) Parameters" at myAOL
database/oracle/parameter.txt · Last modified: 2014/03/18 15:12 by gerardnico