Skip to content

The SPFILE and PFILE Initialization Parameter Files in Oracle 10g

February 3, 2008

Somebody from LinkedIN was asking about the difference between SPFILE and PFILE in Oracle 10g. The following is a bit of explanation about the two initialization files.

The default initialization parameter file used by Oracle 10g is the SPFILE. The SPFILE (also known as the “Server Binary Parameter File” or simply “Binary Parameter File”) is the binary form of your parameter file. The SPFILE, being a binary file, cannot be edited. If you need to do any changes on your initialization parameters for any reasons, you can only do the changes on your PFILE, which is the text form of your parameter file. Please do remember that you must explicitly declare use of the PFILE. You can do that by the command:

[Suppose your database is called MarlonDB]

STARTUP PFILE=’C:\oracle\product\10.2.0\admin\MarlonDB\initMarlonDB01.ora’

If you use your SPFILE to initialize the database, your command is simply:

STARTUP

Take note that if your database has no existing SPFILE and you did not explicitly declare use of a PFILE, you will get an error. To create a SPFILE from your PFILE, use this command:

CREATE SPFILE=’C:\oracle\product\10.2.0\db_1\database\spfileMarlonDB01.ora’ FROM PFILE=’C:\oracle\product\10.2.0\admin\MarlonDB\initMarlonDB01.ora’;

Similarly, you can create your PFILE from the SPFILE with this command:

CREATE PFILE=’C:\oracle\product\10.2.0\admin\MarlonDB\initMarlonDB01.ora’ FROM SPFILE=’C:\oracle\product\10.2.0\db_1\database\spfileMarlonDB01.ora’;

In summary, PFILE is the text form of the initialization parameter file. You can edit the PFILE and you must explicitly declare use of the PFILE when you are starting up the database with it. The SPFILE, on the other hand, cannot be edited. You do not need to explicitly declare use of it on startup because this is the default initialization file used by Oracle 10g.

-Marlon Ribunal (mribunal[AT]gmail[dot]com)

kick it on DotNetKicks.com

Advertisements
2 Comments leave one →
  1. Kuldeep permalink
    November 28, 2011 12:42 AM

    Thanks Marlon . it really helped me.

  2. enny permalink
    September 8, 2008 4:51 AM

    thanks alot but the details of parameterfile is yet to be analysed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives


  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: