查詢初始化引數的方法很多，比如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

檢查alert檔案的方法不但可以獲取當前例項所有非預設初始化引數的資訊，還是初始化引數檔案丟失後用來恢復初始化引數檔案的一種方法。

查詢初始化引數的方法（七）

相關文章