【體系結構】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 XKSPPIA,XKSPPIA,XKSPPCV 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:

ORA-02096: specified initialization parameter is not modifiable with this option

 

 

SYS@lhrdb> alter system set sort_area_size = 65536 deferred;

 

System altered.