Link to home
Start Free TrialLog in
Avatar of DalTXColtsFan
DalTXColtsFan

asked on

Oracle 9i - pfile and spfile - what's the difference, why use one instead of the other

subject line pretty much says it all - it seems that in Oracle 9i you can use either a pfile or an spfile to hold the parameters to start up a database instance.  What are the differences between the two file types, and what would be some reasons to use one or the other?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Elena-S
Elena-S

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of schwertner
SPFILE is a binary file and many entries can be changed dynamically - without shutdown the instance.

You can crerate PFILE from SPFILE using the statement:

SQL>create PFILE=c:\...\init1.ora from SPFILE

After editing and investigating some parameters you can create a SPFILE from PFILE

SQL>create SPFILE= d:\...\rdbms\spfile.ora  from pfile=c:\...\init1.ora

You can not directly use text editor to edit SPFILE.

You can start oracle using PFILE:

SQL>startup pfile=c:\...\init1.ora
The pfile is the traditional parameter file that Oracle has used (at least since Oracle5 days) to tune Oracle for a particualr database and hardware.  The biggest disadvantage of a pfile that is that most of the values are then not modifiable on-the-fly.  You need to edit the file, then shut down and restart the database for the changed values to take effect.

The big advantage of an spfile is that it supports changes to many (not all) of the values on-the-fly, so a database can be returned without having to shut it down.

With Oracle9i, the default is an spfile.  I use and strongly recommend using an spfile.
I see that there was an error in my earlier posting.  I intended that second paragraph to say that with a spfile, an Oracle9i database can be "retuned" (not "returned") without shutting it down.  Elena-S did direct you to a more complete answer.