Oracle's PFILE

Oracle provides two different types of mutually exclusive parameter files that you can use, PFILE and SPFILE.

The PFILE is a text-based file.
PFILE contains number of database settings called parameters.

You can use the V$PARAMETER dynamic view to see the current setting of the different database parameters.

SQL> desc v$parameter

Name Null? Type

----------------------------------------- -------- -------------

NUM NUMBER

NAME VARCHAR2(80)

TYPE NUMBER

VALUE VARCHAR2(512)

DISPLAY_VALUE VARCHAR2(512)

ISDEFAULT VARCHAR2(9)

ISSES_MODIFIABLE VARCHAR2(5)

ISSYS_MODIFIABLE VARCHAR2(9)

ISINSTANCE_MODIFIABLE VARCHAR2(5)

ISMODIFIED VARCHAR2(10)

ISADJUSTED VARCHAR2(5)

ISDEPRECATED VARCHAR2(5)

DESCRIPTION VARCHAR2(255)

UPDATE_COMMENT VARCHAR2(255)

HASH NUMBER

SQL> select name, value from v$parameter where name = 'control_files';

NAME VALUE

-------------------- -----------------------------------------------

control_files C:\ORACLE\ORADATA\BOOKTST\BOOKTST\CONTROL01.CTL, C:\ORACLE

\ORADATA\BOOKTST\BOOKTST\CONTROL02.CTL, C:\ORACLE\ORADATA\

BOOKTST\BOOKTST\CONTROL03.CTL

SQL> show parameter control_files;

As the PFILE is text based, one can edit it in an editor like vi on UNIX or Notepad on Windows. When you have changed it, you need to make sure you save your changes to disk before you exit the editor. Also, make sure you save it as a plain text file, since some editors (like Microsoft Word) can save documents in special formats that Oracle would not be able to read.

Depending on which operating system you are running on, your PFILE is located by default in the ORACLE_HOME\database (usually the case on Windows) or ORACLE_HOME\dbs directory for most other platforms (we talked about where ORACLE_HOME was earlier in this book).

If you are using a PFILE, it takes on the form of initSID.ora, meaning the file will use the ORACLE_SID you defined when you created the database. If your SID is called testdb, the resulting PFILE should be called inittestdb.ora