Scribd
Upload
111 views2 pages

SPFILE and PFILE Management Guide

There are two types of Oracle database parameter files - a static pfile text file and a server-side binary spfile. A pfile must be edited with a text editor while an spfile can only be modified with ALTER SYSTEM commands. Parameter values can be changed dynamically in memory or permanently in the file by editing the appropriate file. Parameters can also be set to take effect for the current instance, next startup, or both using the SCOPE parameter. Pfiles and spfiles can be converted between each other.

Uploaded by

Raghavendra Prabhu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
111 views2 pages

SPFILE and PFILE Management Guide

There are two types of Oracle database parameter files - a static pfile text file and a server-side binary spfile. A pfile must be edited with a text editor while an spfile can only be modified with ALTER SYSTEM commands. Parameter values can be changed dynamically in memory or permanently in the file by editing the appropriate file. Parameters can also be set to take effect for the current instance, next startup, or both using the SCOPE parameter. Pfiles and spfiles can be converted between each other.

Uploaded by

Raghavendra Prabhu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

Parameter File Management

There are two type of parameter file:- pfile and spfile


Pfile pfile is a static, client-side text file that must be updated with a standard text
editor like "notepad" or "vi"
SPfile An SPFILE (Server Parameter File), on the other hand, is a persistent server-
side binary file that can only be modified with the "ALTER SYSTEM" command.
Note : Default location for both parameter files is $ORACLE_HOME/dbs

To change the parameter value in pfile:-


Dynamic parameter (sga_target)
Sql> alter system set sga_target=3g;
The above parameter value gets changed only in memory, to permanently change do the
following:
1. open the parameter file.
2. then specify the value in parameter file
Exp.
$cd $ORACLE_HOME/dbs
$vi inittest.ora
Sga_target=3g
:wq

The static parameter (sga_max_size) can be changed only in parameter file


And to make the value effective, the instance be bounced back
How to change parameter value in spfile

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:


Memory :- value is Set for the current instance only.
Spfile :- update the SPFILE, the parameter will take effect with next database startup
Both :- value is affect the current instance and persist to the SPFILE.

Dynamic parameter:
SQL>Alter system set sga_target=300m;
SQL>Alter system set sga_target=500m scope=memory;
SQL>Alter system set sga_target=500m scope=both;
Static parameter
SQL>Alter system set sga_max_size=1g scope=spfile
SQL>Alter system set sga_max_size=500m comment=max size is 1GB scope=spfile;

Converting between PFILES and SPFILES:


Execute the following commands from a user with SYSDBA or SYSOPER privileges:

SQL> CREATE PFILE FROM SPFILE;


SQL> CREATE SPFILE FROM PFILE;

Presented by Mr. SUNNY


+91-8105030020
Views for Parameter File Mgmt:
-v$parameter, v$parameter2, v$spparameter

Presented by Mr. SUNNY


+91-8105030020

You might also like