Solved

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

Posted on 2004-08-10
4
1,094 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
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
Elena-S earned 50 total points
ID: 11762952
0
 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
Export BLOB data from Oracle 10g 4 35
Oracle Next Available Number 2 32
'G_F01' is not a procedure or is undefined 3 12
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 …
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.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now