Oracle Database Spfile learning Summary

Create SPFILE

By default, ORACLE uses PFILE to start the database. SPFILE must be created by PFILE. The newly created SPFILE will take effect the next time the database is started. create spfile requires SYSDBA or SYSOPER permissions:

Create spfile [= 'spfilename'] from pfile = [= 'pfilename'];

Example:

Create spfile from pfile;

Use spfile

Restart the database and run the startup command. Oralce searches the parameter file a. spfile $ {ORACLE_SID}. ora in the default directory in the following order.

Default directory UNIX: $ {ORACLE_HOME}/dbs/

NT: $ {ORACLE_HOME} \ database

B. spfile. ora

Default directory UNIX: $ {ORACLE_HOME}/dbs/

NT: $ {ORACLE_HOME} \ database

C. init $ {ORACLESID}. ora

Default directory UNIX: $ {ORACLE_HOME}/dbs/

NT: $ {ORACLE_HOME} \ database or $ {ORACLE_HOME} \ admin \ db_name \ pfile

After the spfile is created and the database is restarted, Oracle searches the preceding directories in sequence and the spfile takes effect automatically.

Modify parameters

You can use alter system or import and export the SPFILE content.

Alter system adds a new option: SCOPE. The SCOPE parameter has three optional values: MEMORY, SPFILE, and BOTH.

MEMORY: only changes the running status of the current instance.

SPFILE: only modify the SPFILE settings.

BOTH: Change the instance and SPFILE

Whether spfile is used

1. query the v $ parameter dynamic view. If the following query returns a null value, you are using pfile.

2. Alternatively, you can use the SHOW command to display parameter settings. If the value column of the following result returns a null value, you are using pfile:

3. query the v $ spparameter view. If the following query returns 0, it means you are using pfile. Otherwise, it means you are using spfile:

Export the SPFILE File

The SPFILE file can be exported as a text file. You can add parameters to the SPFILE during the export and creation processes.

Create pfile = '/home/oracle/initxu. ora' from spfile