Changing Oracle Parameter

There are 2 ways of changing Oracle parameter, using SPFILE or PFILE. First of all, you need to check which parameter file is being used by your Oracle database. You can check using below command,

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
     FROM sys.v_$parameter WHERE name = 'spfile';

Init F
------
SPFILE

Change Parameter using PFILE

1. Go to below directory and you will find the init.ora file.

cd $ORACLE_HOME/dbs

2. Please make backup of the file before start editing. Then, you can edit the init.ora file using VI editor. Once done, you can save and restart the Oracle.

vi init.ora

Change Parameter using SPFILE

1. Using the ALTER command to update the server parameter file.

ALTER SYSTEM SET PARAMETER=VALUE SCOPE=SPFILE | MEMORY | BOTH

Change parameter from SPFILE to PFILE

1. Convert SPFILE to PFILE.

SQL> create pfile from spfile;

File created.

2. Shutdown the system. Update the parameter in init.ora file.

3. Convert back PFILE to SPFILE.

SQL> create spfile from pfile;

File created.

4. Start the database.