查询初始化参数的方法（七）-CSDN博客

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

这一篇描述如何判断一个初始化参数是否是默认参数。

查询初始化参数的方法（一）：http://yangtingkun.itpub.net/post/468/484669

查询初始化参数的方法（二）：http://yangtingkun.itpub.net/post/468/484729

查询初始化参数的方法（三）：http://yangtingkun.itpub.net/post/468/485623

查询初始化参数的方法（四）：http://yangtingkun.itpub.net/post/468/485664

查询初始化参数的方法（五）：http://yangtingkun.itpub.net/post/468/485719

查询初始化参数的方法（六）：http://yangtingkun.itpub.net/post/468/485756

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