启动数据库可采用pfile或spfile两种参数文件类型。
sys@TEST0924> select name,value,isspecified from v$spparameter where name like '%sga%';
NAME VALUE ISSPEC
-------------------- -------------------- ------
sga_max_size FALSE
pre_page_sga FALSE
lock_sga FALSE
sga_target 0 TRUE
sys@TEST0924> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfiletest0924.o
ra
使用spfile启动时,sga_target 的isspecified为true,如果为false,则是使用pfile启动;
也可以通过 show parameter spfile这个方法,如果value有值,则说明使用spfile启动。如果没有值,则又pfile启动。
下面用pfile启动,然后再来查询这些
sys@TEST0924> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST0924> startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittest0924.ora';
ORACLE instance started.
Total System Global Area 3340451840 bytes
Fixed Size 2232960 bytes
Variable Size 3204451712 bytes
Database Buffers 117440512 bytes
Redo Buffers 16326656 bytes
Database mounted.
Database opened.
sys@TEST0924> select name,value,isspecified from v$spparameter where name like '%sga%';
NAME VALUE ISSPEC
-------------------- -------------------- ------
sga_max_size FALSE
pre_page_sga FALSE
lock_sga FALSE
sga_target FALSE
sys@TEST0924> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
通过以上对比,可值,图中是由spfile文件启动的。
但是如果启用了amm,即设置了MEMORY_TARGET,且SGA_TARGET设置为0,则sga_target的isspecified 值为FALSE。
如下:
15:11:25 SYS@db252>select name,value,isspecified from v$spparameter where name like '%sga%';
NAME VALUE ISSPEC
------------------------------ -------------------------------------------------- ------
sga_max_size FALSE
pre_page_sga FALSE
lock_sga FALSE
sga_target FALSE
Elapsed: 00:00:00.01
这时spfile参数是有值的:
15:14:49 SYS@db252>show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:INSTANTCLIENT_11_2DATABASE
SPFILEPCRP.ORA
15:15:06 SYS@db252>show parameter target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 1248M
memory_target big integer 1248M
parallel_servers_target integer 8
pga_aggregate_target big integer 0
sga_target big integer 0
15:15:39 SYS@db252>