Solved

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

Posted on 2004-08-10
4
1,099 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 50 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

734 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