How To Modify The Content Of An SPFILE Parameter File

How To Modify The Content Of An SPFILE Parameter File 

Do one of the following:

A. Use the ALTER SYSTEM command to modify the SPFILE currently in use.

To determine if an spfile is being used:
       
SQL> select value from v$parameter where name='spfile';

To modify the contents of the spfile:

SQL>  alter system set undo_management=auto scope=spfile;
System altered.
                 
This sets undo_management to auto.

SQL> alter system set resource_manager_plan='' scope=spfile;
System altered.

This sets the resource_manager_plan parameter to null.

NOTE:
=====
Parameters starting with an underscore must be enclosed with "" (double quotes) to prevent an ORA-00911: invalid character


B. Use the export method.

1. Export the SPFILE to a PFILE. The resulting PFILE will be an editable ASCII file:

Examples:

SQL> create pfile [= 'pfile_name' ] from spfile [= 'spfile_name'];

SQL> create pfile from spfile;

The PFILE is created in directory $ORACLE_HOME/dbs and named init<SID>.ora.

SQL> create pfile = '/testcasesADC/137483.1/initV1023U.ora' from spfile;

The PFILE is created as '/testcasesADC/137483.1/initV1023U.ora'

SQL> create pfile = '/testcasesADC/137483.1/initV1023U_test.ora' from spfile = '/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora';

The PFILE is created as '/testcasesADC/137483.1/initV1023U_test.ora' and the location of the spfile was explicit.
           

2. Modify the PFILE parameter value with a text editor and save the file:

undo_management=auto
resource_manager_plan=''

3. Recreate the SPFILE from the modified PFILE:

If the instance is down:
     
SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';
     
If the instance is up and you try to recreate the spfile the database was started with a naming conflicts occurs.  You will recieve error ORA-32002.

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';

ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
 
To overcome this error specify a different file name and then rename the spfile once the instance is shutdown:

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U_new.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';.