?
Solved

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

Posted on 2004-08-10
4
Medium Priority
?
1,103 Views
Last Modified: 2010-08-05
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!
0
Comment
Question by:DalTXColtsFan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
Elena-S earned 200 total points
ID: 11762952
0
 
LVL 48

Expert Comment

by:schwertner
ID: 11763261
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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 11763296
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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 11810011
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.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question