Parameter file

You may want to tune various instance parameters for memory usage, etc., to do this you will have to change Oracle's initialization parameter file.

Oracle offers two types of parameter files - INIT.ORA and SPFILE. Their default location on startup command is $ORACLE_HOME/dbs or %ORACLE_HOME%\database.

Parameter file types

INIT.ORA/ PFILEs

PFILEs are also know as INIT.ORA files. Characteristics of a PFILE:

• Client side
• Text file
• Edit with text editor like vi or notepad

SPFILEs

Characteristics of an SPFILE:

• Server side
• Binary file
• Edit by issuing ALTER SYSTEM SET commands

To see if a database is using an SPFILE, issue the following command from SQL*Plus:

show parameter spfile;

See what parameters are set

From SQL*Plus (connected with an account having SELECT ANY DICTIONARY privilege):

show parameters db_name

As a query:

SELECT * FROM v$parameter;

Also see

• List of Oracle initialization parameters