Which parameter file my Oracle database using pifile or spfile

Some time you wonder why the start up parameter changes and not getting getting applied to my database.

Oracle database uses a parameter file when starting up. the parameter file could be spfile ( server parameter file or pfile. Normally pfile is used with STARTUP command to start the database. By default Oracle uses spfile to start the database.

sql> select value from sys.v$parameter where name = 'spfile'

sql> STARTUP pfile='C:\app\vikas\admin\orcl\pfile\initORCL.ora'

The spfile as the name suggest is the parameter file maintain by the oracle database server and should not be modified. any manual modification can corrupt it and database may not start further.

Modifying spfile
The spfile are modified by using ALTER Command.
ALTER system SET <parameter_name=value> SCOPE=MEMORY for eg.
ALTER SYSTEM SET open_cursor=400 SCOPE=spfile 

The other option for SCOPE are MEMORY (default) or FILE or BOTH

Converting SPFILE and PFILE.

SPFILE and PFILE are convertible. You can create any of it using other. use to following syntex
SQL> CREATE PFILE FROM SPFILE; 
SQL> CREATE SPFILE FROM PFILE;