Parameter file

From Oracle FAQ
(Redirected from Spfile)
Jump to: navigation, search

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[edit]


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

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


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[edit]

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[edit]