Oracle Server parameters allow you to modify many aspects of the Oracle server (full list).
To see the current value of any parameter:
select name,value
from v$parameter
where name='Some_Parameter';
Use the ALTER SYSTEM command to set parameters:
ALTER SYSTEM set parameter = value SCOPE = MEMORY;
ALTER SYSTEM set parameter = value SCOPE = SPfile;
ALTER SYSTEM set parameter = value SCOPE = BOTH;-- MEMORY - This affects the database now; but will not remain after a restart.
-- SPfile - This does not change the instance immediately, but will modify the SPfile takes effect after a restart.
-- BOTH - change both the current instance and the spfile.Some parameters can be modified immediately with ALTER SYSTEM, some can only be modified for a single session with ALTER SESSION. Static parameters must be modified with scope=SPfile
For string parameters, setting to an empty string will restore the default.
ALTER SYSTEM SET parameter = '' scope=SPfile;For any parameter the RESET option will restore the default.
ALTER SYSTEM RESET parameter scope=SPfile sid='*' ;note
When resetting a parameter, you must specify sid=mySid or sid='*' even for non-RAC instances.
In Oracle9i and above, the SPfile is stored in a binary format on the server. You no longer need to have a local copy to start the database remotely. This also means that changes made via ALTER SYSTEM will persist across server restarts - without any need to remember to edit a separate init.ora file.
A Pfile (old style text init.ORA) can be created with the command:
CREATE Pfile = 'pfilename' FROM SPfile = 'SPfilename';An SPfile (binary) can be created with the command:
CREATE SPfile = 'SPfilename' FROM Pfile ='pfilename';
To create an SPfile, the database need not be started, the old Pfile format is largely for backwards compatibility with older releases.
If the system has both a Pfile and an SPfile, then Oracle will use the SPfile.
If the system has both a generic init.ora and an SID-specific parameter file, Oracle will use the SID-specific file.
SID specific:
spfileSID.ora or initSID.oraGeneric:
spfile.ora or init.oraThe location for all parameter files is:
ORACLE_HOME\database (Windows)
ORACLE_HOME/dbs (Unix/Linux)e.g.
C:\oracle\ora11\database\spfileMYDB.ora
C:\oracle\ora11\database\initMYDB.oraTo use an SPfile in a non standard location you can cheat by creating an init.ora containing just one line:
SPfile='C:\adminfiles\SPfilelive.ora'
For this to work - make sure you don’t also have an SPfile in the default location.
To startup a database with an SPfile:
SQL> startupTo startup a database with a Pfile, use the Pfile= option :
SQL> startup pfile=filename
List Deprecated Parameters with:
select name,value from v$parameter where isdeprecated='TRUE';
Examples
ALTER SYSTEM SET log_archive_format = "live%S.arc" SCOPE = SPfile;
ALTER SYSTEM SET shared_pool_size=64M scope=both
SELECT name, value from v$parameter where name = 'SPfile';
show parameter SPfile;
ALTER SYSTEM SET OPTIMIZER_MODE = "all_rows" SCOPE = SPfile;
'Internal' parameters (starting with _ ) should not be modified unless advised by Oracle Support.
The default value for many of these parameters does vary across Operating System platforms.
“Happiness, wealth, and success are by-products of goal setting; they cannot be the goal themselves” ~ Denis Waitley,The Joy of Working
Related
V$PARAMETER
V$SpPARAMETER
ALTER SYSTEM
Oracle Memory Architecture (Powerpoint)