程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 【體系結構】Oracle參數介紹，體系結構oracle參數

【體系結構】Oracle參數介紹，體系結構oracle參數

編輯：Oracle教程

【體系結構】Oracle參數介紹，體系結構oracle參數

【體系結構】Oracle參數介紹

1 BLOG文檔結構圖

2 前言部分

2.1 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① Oracle中的各種參數介紹及其查詢方法

② Oracle中V$PARAMETER及V$PARAMETER2的區別

③ 隱含參數的查詢、重置、清除

④ 會話參數和實例參數的查詢

⑤ 靜態參數和動態參數、延遲參數

⑥ V$PARAMETER視圖的每列含義（重點）

Tips：

① 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。

② 文章中用到的所有代碼、相關軟件、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載，小麥苗的雲盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。

③ 若網頁文章代碼格式有錯亂，請下載pdf格式的文檔來閱讀。

④ 在本篇BLOG中，代碼輸出部分一般放在一行一列的表格中。其中，需要特別關注的地方我都用灰色背景和粉紅色字體來表示，比如在下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字體標注；對代碼或代碼輸出部分的注釋一般采用藍色字體表示。

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- ------------------- ---------- ---------

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XLHRD_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

2.3 本文簡介

其實很早就想把楊廷琨大神的參數系列的文章整理一下了，一直沒空，最近寫書碰到了這裡問題，正好就抽空整理一下。

第二章 Oracle參數簡介

Oracle數據庫根據SPFILE或PFILE中設置的參數來配置數據庫的啟動。每個數據庫實例在啟動之前，首先讀取這些參數文件中設置的不同參數。Oracle系統中的參數，根據系統使用情況可以簡單分為兩大類：

l 普通參數：Oracle系統正常使用的一些參數

l 非凡參數：包括3種，過時參數、強調參數和隱含參數。

圖3-8 Oracle參數分類

首先介紹一下參數的設置方法。初始化參數的設置方法有很多種：

l 通過“ALTER SYSTEM/SESSION SET 參數名=參數值 SCOPE = MEMORY;”的方式僅在內存裡修改。

l 通過“ALTER SYSTEM SET 參數名=參數值 SCOPE = SPFILE;”的方式只修改SPFILE裡的值。

l 通過“ALTER SYSTEM SET 參數名=參數值 DEFERRED SCOPE = SPFILE;”的方式設置延遲生效，也就是說這個修改只對以後連接到數據庫的會話生效，而對當前會話以及其它已經連接到Oracle的會話不會生效。

l 通過“ALTER SYSTEM/SESSION SET 參數名=參數值 SCOPE = BOTH;”或省略BOTH這個關鍵詞可以同時修改SPFILE和MEMORY中的值。

ALTER SESSION和ALTER SYSTEM的區別如下：

ALTER SESSION

修改的參數只限於本次會話，退出會話再進入時修改失效

ALTER SYSTEM

修改的參數適用於數據庫實例的所有會話，數據庫關閉則修改失效。有特權用戶和DBA可以執行

ALTER SYSTEM DEFERRED

修改是延遲修改，退出會話，下次進入會話時生效。有特權用戶和DBA可以執行

Oracle參數變更生效范圍如下表所示：

在RAC環境中，若想修改所有實例，則可以在ALTER SYSTEM的最後加上“SID='*'”或“SID='實例名'”即可。“*”代表所有實例。

2.1 過時參數和強調參數

過時參數（Obsolete Parameters），顧名思義就是在Oracle以前的版本中存在，但在新版本中已經淘汰了的參數，已經不再使用的參數。在視圖V$OBSOLETE_PARAMETER中，包含這些參數的名稱和一個列ISSPECIFIED，該列用來指出這個參數是否在參數文件中已實際設置。下面的SQL腳本列出了當前系統中所有的過時參數名稱以及它們是否在當前系統中設定。

SELECT NAME, ISSPECIFIED FROM V$OBSOLETE_PARAMETER;

強調參數（Underscored Parameters），是指那些在新版本中保留了下來，但是除非非常需要否則不希望用戶使用的那些參數。強調參數可以通過系統視圖X$KSPPO來查看，該視圖中包含一個名為KSPPOFLAG的字段。該字段用來指明該參數在當前版本中是被丟棄還是被強調。若該值為1，則表示該參數已被丟棄，若該值為2，則表明該參數現為強調參數。

SYS@lhrdb> SELECT KSPPONM, DECODE(KSPPOFLG, 1, 'Obsolete', 2, 'Underscored')

2 FROM X$KSPPO T

3 WHERE T.KSPPONM IN ('hash_join_enabled','job_queue_interval')

4 ORDER BY KSPPONM;

KSPPONM DECODE(KSPP

---------------------------------------------------------------- -----------

hash_join_enabled Underscored

job_queue_interval Obsolete

可以看到HASH_JOIN_ENABLED這個參數為強調參數，在隱含參數中表現為“_HASH_JOIN_ENABLED”，而JOB_QUEUE_INTERVAL已變為了過時參數。

2.2 隱含參數

Oracle系統中還有一類參數稱之為隱含參數（Hidden Parameters），在系統中使用，但Oracle官方沒有公布的參數，這些參數可能是那些還沒有成熟或者是系統開發中使用的參數。這些參數在所有Oracle官方提供的文檔中都沒有介紹，它們的命名有一個共同特征就是都以“_”作為參數的首字符。下面的查詢可以得到當前系統中的所有隱藏參數，需要以SYS用戶登陸，查看兩個視圖：X$KSPPI和X$KSPPCV。下面作者給出具體的SQL語句。

SET PAGESIZE 9999

SET LINE 9999

COL NAME FORMAT A40

COL KSPPDESC FORMAT A50

COL KSPPSTVL FORMAT A20

SELECT A.INDX,

A.KSPPINM NAME,

A.KSPPDESC,

B.KSPPSTVL

FROM X$KSPPI A,

X$KSPPCV B

WHERE A.INDX = B.INDX

AND A.KSPPINM LIKE '/_%' ESCAPE '/'

AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%');

舉個例子，如果需要查詢隱含參數“_LM_DD_INTERVAL”的值，那麼執行上面的代碼後輸入“_LM_DD_INTERVAL”就可以看到該隱含參數的值了，如下所示：

SYS@lhrdb> SET PAGESIZE 9999

SYS@lhrdb> SET LINE 9999

SYS@lhrdb> COL NAME FORMAT A40

SYS@lhrdb> COL KSPPDESC FORMAT A50

SYS@lhrdb> COL KSPPSTVL FORMAT A20

SYS@lhrdb> SELECT A.INDX,

2 A.KSPPINM NAME,

3 A.KSPPDESC,

4 B.KSPPSTVL

5 FROM X$KSPPI A,

6 X$KSPPCV B

7 WHERE A.INDX = B.INDX

8 AND A.KSPPINM LIKE '/_%' ESCAPE '/'

9 AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%');

Enter value for parameter: _lm_dd_interval

old 9: AND LOWER(A.KSPPINM) LIKE LOWER('%&PARAMETER%')

new 9: AND LOWER(A.KSPPINM) LIKE LOWER('%_lm_dd_interval%')

INDX NAME KSPPDESC KSPPSTVL

---------- ---------------------------------------- -------------------------------------------------- --------------------

578 _lm_dd_interval dd time interval in seconds 10

可以看到該隱含參數的值為10。

對於隱含參數而言，修改隱含參數的值的時候需要將隱含參數用雙引號括起來。若要清除SPFILE中的隱含參數可以使用RESET命令。

SYS@lhrdb> alter system set _lm_dd_interval=20 scope=spfile;

alter system set _lm_dd_interval=20 scope=spfile

ERROR at line 1:

ORA-00911: invalid character

SYS@lhrdb> alter system set "_lm_dd_interval"=20 scope=spfile;

System altered.

SYS@lhrdb> alter system reset "_lm_dd_interval" scope=spfile sid='*';

System altered.

普通用戶是不具備查詢隱含參數的權限的，可以通過創建視圖和同義詞的方式來解決這個問題，如下所示：

CREATE OR REPLACE VIEW VW_YH_PARAMETER_LHR AS

SELECT A.INDX, A.KSPPINM NAME, A.KSPPDESC, B.KSPPSTVL

FROM X$KSPPI A, X$KSPPCV B

WHERE A.INDX = B.INDX

AND A.KSPPINM LIKE '/_%' ESCAPE '/' --TRANSLATE (ksppinm, '_', '#') LIKE '#%'

;

GRANT SELECT ON VW_YH_PARAMETER_LHR TO PUBLIC;

CREATE PUBLIC SYNONYM VW_YH_PARAMETER_LHR FOR SYS.VW_YH_PARAMETER_LHR;

2.3 普通參數（系統當前參數）

注意：本小節內容多數來源於楊廷琨老師的博客。

1、普通參數

普通參數就是Oracle系統正常使用的一些參數。查詢Oracle初始化參數的方式大約有如下幾種：

表3-8 查詢Oracle初始化參數的方式

一般在查詢初始化參數的時候都習慣性的使用SHOW PARAMETER，也就是查詢V$PARAMETER視圖。V$PARAMETER視圖反映的是初始化參數在當前會話中生效的值，而V$SYSTEM_PARAMETER反映的才是實例級上的初始化參數。有關視圖V$PARAMETER的解釋參考下表：

表3-9 V$PARAMETER視圖解釋

2.3.1 V$PARAMETER和V$PARAMETER2

首先看一下V$PARAMETER和V$PARAMETER2的區別，這個區別同樣適用於V$SYSTEM_PARAMETER和V$SYSTEM_PARAMETER2：

SELECT NAME, VALUE FROM V$PARAMETER

MINUS

SELECT NAME, VALUE FROM V$PARAMETER2;

SELECT NAME, VALUE FROM V$PARAMETER2

MINUS

SELECT NAME, VALUE FROM V$PARAMETER;

現在這兩個視圖的結果一目了然了。進一步的研究可以看到底層的數據源來自兩個不同的位置，V$PARAMETER來自x$ksppcv ，V$PARAMETER2來自x$ksppcv2，數據源的不同也正是前端的不同。

2.3.2 V$PARAMETER和V$SYSTEM_PARAMETER

一般在查詢初始化參數的時候都習慣性的使用SHOW PARAMETER，也就是查詢V$PARAMETER視圖。V$PARAMETER視圖反映的是初始化參數在當前會話中生效的值，而V$SYSTEM_PARAMETER反映的才是實例級上的初始化參數。

我們通過query_rewrite_enabled這個參數來做一個驗證。

SQL> show parameter query_rewrite_enabled

NAME TYPE VALUE

------------------------------------ ----------- --------------------

query_rewrite_enabled string TRUE

SQL> select name, value

2 from v$parameter

3 where name = 'query_rewrite_enabled';

NAME VALUE

---------------------------------------- ----------------------------

query_rewrite_enabled TRUE

SQL> select name, value

2 from v$system_parameter

3 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

2 from v$parameter

3 where name = 'query_rewrite_enabled';

NAME VALUE

---------------------------------------- ---------------------------

query_rewrite_enabled FALSE

SQL> select name, value

2 from v$system_parameter

3 where name = 'query_rewrite_enabled';

NAME VALUE

---------------------------------------- ---------------------------

query_rewrite_enabled TRUE

可以看到，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 視圖的結果發生了改變。

SQL> CONN / as sysdba

已連接。

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 視圖來獲取。

2.3.3 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> 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 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 視圖獲取的查詢。

2.3.4 GV$SPPARAMETER參數的必要性

其實這裡還有一個問題，就是 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

2.3.5 如何判斷一個初始化參數的來源？

判斷數據庫啟動是否啟用了 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

上面就列出了 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 例程已經啟動。

Total System Global Area 776896512 bytes

Fixed Size 2098776 bytes

Variable Size 246069672 bytes

Database Buffers 524288000 bytes

Redo Buffers 4440064 bytes

數據庫裝載完畢。

數據庫已經打開。

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 中的設置並不相同。

2.3.6 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'

《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》

*.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 只對當前實例有效，雖然獲取的結果包含多個實例的設置，但是這些設置可能和其他實例上的真正設置並不相符。

2.3.7 如何判斷一個初始化參數是否是默認參數值?

如何判斷一個初始化參數的值是否是默認參數值？Oracle在視圖V$SYSTEM_PARAMETER或V$PARAMETER中提供了一個列ISDEFAULT，表示當前設置的值是否是數據庫的默認值。

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 文件的方法不但可以獲取當前實例所有非默認初始化參數的信息，還是初始化參數文件丟失後用來恢復初始化參數文件的一種方法。

2.3.8 靜態參數和動態參數

如何判斷一個初始化參數的值是否是延遲生效、是否是動態參數？動態參數指的是可以使用ALTER SESSION或ALTER SYSTEM在數據庫運行時進行修改並能立即生效的參數。靜態參數指的是只能通過修改參數文件且數據庫必須要重啟才能生效的參數。Oracle在視圖V$PARAMETER中提供了一個列ISSYS_MODIFIABLE，若值為IMMEDIATE代表參數可用ALTER SYSTEM更改，且立刻生效，該參數屬於動態參數；若值為DEFERRED代表參數可以用ALTER SYSTEM更改，但是在新連接的會話中生效，該參數屬於動態參數；若值為FALSE代表參數不能使用ALTER SYSTEM更改，但是若當前參數文件使用的是SPFILE，則可以使用ALTER SYSTEM更改，且下次實例啟動生效，該參數屬於靜態參數。

靜態參數舉例：

SYS@lhrdb> alter system set processes=300;

alter system set processes=300

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

動態參數舉例：

SYS@lhrdb> alter system set undo_retention=10800 ;

System altered.

動態延遲（DEFERRED）參數舉例。DEFERRED指定系統修改是否只對以後的會話生效（對當前建立的會話無效，包括執行此修改的會話）。默認情況下，ALTER SYSTEM命令會立即生效，但是有些參數不能“立即”修改，只能為新建立的會話修改這些參數。

SYS@lhrdb> alter system set sort_area_size = 65536;

alter system set sort_area_size = 65536

ERROR at line 1: