What is difference between spfile and init.ora file???

Questions by hasanvtu   answers by hasanvtu

Showing Answers 1 - 23 of 23 Answers

suresh sajwan

  • Nov 2nd, 2006

spfile are dynamic file.spfile can be created from pfile.you can open your database without spfile. init.ora is must to open the database.you can edit the init.ora file because it is a text file.whereas spfile cannot it reside on serverside.

  Was this answer useful?  Yes


  • Nov 3rd, 2006

init.ora spfile both are contains Database parameters info. Both are supported by oracle. Every database instance required either any one. If both are present first choice is given to spfile only. (be careful if both are present any changes made in pfile those are not recognized in next instance because it always first preference is given to spfile only).
init.ora saved in the format of ASCII
SPFILE saved in the format of binary.
init.ora info is read by oracle engine at the time of database instance starting time only any modification made in this, those are applicable when in the next startup only. But in spfile modifications (through alter system..... command) can applicable without restarting oracle database(restarting instance).
spfile we can create with the help of
create spfile from pfile.
as well as we can create pfile (init.ora) from spfile
create pfile from spfile.
In general 24x7 environment, spfile is only preferable, because parameters can change dynamically.
Any queries regarding this explanation pls. Give some reply.

Spfile are dynamic file.spfile can be created from pfile.You can open your database without spfile. init.ora is must to open the database.you can edit the init.ora file because it is a text file.whereas spfile cannot it reside on serverside.

init.ora as name suggested a initialisation file,it is a simple text file oracle reads init.ora and according to init.ora oracle allocates memory of db_block_size and buffer cache and some other initallisation parameter,it is having a different name as operating system changes.

  Was this answer useful?  Yes

manish hoiyani

  • Dec 8th, 2006

Spfile is a binary file ... we cannot be changed

where as pfile is init file   to start an oracle instance which allocates the memory resources for  the database....

we can edit the pfile,,,,  but we cannot do changes in spfile,,, we can create

spfile from pfile..

  Was this answer useful?  Yes


  • Jan 8th, 2007

SPfile is a binary file and init.ora file is a text file, We can edit the init.ora file but we can't edit the spfile, we can create spfile through init.ora file.

The use of spfile is--> we can directly startup the database, otherwise if we don't have spfile for every startup of database we have to write the path of init.ora file


  Was this answer useful?  Yes


  • Jan 23rd, 2007

If you are using spfile then you do not need to manually change parameters as in pfile, you can specify the scope for the parameter.

  Was this answer useful?  Yes


  • Jan 26th, 2007

Pfile1. text file2. changes take effect on the next startup3. initSID.oraSPfile1. binary file2. changes perisitent across startup and shutdown3. spfileSID.ora Only way to start db with spfile in non default location SPFILE = must beplaced in pfile

  Was this answer useful?  Yes


  • Aug 30th, 2011

Spfile is binaryfile and pfile is text file.we can create pfile manually,it creates automaticaly when we create database is dbca.pfile syntax is init.ora,spfile syntax is spfile.ora

  Was this answer useful?  Yes


  • Sep 1st, 2011

SPfile is a binary file that only Oracle can read it and understand it, whereas init.ora is text based file that can be understood by any user. Both these files contain the parameters that reflect the destinations, SGA memory when the Oracle instance got started up.

  Was this answer useful?  Yes

Spfile is a binary file.it's not human readable file.init.ora file is pfile is human readable file. we can retrieve pfile from spfile vice versa. using these statement create pfile from spfile and vice versa.these both file contains parameters of sga memory information.

  Was this answer useful?  Yes


  • Oct 11th, 2011

There are some parameters you can not change dynamically even you use SPFILE. For example: if you want to increase the no of processes then you need to alter system set processes= scope=spfile; then you need to bounce the server to take the new value effect

  Was this answer useful?  Yes


  • Mar 22nd, 2012

Hi Friends don't confuse too much take it as simple.
SPfile is server parameter file server made changes we cannot modify anything to it.
Pfile or init.ora file is a parameter file we can done the modifications. what ever changes we need for our database we can change it.
For Example: If you want to change the undo tablespace just mention it in the parameter file like.undo_tablespace=undo01.dbf

  Was this answer useful?  Yes


  • Mar 22nd, 2012

Using SPFILE feature, we can dynamically expand SGA without database shut down. It is not available with PFILE.

  Was this answer useful?  Yes


  • May 1st, 2012

Spfile server parameter file it is an substitute for pfile with the benefit of "make the changes in this spfile dynamically means if we made the changes in the spfile there is no need to restart of database" which is cant performed by the pfile (pfile needs restart after making changes in it)... Thank u..

  Was this answer useful?  Yes


  • May 11th, 2012

Using spfile,change the parameters dynamically.But in pfile,after change the parameters u must restart the database..
spfile is a binary file n it will automatically created with dbca utility(at db creation time).Pfile is a text file n it will edit by any text editor.

  Was this answer useful?  Yes


  • Aug 11th, 2012

SP file is more advantage than P file
SP File: SP file is available from above 9i versions.SP File is a server side file,Dynamic file,no need to restart when ever changes happen.u cant edit the SP file bcz chances to go corrupt.SP file modified with ALTER SYSTEM SET....SP file can be backup with RMAN but P file can not.SP file reduces human errors.

P FILE(init.ora) : P file available in before 9i versions.It is a client side and text file.we can edit P file using VI editor or note pad. Need to restart every time when ever changes happen.

  Was this answer useful?  Yes


  • Feb 14th, 2013

Sp file is static and p file is dynamic

  Was this answer useful?  Yes


  • Jun 1st, 2013

spfile provides dynamic mean of maintaining your initialization parameters.

  Was this answer useful?  Yes


  • Jan 22nd, 2014

spfile is in binary format or in encrypted type we cannot modify the values but pfile is in ASCII format we can change the values.

  Was this answer useful?  Yes


  • Feb 5th, 2014

spfile is in binary format we cannot edit but in init.ora file we can edit

  Was this answer useful?  Yes

syed fahad

  • Sep 13th, 2014

PFILE is a txt file and SPFILE is a binary(when u freshly create a database first database is read spfile after we open the database it will create pfile with the help of this command : create pfile from spfile)

  Was this answer useful?  Yes

Amitkumar S Solanki

  • Oct 17th, 2014

SPFile is dynamic file and PFile is static

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions