2007/10/04

HowTo: Remove parameters from the spfile

Here's a question that comes up often:
  • How do you remove an SPFILE parameter (not change the value of, but actually purge it outright)?
Answer:
  • Use "ALTER SYSTEM RESET ..." (For database versions 9i and up)
Syntax:
  • ALTER SYSTEM RESET PARAMETER SID='SID|*'
  • ALTER SYSTEM RESET "_TRACE_FILES_PUBLIC" SCOPE=SPFILE SID='*';
NOTE: The "SID='SID|*'" argument is REQUIRED!
Posted by at
Labels: ,

1 comment:

NerdyRedneck Rob said...

This is an interesting note but maybe my old timey roots are showing but I just don't like SPFILEs.

The add no value whatsoever and they increase workload making you jump through hoops just like this one here to do what use to be quick and easy to do. Edit the file. Plus I had one burn me before. You have to start up the database to edit the spfile and if the spfile has a bad parm you can not even startup nomount. Then you are just plain old fashioned HOSED. Why paint your self in a corner?

I dump the spfile to init files as soon as I can. On windows you have to recreate the service but in UNIX the default is still $ORACLE_HOME/dbs/initsid.ora. Just the way I like it.

Surly old Curmudgoeon. :)

10/24/2007 11:55 AM

Post a Comment

Subscribe to: Post Comments (Atom)