Database parameter files Pfile and SPFile

Pfile and SPFILE are the parameters of the Oracle database, pfile format is a text file, you can directly edit the file modification, Oracle 9i can only use Pfile to specify and modify the database initialization parameters; The SPFILE format is a binary file, only SQL command Modification, Oracle started with the 9i and introduced SPFile.

1. How do I determine whether a database is pfile or SPFile?

A. Execute the following command, the result is non-null, using the SPFile

Show parameter SPFile;

B. Execute the following query, the result is non-null, using the SPFile

SELECT name, VALUE, from v$parameter WHERE NAME = ' spfile ';

C. Execute the following query, the result >0, with the SPFile

where true;

2. When using Pfile, modify the database initialization parameters, restart to take effect, very inconvenient;

when using SPFile, if you modify the static database initialization parameters, the restart will not take effect, if you modify the dynamic database initialization parameters, you can not restart, it will be effective. See details:

Parameter type	S cope= SPFile	S cope= Memory	S cope= both	Deferred
Static parameters	Yes, restart the server to take effect	No	No	No
Dynamic parameters (Issys_modifiable is immediate)	Yes, restart the server to take effect	Yes, immediate effect, restart service failure	Yes, effective immediately, restarting the server still works	OK
Dynamic parameters (Issys_modifiable is deferred)	OK	No	No	OK

3. creating PFILE and SPFILE

Create spfile[= ' spfile_name'] from pfile[='pfile_name']; Create pfile[='pfile_name' from spfile[='spfile_name  '];

4. Search Order of parameter files

A. Spfile<sid>.ora

Unix/linux default directory    $ORACLE _home/dbs/ Windows default directory        %oracle_home%\database

B. Spfile.ora

Unix/linux default directory   $ORACLE _home/dbs/ Windows default directory       %oracle_home%\database

C. Init<sid>.ora

Unix/linux default directory   $ORACLE _home/dbs/ Windows default directory       %oracle_home%\database

Database parameter files Pfile and SPFile