How to Remove a Parameter from SPFILE in Oracle

June 24, 2020

You can remove or delete/reset spfile parameter in Oracle Database using alter system reset command. After changing the parameter you need to restart the database.

Since spfile is a binary file you can’t open and change it’s values using vi editor in Linux. But if you are using pfile then you can easily modify and remove any parameter using text vi editor.

Steps to Remove Parameter from SPFILE in Oracle

How to Find Out the size of your Oracle Database

STEP 1. Login to the database and check the existing parameter value before removing. You can capture the values for your reference.

SQL> show parameter parameter_name 

Example:
SQL> show parameter olap_page_pool_size
NAME TYPE VALUE
olap_page_pool_size big integer 4M

STEP 2: Remove the spfile parameter values.

SQL> alter system reset parameter_name scope=spfile;

In RAC:
SQL> alter system reset parameter_name scope=spfile sid='*';

Example:
SQL> alter system reset olap_page_pool_size scope=spfile;
System altered.

STEP 3: Restart the database to make changes into spfile.

$ sqlplus / as sysdba
SQL> shut immediate;
SQL> startup

STEP 4: Check the updated parameter values.

SQL> show parameter parameter_name
Reclaiming Unused Space in Datafiles

Must Read: