ORACLE查询初始化参数的方法

今天发现了几篇yangtingkun写的关于查询oracle初始化参数的博客，写的十分全面和详细。果断的转载学习！

查询初始化参数的方法（一）

出处: http://yangtingkun.itpub.net/post/468/484669
---------------------------------------------------------------

查询初始化参数的方法很多，比如SHOW PARAMETER，或查询V$PARAMETER等，这里简单总结一下。

这一篇简单介绍多种查询初始化参数的方法。

首先初始化参数的设置的方法就有很多种：可以通过ALTER SYSTEM SET SCOPE = MEMORY的方式仅设置内存的修改，也可以通过ALTER SYSTEM SET SCOPE = SPFILE只修改SPFILE的值，当然也可以同时修改SPFILE和MEMORY中的设置。还有一种初始化参数可以设置延迟生效，也就是说这个修改只对以后连接到数据库的会话生效，而对当前会话以及其他已经连接到Oracle的会话不会生效。如果再考虑RAC环境，数据库中存在多个启动实例的情况，那么查询数据库初始化参数就更加复杂了。

所幸Oracle提供的查询初始化参数方法也有很多种：SHOW PARAMETERS、SHOW SPPARAMETERS、CREATE PFILE、V$PARAMETER、V$PARAMETER2、V$SYSTEM_PARAMETER、V$SYSTEM_PARAMETER2、V$SPPARAMETER。

SHOW PARAMETERS是SQLPLUS工具提供的查询初始化参数的方法，这个方法查询的初始化参数是当前会话生效的初始化参数。

SHOW SPPARAMETERS也是SQLPLUS工具提供的方法，用来查询当前会话生效的SPFILE参数包含的初始化参数。这个命令在11g以后sqlplus版本中有效。

CREATE PFILE命令不像其他方法那样直观，这种方法可以将SPFILE中或当前内存中设置的初始化文件保存到PFILE文件中，然后就可以通过文本编辑工具直观的看到SPFILE中或当前内存中设置了哪些初始化参数。虽然这种方法看上去比较麻烦，但是这种方法列出的参数都是用户设置的参数，所有默认值的参数并不会列出来，因此看到的结果要比其他方法直观得多。在11g以后的版本允许CREATE PFILE FROM MEMORY。

V$PARAMETER视图提供了当前会话可见的初始化参数的设置，如果像查询RAC数据库的所有实例的设置，可以查询GV$PARAMETER视图。

V$PARAMETER2视图和V$PARAMETER差不多，唯一的区别在于对于包括值的初始化参数，从这个视图会返回多条记录，每条记录对应一个值。同样的，对于RAC环境可以查询GV$PARAMETER2视图。

V$SYSTEM_PARAMETER视图记录当前实例生效的初始化参数设置。注意这里是实例生效而不是会话生效。同样，GV$SYSTEM_PARAMETER则包含了所有实例生效的初始化参数信息。

V$SYSTEM_PARAMETER2视图与V$SYSTEM_PARAMETER视图的关系和V$PARAMETER2视图与V$PARAMETER视图的关系一样，都是对于包含多个值的参数采用了分行处理的方式。

V$SPPARAMETER记录了来自SPFILE文件中初始化参数。如果参数在SPFILE文件中没有设置，则字段ISSPECIFIED对应的值为FALSE。同样可以查询GVSPPARAMETER参数来显示RAC环境所有实例的设置。

首先看一下V$PARAMETER和V$PARAMETER2的区别，这个区别同样适用于V$SYSTEM_PARAMETER和V$SYSTEM_PARAMETER2：

SQL> select name,value from v$parameter
2 minus
3 select name,value from v$parameter2;

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
control_files
D:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/CONTROL01.CTL, D:/ORACLE/PRODUCT/10.2.0/OR
ADATA/TEST/CONTROL02.CTL, D:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/CONTROL03.CTL

db_file_name_convert
D:/oracle/product/10.2.0/oradata/testdg, D:/oracle/product/10.2.0/oradata/test

log_file_name_convert
D:/oracle/product/10.2.0/oradata/testdg, D:/oracle/product/10.2.0/oradata/test

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------

SQL> select name,value from v$parameter2
2 minus
3 select name,value from v$parameter;

control_files
D:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/CONTROL02.CTL

control_files
D:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/CONTROL03.CTL

db_file_name_convert
D:/oracle/product/10.2.0/oradata/testdg

log_file_name_convert
D:/oracle/product/10.2.0/oradata/test

已选择7行。

SQL>

查询初始化参数的方法（二）

出处: http://yangtingkun.itpub.net/post/468/484729
---------------------------------------------------------------

上一篇文章介绍了很多种查询初始化参数的方法，而且说明了V$PARAMETER视图和V$PARAMETER2视图的区别。

这里看看V$PARAMETER和V$SYSTEM_PARAMETER视图的区别。

一般在查询初始化参数的时候都习惯性的使用SHOW PARAMETER，也就是查询V$PARAMETER视图，但是有些时候查询这个视图得到的结果并不准确：

SQL> show parameter query_rewrite_enabled

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
query_rewrite_enabled string
TRUE
SQL> select name,value from v$parameter where name='query_rewrite_enabled';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
query_rewrite_enabled
TRUE

SQL> select name,value from v$system_parameter where name='query_rewrite_enabled';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
query_rewrite_enabled
TRUE

如果在会话级修改query_rewrite_enabled这个初始化参数：

SQL> alter session set query_rewrite_enabled=false;

会话已更改。

SQL> show parameter query_rewrite_enabled;

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
query_rewrite_enabled string
FALSE
SQL> select name,value from v$parameter where name ='query_rewrite_enabled';

SQL> select name,value from v$system_parameter where name ='query_rewrite_enabled';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
query_rewrite_enabled
TRUE

SQL>

可以看到，show parameter和查询v$parameter视图的结果都是FALSE，而刚才做的修改只是会话级，并没有修改系统的初始化参数。在上一篇描述这两个视图的时候就提到了，V$PARAMETER视图反映的是初始化参数在当前会话中生效的值，而V$SYSTEM_PARAMETER反映的才是实例级上的初始化参数。

在来看看延迟参数修改的情况：

SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';

NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on

SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';

NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on

SQL> alter system set recyclebin = off deferred scope = memory;

系统已更改。

SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';

NAME VALUE
---------------------------------------- ------------------------------------
recyclebin on

SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';

NAME VALUE
---------------------------------------- ------------------------------------
recyclebin OFF

结果和前面的恰好反过来，v$parameter视图中的结果没有发生变化，而v$system_parameter视图的结果变成了OFF。这是因为延迟修改对数据库中当前存在的会话不生效，因此反映当前会话情况的v$parameter视图结果不变，而对于系统而言，初始化参数已经改变，而且所有新建会话的参数也会改变，所以v$system_parameter视图的结果发生了改变。

重新连接session

SQL> select name, value
2 from v$parameter
3 where name = 'recyclebin';

NAME VALUE
---------------------------------------- ---------------------------
recyclebin OFF

SQL> select name, value
2 from v$system_parameter
3 where name = 'recyclebin';

NAME VALUE
---------------------------------------- ---------------------------
recyclebin OFF

根据这两个例子，利用V$PARAMETER视图获取系统的启动初始化参数是不准确的，应该从V$SYSTEM_PARAMETER视图来获取。

查询初始化参数的方法（三）

出处: http://yangtingkun.itpub.net/post/468/485623

上一篇介绍了V$SYSTEM_PARAMETER和V$PARAMETER视图之间的区别，这篇主要讨论RAC环境下初始化参数的查询。

前文已经提到，使用SHOW PARAMETER查询，看到的是当前会话可以看到的初始化参数，那么这个参数导致是全局设置还是当前实例设置的，是从这个命令中看不到的。

虽然Oracle提供了GV$开头的初始化参数，可以用来查询两个实例上的设置，但是情况并不是这么简单的。

一个简单的例子：

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
SQL> alter system set open_cursors = 500 scope = both sid = 'test1';

系统已更改。

SQL> set instance test2
Oracle Database 11g Release 11.1.0.0.0 - Production
SQL> conn sys as sysdba输入口令: 已连接。
SQL> alter system set open_cursors = 400 scope = both sid = 'test2';

系统已更改。

SQL> disc从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options 断开
SQL> set instance local
Oracle Database 11g Release 11.1.0.0.0 - Production
SQL> conn / as sysdba已连接。

现在来看看不同的查询方法得到的结果：

SQL> select name, value
2 from v$parameter
3 where name = 'open_cursors';

NAME VALUE
------------------------------ --------------------------------------------------
open_cursors 500

SQL> select inst_id, name, value
2 from gv$parameter
3 where name = 'open_cursors';

INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 open_cursors 500
2 open_cursors 400

SQL> show parameter open_cursors

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 500
SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* open_cursors 300
test1 open_cursors 500
test2 open_cursors 400

SQL> show spparameter open_cursors

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* open_cursors integer 300
test2 open_cursors integer 400
test1 open_cursors integer 500

似乎除了看不到全局设置外，GV$PARAMETER参数和V$SPPARAMETER没有什么不同，其实不然，如果alter system set的时候只修改了spfile或只修改了memory参数，结果就会不同：

SQL> alter system set open_cursors = 600 scope = memory sid = 'test1';

系统已更改。

SQL> alter system set open_cursors = 700 scope = spfile sid = 'test2';

系统已更改。

SQL> select name, value
2 from v$parameter
3 where name = 'open_cursors';

NAME VALUE
------------------------------ --------------------------------------------------
open_cursors 600

SQL> select inst_id, name, value
2 from gv$parameter
3 where name = 'open_cursors';

INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 open_cursors 600
2 open_cursors 400

SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* open_cursors 300
test1 open_cursors 500
test2 open_cursors 700

从上面的对比就可以看出，通过GV$视图访问的结果和SPFILE中包含的信息其实是两回事。

除了上面介绍的几种视图之外，CREATE PFILE其实也是一个不错的选择，在10g以前只能CREATE PFILE FROM SPFILE，得到的结果类似于对VSPPARAMETER视图的查询，而11g增加了CREATE PFILE FROM MEMORY选项，这个得到的结果类似于从GV$SYSTEM_PARAMETER视图获取的查询。

查询初始化参数的方法（四）

出处: http://yangtingkun.itpub.net/post/468/485664
---------------------------------------------------------------

前一篇文章介绍了V$SPPARAMETER参数，也说明了V$SPPARAMETER视图中的信息与GV$PARAMETER视图中的区别。

其实这里还有一个问题，就是GV$SPPARAMETER是否有意义。因为V$SPPARAMETER参数本身就包含了SID列，SPFILE中本身就包含了所有实例的设置，那么查询GV$SPPARAMETER视图是否就意义不大呢，其实不然。

因为RAC的各个节点可以使用统一的SPFILE启动，同样也可以选择不同的SPFILE来进行启动，这时GV$SPPARAMETER视图中获取结果，才是真正各个实例SPFILE中设置的结果。

这样说比较难以理解，看一个简单的例子：

SQL> select inst_id, name, value
2 from gv$system_parameter
3 where name = 'open_cursors';

INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 open_cursors 600
2 open_cursors 400

SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* open_cursors 300
test1 open_cursors 500
test2 open_cursors 700

SQL> select inst_id, sid, name, value
2 from gv$spparameter
3 where name = 'open_cursors';

INST_ID SID NAME VALUE
---------- ---------- ------------------------------ ------------------------------------
1 * open_cursors 300
1 test1 open_cursors 500
1 test2 open_cursors 700
2 * open_cursors 300
2 test1 open_cursors 500
2 test2 open_cursors 700

已选择6行。

SQL> select inst_id, name, value
2 from gv$system_parameter
3 where name = 'spfile';

INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 spfile +DATA/test/spfiletest.ora
2 spfile +DATA/test/spfiletest.ora

下面里面内存中参数来创建SPFILE，并利用新建的SPFILE来启动当前实例：

SQL> create spfile='/export/home/oracle/spfiletest1.ora' from memory;

文件已创建。

SQL> host
$ vi /export/home/oracle/inittest1.ora
"/export/home/oracle/inittest1.ora" [New file]
spfile=/export/home/oracle/spfiletest1.ora

"/export/home/oracle/inittest1.ora" [New file] 2 lines, 44 characters
$ exit

SQL> shutdown immediate数据库已经关闭。已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile=/export/home/oracle/inittest1.ora
ORACLE 例程已经启动。

Total System Global Area 776896512 bytes
Fixed Size 2098776 bytes
Variable Size 246069672 bytes
Database Buffers 524288000 bytes
Redo Buffers 4440064 bytes数据库装载完毕。数据库已经打开。

下面检查spfile中的设置：

SQL> select inst_id, name, value
2 from gv$system_parameter
3 where name = 'spfile';

INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 spfile /export/home/oracle/spfiletest1.ora
2 spfile +DATA/test/spfiletest.ora

SQL> select inst_id, name, value
2 from gv$system_parameter
3 where name = 'open_cursors';

INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 open_cursors 600
2 open_cursors 400

SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
test1 open_cursors 600
test2 open_cursors 400

SQL> select inst_id, sid, name, value
2 from gv$spparameter
3 where name = 'open_cursors';

INST_ID SID NAME VALUE
---------- ---------- ------------------------------ --------------------------------
2 * open_cursors 300
2 test1 open_cursors 500
2 test2 open_cursors 700
1 test1 open_cursors 600
1 test2 open_cursors 400

可以看到，由于两个实例采用了不同的SPFILE，导致两个实例上设置的对方实例的初始化参数值，与对方实例上当前设置值不符。

在上面的例子中，两个实例上真正的参数设置查询方式为：

SQL> select inst_id, sid, name, value
2 from gv$spparameter
3 where name = 'open_cursors'
4 and substr(sid, -1) = to_char(inst_id);

INST_ID SID NAME VALUE
---------- ---------- ------------------------------ -----------------------------------
2 test2 open_cursors 700
1 test1 open_cursors 600

查询初始化参数的方法（五）

出处: http://yangtingkun.itpub.net/post/468/485719
---------------------------------------------------------------

判断数据库启动是否启用了SPFILE很简单，只需要通过SHOW PARAMETER SPFILE命令就可以看到：

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /export/home/oracle/spfiletest1.ora

但是判断一个初始化参数是否由SPFILE设置，并不是那么容易。

首先V$SPPARAMETER里面包含了所有可以设置初始化参数的记录：

SQL> select sid, count(*)
2 from v$spparameter
3 group by sid;

SID COUNT(*)
---------- ----------
* 391
test2 6
test1 6

不过这个问题并不难解决，对于通过SPFILE指定的参数，V$SPPARAMETER视图中的ISSPECIFIED列的值为TRUE，如果在SPFILE中没有指定，则这个值为FALSE。

SQL> select isspecified, count(*)
2 from v$spparameter
3 group by isspecified;

ISSPEC COUNT(*)
------ ----------
TRUE 144
FALSE 259

SQL> select sid, name, value
2 from v$spparameter
3 where isspecified = 'TRUE'
4 and name not like '_%' escape '';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* processes 150
* resource_manager_plan
* sga_target 775946240
* control_files +DATA/test/controlfile/current.529.684067899
* db_block_size 8192
* compatible 11.1.0.0.0
* log_archive_config
* log_archive_dest_1 LOCATION=/data/oracle/oradata/test/archivelog
* log_buffer 4197376
* cluster_database TRUE
* cluster_database_instances 3
* db_create_file_dest +DATA
test1 thread 1
test2 thread 2
test1 undo_tablespace UNDOTBS1
test2 undo_tablespace UNDOTBS2
test1 instance_number 1
test2 instance_number 2
test1 remote_login_passwordfile SHARED
test2 remote_login_passwordfile EXCLUSIVE
* db_domain
* plsql_warnings DISABLE:ALL
* result_cache_max_size 3899392
test1 core_dump_dest /data/oracle/diag/rdbms/test/test1/cdump
test2 core_dump_dest /data/oracle/diag/rdbms/test/test2/cdump
* audit_file_dest /data/oracle/admin/test/adump
* audit_trail DB
* db_name test
test2 open_cursors 400
* open_cursors 500
* optimizer_mode ALL_ROWS
* query_rewrite_enabled TRUE
* pga_aggregate_target 256901120
* optimizer_dynamic_sampling 2
* skip_unusable_indexes TRUE
* diagnostic_dest /data/oracle

已选择36行。

上面就列出了SPFILE中所有指定的参数，不过并不以为SPFILE中设置的参数就一定会生效。

比如在使用PFILE指定SPFILE参数的方式启动时，PFILE里面可以在SPFILE之前指定实例级的初始化参数，用来覆盖SPFILE里相同的数据库级的初始化参数设置。

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
test1

SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
test1 open_cursors 600
test2 open_cursors 400

SQL> alter system reset open_cursors scope = spfile sid = 'test1';

系统已更改。

SQL> alter system set open_cursors = 500;

系统已更改。

SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
test2 open_cursors 400
* open_cursors 500

下面通过修改inittest1.ora参数，在spfile参数前面，加上open_cursors参数：

SQL> host vi /export/home/oracle/inittest1.ora
test1.open_cursors=1000
spfile=/export/home/oracle/spfiletest1.ora

"/export/home/oracle/inittest1.ora" 3 lines, 68 characters

SQL> shutdown immediate数据库已经关闭。已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile=/export/home/oracle/inittest1.ora
ORACLE 例程已经启动。

SQL> select name, value
2 from v$system_parameter
3 where name = 'open_cursors';

NAME VALUE
------------------------------ --------------------------------------------------
open_cursors 1000

SQL> select sid, name, value, isspecified
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE ISSPEC
---------- ------------------------------ ---------------------------------------- ------
test2 open_cursors 400 TRUE
* open_cursors 500 TRUE

检查当前的数据库设置可以发现，虽然当前SPFILE中open_cursors是明确设置的，但是由于pfile中设置了实例级的初始化参数覆盖了数据库级的初始化参数，导致系统当前的参数设置和SPFILE中的设置并不相同。

查询初始化参数的方法（六）

出处: http://yangtingkun.itpub.net/post/468/485756
---------------------------------------------------------------

前面介绍了很多种方法，这些方法都是在数据库中查询初始化参数的设置。其实还有一种方法更加的一目了然，就是CREATE PFILE的方式：

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/test/spfiletest.ora
SQL> create pfile = '/export/home/oracle/inittest1.ora' from spfile;

文件已创建。

SQL> host more /export/home/oracle/inittest1.ora
test2.__db_cache_size=541065216
test1.__db_cache_size=524288000
test2.__java_pool_size=4194304
test1.__java_pool_size=4194304
test2.__large_pool_size=4194304
test1.__large_pool_size=4194304
test1.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
test2.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
test2.__pga_aggregate_target=260046848
test1.__pga_aggregate_target=260046848
test2.__sga_target=775946240
test1.__sga_target=775946240
test2.__shared_io_pool_size=0
test1.__shared_io_pool_size=0
test2.__shared_pool_size=218103808
test1.__shared_pool_size=234881024
test2.__streams_pool_size=0
test1.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/test/adump'
*.audit_trail='db'
*.cluster_database=true
*.cluster_database_instances=3
*.compatible='11.1.0.0.0'
*.control_files='+DATA/test/controlfile/current.529.684067899'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='test'
*.diagnostic_dest='/data/oracle'
test1.instance_number=1
test2.instance_number=2
*.log_archive_config=''
*.log_archive_dest_1='LOCATION=/data/oracle/oradata/test/archivelog'
*.open_cursors=300
test1.open_cursors=500
test2.open_cursors=700
*.pga_aggregate_target=256901120
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
test1.remote_login_passwordfile='SHARED'
*.sga_target=772800512
test1.thread=1
test2.thread=2
*.undo_tablespace='UNDOTBS1'
test2.undo_tablespace='UNDOTBS2'

使用了这个语句，所有的SPFILE中设置的初始化参数设置都一目了然。

除了CREATE PFILE FROM SPFILE外，11g还增加了CREATE PFILE FROM MEMORY选项，使得用户可以直接从数据库当前生效的参数来生成PFILE文件，利用这个方法，就可以解决上一篇文章最后介绍的PFILE中设置的实例级参数覆盖SPFILE中数据库级参数的情况：

SQL> create pfile = '/export/home/oracle/inittest1.ora' from memory;

文件已创建。

SQL> host more /export/home/oracle/inittest1.ora
# Oracle init.ora parameter file generated by instance test1 on 06/12/2009 15:18:46
test1.__db_cache_size=500M
test2.__db_cache_size=516M
*.__java_pool_size=4M
*.__large_pool_size=4M
*.__oracle_base='/data/oracle' # ORACLE_BASE set from environment
*.__pga_aggregate_target=248M
*.__sga_target=740M
*.__shared_io_pool_size=0
test1.__shared_pool_size=224M
test2.__shared_pool_size=208M
*.__streams_pool_size=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._complex_view_merging=TRUE
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._dimension_skip_null=TRUE
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._ksb_restart_policy_times='0'
*._ksb_restart_policy_times='60'
*._ksb_restart_policy_times='120'
*._ksb_restart_policy_times='240' # internal update to set default
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
*._mmv_query_rewrite_enabled=TRUE
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_adjust_for_part_skews=TRUE
*._optim_enhance_nnull_detection=TRUE
*._optim_new_default_join_sel=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=TRUE
*._optimizer_correct_sq_selectivity=TRUE
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_hjsmj_multimatch=TRUE
*._optimizer_cost_model='CHOOSE'
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enhanced_filter_push=TRUE
*._optimizer_extend_jppd_view_types=TRUE
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=240
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_group_by_placement=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_join_elimination_enabled=TRUE
*._optimizer_join_order_control=3
*._optimizer_join_sel_sanity_check=TRUE
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_multi_level_push_pred=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_new_join_card_computation=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion='DEPTH'
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_push_down_distinct=0
*._optimizer_push_pred_cost_based=TRUE
*._optimizer_rownum_bind_default=10
*._optimizer_rownum_pred_based_fkr=TRUE
*._optimizer_skip_scan_enabled=TRUE
*._optimizer_sortmerge_join_inequality=TRUE
*._optimizer_squ_bottomup=TRUE
*._optimizer_star_tran_in_with_clause=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_undo_cost_change='11.1.0.6'
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_view_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_minus_intersect=TRUE
*._px_pwg_enabled=TRUE
*._px_ual_serial_input=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops='RUN_TIME'
*._sqltune_category_parsed='DEFAULT' # parsed sqltune_category
*._subquery_pruning_enabled=TRUE
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs='YES_GSET_MVS'
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE
*.audit_file_dest='/data/oracle/admin/test/adump'
*.audit_trail='DB'
*.cluster_database=TRUE
*.cluster_database_instances=3
*.compatible='11.1.0.0.0'
*.control_files='+DATA/test/controlfile/current.529.684067899'
test1.core_dump_dest='/data/oracle/diag/rdbms/test/test1/cdump'
test2.core_dump_dest='/data/oracle/diag/rdbms/test/test2/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='test'
*.diagnostic_dest='/data/oracle'
test1.instance_number=1
test2.instance_number=2
*.log_archive_config=''
*.log_archive_dest_1='LOCATION=/data/oracle/oradata/test/archivelog'
*.log_buffer=4197376 # log buffer update
*.open_cursors=500
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=245M
*.plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
test1.remote_login_passwordfile='SHARED'
test2.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=3808K
*.sga_target=740M
*.skip_unusable_indexes=TRUE
test1.thread=1
test2.thread=2
test1.undo_tablespace='UNDOTBS1'
test2.undo_tablespace='UNDOTBS2'

但是这种方法显然也存在问题，首先从得到的结果看，里面除了包含用户设置的初始化参数外，还包含了大量的隐含参数。如果这些隐患参数是Oracle用于自动调整的双下划线参数也不奇怪，问题是大部分都是Oracle不推荐设置的单下划线隐含参数。不过这倒是一个查看Oracle隐患参数的好办法。

另外一个问题是，这个方法只对当前实例设置的参数有效，而无法合并多个实例的设置，对比上面的OPEN_CURSORS参数的设置和下面查询的结果就可以发现这个问题：

SQL> select sid, name, value
2 from v$spparameter
3 where name = 'open_cursors';

SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* open_cursors 300
test1 open_cursors 500
test2 open_cursors 700

SQL> select inst_id, name, value
2 from gv$system_parameter
3 where name = 'open_cursors';

INST_ID NAME VALUE
---------- ------------------------------ --------------------------------------------------
1 open_cursors 500
2 open_cursors 500

显然CREATE PFILE获取的结果和SPFILE中的设置并不相符，下面修改一下初始化参数OPEN_CURSORS的值：

SQL> alter system set open_cursors = 400;

系统已更改。

SQL> create pfile = '/export/home/oracle/inittest1.ora' from memory;

文件已创建。

SQL> host more /export/home/oracle/inittest1.ora | grep open_cursors
*.open_cursors=400

从这个结果可以看到，CREATE PFILE获取的PFILE只对当前实例有效，虽然获取的结果包含多个实例的设置，但是这些设置可能和其他实例上的真正设置并不相符。

查询初始化参数的方法（七）

Oracle在视图V$SYSTEM_PARAMETER中提供了一个列ISDEFAULT，表示当前设置的值是否是数据库的默认值：

SQL> select name, value, isdefault
2 from v$system_parameter
3 where name = 'open_cursors';
NAME VALUE ISDEFAULT
------------------------------ -------------------------------------------------- ---------
open_cursors 400 FALSE
SQL> select isdefault, count(*)
2 from v$system_parameter
3 group by isdefault;
ISDEFAULT COUNT(*)
--------- ----------
TRUE 267
FALSE 22

根据这个结果可以看到，数据库中绝大部分的初始化参数设置都是默认值。

SQL> select name, value, isdefault
2 from v$system_parameter
3 where name = 'undo_retention';
NAME VALUE ISDEFAULT
------------------------------ -------------------------------------------------- ---------
undo_retention 900 TRUE
SQL> select sid, name, value
2 from v$spparameter
3 where name = 'undo_retention';
SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* undo_retention
SQL> alter system set undo_retention = 900;
系统已更改。
SQL> select name, value, isdefault
2 from v$system_parameter
3 where name = 'undo_retention';
NAME VALUE ISDEFAULT
------------------------------ -------------------------------------------------- ---------
undo_retention 900 TRUE
SQL> select sid, name, value
2 from v$spparameter
3 where name = 'undo_retention';
SID NAME VALUE
---------- ------------------------------ --------------------------------------------------
* undo_retention 900

对于手工设置的初始化参数与系统默认值相同的情况，通过v$system_parameter视图是无法区分的。

同样通过查询V$SPPARAMETER视图检查SPFILE的设置也不准确，因为初始化参赛可能是通过PFILE设置的，或者是实例启动后由ALTER SYSTEM命令进行过修改。

对于这种情况，其实上一篇文章中介绍的CREATE PFILE FROM MEMORY或CREATE的方式是可以看到的，不过既然这种方法能够查询得到，那么数据库中一定是在默写地方进行了记录。

实际上查询V$SYSTEM_PARAMETER4视图就可以获取到所有用户设置的初始化参数。

SQL> select sid, name, display_value value
2 from v$system_parameter4
3 where name not like '/_%' escape '/';
SID NAME VALUE
---------- ------------------------------ -----------------------------------------------
test1 processes 150
test1 spfile +DATA/test/spfiletest.ora
test1 resource_manager_plan SCHEDULER[0x2C0E]:DEFAULT_MAINTENANCE_PLAN
test1 sga_target 740M
test1 control_files +DATA/test/controlfile/current.529.684067899
test1 db_block_size 8192
test1 compatible 11.1.0.0.0
test1 log_archive_config
test1 log_archive_dest_1 LOCATION=/data/oracle/oradata/test/archivelog
test1 log_buffer 4197376
test1 cluster_database TRUE
test1 cluster_database_instances 3
test1 db_create_file_dest +DATA
test1 thread 1
test1 undo_tablespace UNDOTBS1
test1 undo_retention 900
test1 instance_number 1
test1 remote_login_passwordfile SHARED
test1 db_domain
test1 plsql_warnings DISABLE:ALL
test1 result_cache_max_size 3808K
test1 core_dump_dest /data/oracle/diag/rdbms/test/test1/cdump
test1 audit_file_dest /data/oracle/admin/test/adump
test1 audit_trail DB
test1 db_name test
test1 open_cursors 400
test1 optimizer_mode ALL_ROWS
test1 query_rewrite_enabled TRUE
test1 pga_aggregate_target 245M
test1 optimizer_dynamic_sampling 2
test1 skip_unusable_indexes TRUE
test1 diagnostic_dest /data/oracle
已选择32行。

而事实上，当数据库执行CREATE PFILE FROM MEMORY命令时，Oracle创建PFILE的数据源就是V$SYSTEM_PARAMETER4这个视图。
前面介绍了很多种查询初始化参数的方法，其实还有一个方法也是很有用的，就是通过alert文件检查数据库加载的所有非默认值的初始化参数。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE例程已经关闭。
SQL> startup
ORACLE例程已经启动。
Total System Global Area 776896512 bytes
Fixed Size 2098776 bytes
Variable Size 246077864 bytes
Database Buffers 524288000 bytes
Redo Buffers 4431872 bytes
数据库装载完毕。
数据库已经打开。
SQL> host
bash-3.00$ tail -200 /data/oracle/diag/rdbms/test/test1/trace/alert_test1.log
Sat Jun 13 15:20:03 2009
Stopping background process SMCO
Stopping background process FBDA
Shutting down instance: further logons disabled
Sat Jun 13 15:20:05 2009
Stopping background process CJQ0
Stopping background process QMNC
Stopping background process MMNL
Stopping background process MMON
Shutting down instance (immediate)
License high water mark = 6
ALTER DATABASE CLOSE NORMAL
Sat Jun 13 15:20:10 2009
SMON: disabling tx recovery
SMON: disabling cache recovery
Sat Jun 13 15:20:10 2009
Shutting down archive processes
Archiving is disabled
.
.
.
Sat Jun 13 15:20:19 2009
Instance shutdown complete
Sat Jun 13 15:20:22 2009
Some alert messages have been suppressed because they were produced too early
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface type 1 ce1 255.255.255.0 configured from OCR for use as a cluster interconnect
WARNING 255.255.255.0 could not be translated to a network address error 1
Interface type 1 ce0 255.255.255.0 configured from OCR for use as a public interface
WARNING 255.255.255.0 could not be translated to a network address
WARNING: No cluster interconnect has been specified. Depending on
the communication driver configured Oracle cluster traffic
may be directed to the public interface of this machine.
Oracle recommends that RAC clustered databases be configured
with a private interconnect for enhanced security and
performance.
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version:11.1.0.6.0.
Using parameter settings in server-side pfile /data/oracle/product/11.1/database/dbs/inittest1.ora
System parameters with non-default values:
processes = 150
spfile = "+DATA/test/spfiletest.ora"
sga_target =740M
control_files = "+DATA/test/controlfile/current.529.684067899"
db_block_size = 8192
compatible = "11.1.0.0.0"
log_archive_config = ""
log_archive_dest_1 = "LOCATION=/data/oracle/oradata/test/archivelog"
cluster_database = TRUE
cluster_database_instances= 3
db_create_file_dest = "+DATA"
thread = 1
undo_tablespace = "UNDOTBS1"
undo_retention = 900
instance_number = 1
remote_login_passwordfile= "SHARED"
db_domain = ""
audit_file_dest = "/data/oracle/admin/test/adump"
audit_trail = "DB"
db_name = "test"
open_cursors = 500
pga_aggregate_target =245M
diagnostic_dest = "/data/oracle"
Cluster communication is configured to use the following interface(s) for this instance
172.0.2.62
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
Sat Jun 13 15:20:23 2009
PMON started with pid=2, OS id=19138
Sat Jun 13 15:20:23 2009
VKTM started with pid=4, OS id=19140 at elevated priority
VKTM running at (20)ms precision
Sat Jun 13 15:20:24 2009
DIAG started with pid=6, OS id=19144
Sat Jun 13 15:20:24 2009
DBRM started with pid=8, OS id=19146
.
.
.
Completed: ALTER DATABASE OPEN
Sat Jun 13 15:20:41 2009
Starting background process CJQ0
Sat Jun 13 15:20:41 2009
CJQ0 started with pid=64, OS id=19434
Setting Resource Manager plan SCHEDULER[0x2C0E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

检查alert文件的方法不但可以获取当前实例所有非默认初始化参数的信息，还是初始化参数文件丢失后用来恢复初始化参数文件的一种方法

---end----

秒客网

ORACLE查询初始化参数的方法

相关文章