oracle查看pfile路径,如保查看当前oracle启动用的是pfile还是spfile

Oracle 9i以后启动的时候默认使用的初始化文件是spfile,但是如何判断数据库启动是使用spfile还是pfile初始化文件.可以以下方法,

1、show parameter spfile

2、show parameter pfile

3、看v$spparameter视图

[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