You can change SPFILE settings with Oracle Enterprise Manager or by using the SET clause of the ALTER SYSTEM statement.
Note:
Modifying the SPFILE using tools other than Oracle Enterprise Manager or SQL*Plus can corrupt the file and prevent database startup. To repair the file, you might be required to create a PFILE and then regenerate the SPFILE.
The examples in this section appear in ASCII text although the SPFILE is a binary file. Assume that you start an instance with an SPFILE containing the following entries:
*.OPEN_CURSORS=500 prod1.OPEN_CURSORS=1000
The value before the period (.) in an SPFILE entry identifies the instance to which the particular parameter value belongs. When an asterisk (*) precedes the period, the value is applied to all instances that do not have a subsequent, individual value listed in the SPFILE.
For the instance with the Oracle system identifier (SID) prod1, the OPEN_CURSORS parameter is set to 1000 even though it has a database-wide setting of 500. Parameter file entries that have the asterisk (*) wildcard character only affect the instances without an instance-specific entry. This gives you control over parameter settings for instance prod1. These two types of settings can appear in any order in the parameter file.
If another DBA runs the following statement, then Oracle Database updates the setting on all instances except the instance with SID prod1:
ALTER SYSTEM SET OPEN_CURSORS=1500 sid='*' SCOPE=SPFILE;
The SPFILE now has the following entries for OPEN_CURSORS:
*.OPEN_CURSORS=1500 prod1.OPEN_CURSORS=1000
Run the following statement to reset OPEN_CURSORS to its default value for all instances except prod1:
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE;
The SPFILE now has just the following entry for prod1:
prod1.OPEN_CURSORS=1000
Run the following statement to reset the OPEN_CURSORS parameter to its default value for instance prod1 only:
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE SID='prod1';