檢視ORACLE啟動時使用spfile還是pfile

Oracle 9i以後啟動的時候預設使用的初始化檔案是spfile,但是如何判斷資料庫啟動是使用spfile還是pfile初始化檔案.可以用3種方法,
1、show parameter spfile
2、show parameter pfile
3、看v$spparameter檢視[@more@][oracle@Rhel4 dbs]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jun 18 07:30:49 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> conn /as sysdba;
Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 306186368 bytes
Database Buffers 109051904 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.

SQL> col name format a10;
SQL> col type format a10;
SQL> col value format a30;

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string /home/oracle/oracle_11g/product/11.1.0/db_1/dbs/spfileorcl.ora

SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string /home/oracle/oracle_11g/product/11.1.0/db_1/dbs/spfileorcl.ora

發現show parameter spfile和show parameter pfile結果一樣。

SQL> select decode(count(*),1,'spfile','pfile') from v$spparameter where rownum=1 and isspecified = 'TRUE';

DECODE(COUNT
------------
spfile

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

預設使用的是spfile，現在指定啟動時讀取的初始化檔案，這樣指定的pfile會覆蓋查詢初始化檔案的優先順序.
SQL> create pfile from spfile;

File created.

SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.oraORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 306186368 bytes
Database Buffers 109051904 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string

SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
spfile string

show parameter spfile和show parameter pfile結果仍然一樣，此時spfile的值為NULL了。

SQL> select decode(count(*),1,'spfile','pfile') from v$spparameter where rownum=1 and isspecified = 'TRUE';

DECODE(COUNT
------------
pfile

from:http://blog.csdn.net/test_me/article/details/5678316