【DB筆試面試538】在Oracle中， 資料庫的參數分為哪幾類？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中， 資料庫的參數分為哪幾類？
♣
答案部分
Oracle資料庫根據SPFILE或PFILE中設置的參數來啟動資料庫。Oracle中的參數，根據系統使用情況可以簡單分為兩大類：
l 普通參數：Oracle系統正常使用的一些參數。
l 非凡參數：包括4種，過時參數、強調參數、隱含參數及推導參數。
圖 3-11 Oracle參數分類
（一）參數類型介紹
初始化參數有兩種類型。
靜態參數（Static parameters）：影響實例或整個資料庫，只能通過更改init.ora或SPFILE的內容來修改。靜態參數要求關閉資料庫後再重新啟動資料庫才能生效。無法對當前實例更改靜態參數。
動態參數（Dynamix parameters）：可以在資料庫聯機時更改。有兩種類型的動態參數：
會話級別參數僅影響一個用戶會話。這類參數的示例有國家語言支援(NLS)參數，這些參數可用於指定排序、日期參數等的國家語言設置。這些參數可以在某個給定會話中使用，並且會在該會話結束時失效。
系統級別參數影響整個資料庫和所有會話。這類參數的示例有修改SGA_TARGET值和設置歸檔日誌目標位置。這些參數在指定的SCOPE內保持有效。要使這些參數設置永久有效，必須通過指定SCOPE=BOTH選項或手動編輯PFILE，將這些參數添加到SPFILE。
可以使用ALTER SESSION和ALTER SYSTEM命令更改動態參數。使用ALTER SYSTEM語句的SET子句可以設置或更改初始化參數值。可選的SCOPE子句用於指定更改的作用域，如下所示：
l SCOPE=SPFILE：此更改僅在伺服器參數文件中應用。不會對當前實例進行更改。對於動態和靜態參數，更改在下一次啟動後生效，並且永久保持有效。對於靜態參數來說，只允許指定該SCOPE值。
l SCOPE=MEMORY：此更改僅應用到記憶體中。會對當前實例進行更改，且更改立即生效。對於動態參數，更改立即生效，但不會永久保持，因為伺服器參數文件不會進行更新。對於靜態參數，不允許指定該值。
l SCOPE=BOTH：此更改會應用到伺服器參數文件和記憶體中。會對當前實例進行更改，且更改立即生效。對於動態參數，更改永久保持有效，因為伺服器參數文件會進行更新。對於靜態參數，不允許指定該值。
如果實例不是使用伺服器參數文件(SPFILE)啟動的，則指定SCOPE=SPFILE或SCOPE=BOTH是錯誤的。如果實例是使用伺服器參數文件啟動的，則默認值為SCOPE=BOTH；如果實例是使用文本初始化參數文件啟動的，則默認值為MEMORY。
Oracle的推導參數（Derived Parameters）也是初始化參數的一種。推導參數值通常來自於其它參數的運算，依賴其它參數計算得出，例如SESSIONS、DML_LOCKS等參數都屬於推導參數。該類參數在官方文檔中的「Default value」中由關鍵字Derived標識，例如，DML_LOCKS參數的默認值為：Derived: 4 * TRANSACTIONS，說明該參數為推導參數，它的值默認為參數TRANSACTIONS值的4倍。
如何判斷一個初始化參數的值是否是默認參數值？Oracle在視圖V$SYSTEM_PARAMETER或V$PARAMETER中提供了一個列ISDEFAULT，表示當前設置的值是否是資料庫的默認值。
如何判斷一個初始化參數的值是否是延遲生效?是否是動態參數？動態參數指的是可以使用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.
（二）參數的設置方法
初始化參數的設置方法有很多種：
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參數變更生效範圍如下表所示：
|
參數 |
取值 |
結果 |
動態參數 |
靜態參數 |
|---|---|---|---|---|
|
SCOPE |
SPFILE |
表示該修改只對伺服器參數有效 |
資料庫重啟時有效，永久有效 |
資料庫重啟時有效，永久有效，靜態參數只適於SPFILE |
|
MEMORY |
表示該修改只對記憶體有效 |
立即有效，但不產生永久效果，因為沒有修改伺服器參數 |
不允許使用 |
|
|
BOTH |
表示該修改對上述兩種都有效 |
立即有效，永久有效，使用BOTH選項實際上等同於不帶參數的ALTER SYSTEM語句 |
不允許使用 |
在RAC環境中，若想修改所有實例，則可以在ALTER SYSTEM的最後加上「SID='*'」或「SID='實例名'」即可，其中，「*」代表所有實例。
下面詳細來介紹各種參數。
（三）普通參數
普通參數就是Oracle系統正常使用的一些參數。查詢Oracle初始化參數的方式有如下幾種：
表 3-13 查詢Oracle初始化參數的方式
|
查詢命令 |
含義 |
|---|---|
|
SHOW PARAMETERS/SHOW PARAMETER |
SQL*Plus工具提供的查詢初始化參數的方法，這個方法查詢的初始化參數是當前會話生效的初始化參數。 |
|
SHOW SPPARAMETERS/SHOW SPPARAMETER |
SQL*Plus工具提供的方法，用來查詢當前會話生效的SPFILE參數包含的初始化參數。這個命令在Oracle 11g以後SQL*Plus版本中有效。 |
|
CREATE PFILE |
CREATE PFILE命令不像其它方法那樣直觀，這種方法可以將SPFILE中或當前記憶體中設置的初始化文件保存到PFILE文件中，然後就可以通過文本編輯工具直觀地看到SPFILE中或當前記憶體中設置了哪些初始化參數。雖然這種方法看上去比較麻煩，但是這種方法列出的參數都是用戶設置的參數，所有默認值的參數並不會列出來，因此看到的結果要比其它方法直觀得多。在Oracle 11g以後的版本允許執行CREATE PFILE FROM MEMORY操作。 |
|
V$PARAMETER |
V$PARAMETER視圖提供了當前會話可見的初始化參數的設置，如果想查詢RAC資料庫的所有實例的設置，那麼可以查詢GV$PARAMETER視圖。該視圖底層來自於X$KSPPCV。 |
|
V$PARAMETER2 |
V$PARAMETER2視圖和V$PARAMETER差不多，唯一的區別在於對於包括多值的初始化參數，從這個視圖會返回多條記錄，每條記錄對應一個值。同樣的，對於RAC環境可以查詢GV$PARAMETER2視圖。該視圖底層來自於X$KSPPCV2。 |
|
V$SYSTEM_PARAMETER |
V$SYSTEM_PARAMETER視圖記錄當前實例生效的初始化參數設置。注意這裡是實例生效而不是會話生效。同樣，GV$SYSTEM_PARAMETER則包含了所有實例生效的初始化參數資訊。 |
|
V$SYSTEM_PARAMETER2 |
V$SYSTEM_PARAMETER2視圖與V$SYSTEM_PARAMETER視圖的關係和V$PARAMETER2視圖與V$PARAMETER視圖的關係一樣，都是對於包含多個值的參數採用了分行處理的方式。 |
|
V$SPPARAMETER |
V$SPPARAMETER記錄了來自SPFILE文件中初始化參數。如果參數在SPFILE文件中沒有設置，那麼欄位ISSPECIFIED對應的值為FALSE。同樣可以查詢GV$SPPARAMETER參數來顯示RAC環境所有實例的設置。 |
一般在查詢初始化參數的時候都習慣性地使用SHOW PARAMETER，也就是查詢V$PARAMETER視圖。V$PARAMETER視圖反映的是初始化參數在當前會話中生效的值，而V$SYSTEM_PARAMETER反映的才是實例級上的初始化參數。有關視圖V$PARAMETER的解釋參考下表：
表 3-14 V$PARAMETER視圖解釋
|
列 |
數據類型 |
說明 |
參數舉例 |
|---|---|---|---|
|
NUM |
NUMBER |
參數NUM |
processes、sessions |
|
NAME |
VARCHAR2(80) |
參數名 |
processes、sessions |
|
type |
number |
參數類型：1 – Boolean2 – String3 – Integer4 – Parameter file5 – Reserved6 – Big integer |
processes、timed_statistics |
|
VALUE |
VARCHAR2(4000) |
會話的當前值，若沒有用ALTER SESSION修改過參數值，則當前值就是實例級的參數值 |
processes、timed_statistics |
|
DISPLAY_VALUE |
VARCHAR2(4000) |
和VALUE列的值一樣，不過該列的值顯示更加友好 |
processes、timed_statistics |
|
ISDEFAULT |
VARCHAR2(9) |
參數是否為預設值 |
processes、timed_statistics |
|
ISSES_MODIFIABLE |
VARCHAR2(5) |
若值為TRUE，則代表參數可用ALTER SEEEION更改；若值為FALSE，則代表參數不能用ALTER SEEEION更改 |
processes、timed_statistics |
|
ISSYS_MODIFIABLE |
VARCHAR2(9) |
若值為IMMEDIATE，則代表參數可用ALTER SYSTEM更改，且立刻生效，該參數屬於動態參數；若值為DEFERRED，則代表參數可以用ALTER SYSTEM更改，但是在新連接的會話中生效，該參數屬於動態參數；若值為FALSE，則代表參數不能使用ALTER SYSTEM更改，但是若當前參數文件使用的是SPFILE，則可以使用ALTER SYSTEM更改，且下次實例啟動生效，該參數屬於靜態參數 |
recyclebin、instance_name |
|
ISINSTANCE_MODIFIABLE |
VARCHAR2(5) |
若值為TRUE，則代表參數可以在不同的實例上設置不同的值。若值為FALSE，則代表參數在RAC的所有實例上必須設置相同的值。如果ISSYS_MODIFIABLE列為FALSE，則該列也為FALSE |
processes、timed_statistics |
|
ISMODEIFIED |
VARCHAR2(10) |
若值為MODIFIED，則代表值已經被命令ALTER SESSION修改過；若值為SYSTEM_MOD，則代表該參數值已經被命令ALTER SYSTEM修改過；若值為FALSE，則代表自從實例啟動後該參數沒有被修改過 |
processes、timed_statistics |
|
ISADJUSTED |
VARCHAR2(5) |
指出資料庫是否調整輸入值。例如參數值應該為素數，但用戶輸入一個非素數，因此資料庫將該值調整為下一個素數） |
processes、timed_statistics |
|
DESCRIPTION |
VARCHAR2(255) |
有關此參數的一個描述性的注釋 |
processes、timed_statistics |
|
ISDEPRECATED |
VARCHAR2(5) |
標識該參數是否已廢棄 |
lock_name_space、instance_groups |
|
ISBASIC |
VARCHAR2(5) |
標識該參數是否是一個基本參數 |
lock_name_space、timed_statistics |
|
UPDATE_COMMENT |
VARCHAR2(255) |
最近更新過的注釋 |
processes、sessions |
|
HASH |
NUMBER |
該參數的HASH值 |
processes、sessions |
（四）非凡參數
下面分別講解3種非凡參數。
過時參數（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已變為了過時參數。
Oracle系統中還有一類參數稱之為隱含參數（Hidden Parameters），在系統中使用，但Oracle官方沒有公布的參數，這些參數可能是那些還沒有成熟或者是系統開發中使用的參數。這些參數在所有Oracle官方提供的文檔中都沒有介紹，它們的命名有一個共同特徵就是都以「_」作為參數的首字元。下面的查詢可以得到當前系統中的所有隱藏參數，需要以SYS用戶登陸，查看兩個視圖：X$KSPPI和X$KSPPCV。下面作者給出具體的SQL語句。
舉個例子，如果需要查詢隱含參數「_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;
& 說明：
有關Oracle參數的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2127338/
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
