System

Parameter File

Change
Create
Display
Startup With PFile\SPFile
Windows Registry

Default Directory UNIX\LINUX

%ORACLE_HOM%\dbs

Windows (as shown via registry item RDBMS_CONTROL)

%ORACLE_HOME%\database

Change Value

ALTER Database|System SET <ParameterName>=<value> SCOPE=<both|spfile|memory>;

 ALTER SYSTEM SET pga_aggregate_target=256m SCOPE=both;

RAC Example

ALTER SYSTEM SET <ParameterName>=<value> 'SID='*' SCOPE=<both|spfile|memory>;

 ALTER SYSTEM SET sec_case_sensitive_logon=false SID='*' SCOPE=both;

Create

-- Create PFILE From SPFILE
CREATE pfile='c:\temp\file.ora' FROM spfile;

-- Create SPFILE from PFILE
CREATE SPFILE FROM PFILE; Creates file in default directory from current pfile.
OR

 CREATE SPFILE FROM PFILE='c:\temp\initDB1.ora';

-- Create SPFILE in New Location
CREATE SPFILE='c:\temp\pfile.ora' FROM PFILE='c:\temp\initDB1.ora';

The SPFILE cannot be created in the default location if it is active.
By default (if not specified) the SPFILE is created in the $ORACLE_HOME/database directory.

Display

The best view to use is V$SYSTEM_PARAMETER. This will display the parameter settings on system (instance) level. V$PARAMETER will show in on session level and initially inherits it from V$SYSTEM_PARAMETERS. V$SPPARAMETER will show the setting in the SPFILE (if used). They might be different from V$SYSTEM_PARAMETERS depending if they have been changed using an ALTER SYSTEM SCOPE=MEMORY

Useful Parameter Formating for SQLPLus

SET LINESIZE 200
COL name  FORMAT a40
COL value FORMAT a65

-- Show Dynamic Parameters

 SELECT name,value, ISSES_MODIFIABLE, ISINSTANCE_MODIFIABLE
 FROM v$parameter
 WHERE isInstance_modifiable='TRUE'
 ORDER BY name;

Non-dynamic params will give you an the ORA-02095 error if trying to change.

-- Non-default Parameters

 SELECT name, value FROM v$spparameter WHERE value !='NULL' ORDER BY name;

-- Current Parameter Values

 SELECT name, value FROM v$parameter ORDER BY name;

-- SPFile Parameter Values

 SELECT name, value FROM v$spparameter ORDER BY name;

-- All Paramters V2

 SQLPLUS> show parameters

-- Instance Parameters (these are what all new sessions inherit)

 SELECT value FROM v$system_parameter
 SELECT value FROM v$system_parameter WHERE name='service_names';

-- Location (path) of Currently Used SPFILE

 show parameter spfile

-- Wild Card String Match By Partial Parameter Name

Format: show parameter {Part_of_Parameter_String_to_Match}

 show parameter log
 show parameter sga

Startup

Open Database Using Specified PFILE/SPFILE

 shutdown immediate
 startup pfile='c:\temp\my_pfile.ora'

Windows

Set a Database to Use the SPFILE

If you have created a service with the oradim command pointing to the pfile and you want to use the spfile instead:

Create an SPFILE from your PFILE.
Edit the registry in HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/OracleHome
Remove the key ORA_<OracleSID>_PFILE if it exists.
Bounce the database.

<- System