Files - Expert Oracle Database Architecture - page 85

VALUE UPDATE_COMMENT
-------------------- ----------------------------------------
536870912 Changed 14-aug-2013, AWR recommendation

Unsetting Values in SPFILEs
The next question that arises is, how do we unset a value that we previously set. In other words, we don't want that
parameter setting in our SPFILE anymore. Since we can't edit the file using a text editor, how do we accomplish that?
This, too, is done via the ALTER SYSTEM command, but using the RESET clause:

Alter system reset parameter <scope=memory|spfile|both> sid='sid|*'

So, for example, if we wanted to remove the sort_area_size parameter, to allow it to assume the default value we
specified previously, we could do so as follows:

EODA@ORA12CR1> alter system reset sort_area_size scope=spfile ;
System altered.

■ in prior releases, specifically in Oracle 10 g release 2 and earlier, the SID= clause was not optional as it is
now. in those releases, you'd include SID='*' on the end of the ALTER SYSTEM command to reset the parameter for all
instances in the SPFILE . Or you'd specify SID='some_sid' to reset it for a single instance.

Note

The sort_area_size is removed from the SPFILE , which you can verify by issuing the following:

EODA@ORA12CR1> connect / as sysoper;
Connected.

PUBLIC@ORA12CR1> create pfile='/tmp/pfile.tst' from spfile;
File created.

You can then review the contents of /tmp/pfile.tst , which will be generated on the database server. You'll find
the sort_area_size parameter does not exist in the parameter file anymore.

Creating PFILEs from SPFILEs
The CREATE PFILE...FROM SPFILE command we just saw is the opposite of CREATE SPFILE . It takes the binary SPFILE
and creates a plain text file from it—one that can be edited in any text editor and subsequently used to start up the
database. You might use this command for at least two things on a regular basis:

• To create a one-time parameter file with some special settings, to start up the database for

maintenance. So, you'd issue CREATE PFILE...FROM SPFILE and edit the resulting text PFILE ,
modifying the required settings. You'd then start the database, using the PFILE=<FILENAME>
option to specify your PFILE instead of the SPFILE . After you finished, you'd just start up
normally without specifying the PFILE=<FILENAME> , and the database would use the SPFILE .