Oracle參數的查詢和修改方法

150次閱讀

共計 9210 個字符，預計需要花費 24 分鐘才能閱讀完成。

這篇文章主要講解了“Oracle 參數的查詢和修改方法”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“Oracle 參數的查詢和修改方法”吧！

測試環境

DB Version: oracle 11.2 RAC

OS: RHEL 6.x

v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter 的區別

Oracle 11g 里主要的查詢參數的視圖有 v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter, dba_hist_parameter

v$parameter 和 v$parameter2 有什么區別呢？就跟 v$system_parameter 和 v$system_parameter2 的區別是一樣的。呵呵，具體為:

v$parameter 里存的是每個 parameter 的 value, 一個 parameter 一條記錄。v$parameter2 也存的是每個 parameter 的 value, 不過在 v$parameter2 里是每行的 name 只會存一個 value。以 control_files 為例，這個 parameter 會對應至少兩個值，那么在 v$parameter 里只會有一條記錄，而在 v$parameter2 里卻會有 2 條記錄。其實真想不明白 oracle 為啥要設計出一個這樣的視圖來，使用場景在哪？歡迎有知道的童鞋告訴我下

v$parameter 和 v$system_parameter 的區別：v$parameter 是存儲當前 session 的 parameter/value, 而 v$system_parameter 存儲的是當前 instance 級別的 parameter/value; 也就是說一個新建立的 session，v$parameter 和 v$system_parameter 里面的 parameter/value 是一致的; 因為 session level parameter 是從 instance level parameter 繼承來的; 然后通過 alter session 可以修改 v$parameter 的值，但是并不會修改 v$system_parameter data

我嘗試從 session1 執行一個 alter session, 然后再 session1 看到 v$parameter 值是變化了，打開 session2, 看到的 v$parameter 的值還是老的值，這我就困惑了，不是說 v$parameter 能看到 alter session 之后的值嗎？為啥 session1 和 session2 看到的結果不一樣呢? 這就是因為 v$parameter 顯示的是當前 session 的 parameter name/value 呀.

v$spparameter 和 v$system_parameter: 這兩個 view 的區別在于: v$spparameter 用于存儲 spfile 里的 parameter name/value, 而 v$system_parameter 用于存儲 instance level 當前的 parameter name/value; 體現在哪呢？

例子

SQL select name, value from v$system_parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$spparameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

———— 執行 alter session 之后三張表的查詢結果

Session 1:

SQL select userenv(sid) from dual;

USERENV(SID)

————–

569

SQL alter session set cursor_sharing= FORCE

Session altered.

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing FORCE

SQL select name, value from v$spparameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$system_parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

Session 2:

SQL select userenv(sid) from dual;

USERENV(SID)

————–

853

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$spparameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$system_parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

從這里可以看到通過 alter session 修改之后，當前 session1 里查 v$parameter, cursor_sharing 已經改成了 FORCE, 但是另外一個 session 的 v$parameter 里的值并沒有改變; 通過 v$system_parameter 和 v$spparameter 里的值也沒有改變；

例子 2:

僅僅修改 v$spparameter

通過 alter system set … scope=spfile，這樣修改的參數就只會進入 spfile 里，所以只有 v$spparameter 才能看到;

修改前

SQL select name, value from v$parameter where name= cursor_sharing

NAME VALUE

—————————— ——————————

cursor_sharing EXACT

SQL select name, value from v$parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors 500

SQL select name, value from v$system_parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors 500

SQL select name, value from v$spparameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors 500

修改后

SQL alter system set open_cursors=3000
scope=spfile sid= *

System altered.

SQL select name, value from v$system_parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors
500

SQL select name, value from v$spparameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors
3000

SQL select name, value from v$parameter where name= open_cursors

NAME VALUE

—————————— ——————————

open_cursors
500

例子 3

只修改 v$system_parameter

修改前

SQL select name, value from v$spparameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count

注意這里顯示的是 NULL，表示這個 parameter value 在 spfile 里沒有設置，但是 v$system_parameter 里又有值，這是因為系統會根據一定的規則自己計算出一個值來

SQL select name, value from v$system_parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 128

SQL select name, value from v$parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 128

修改后

SQL alter system set db_file_multiblock_read_count=256
scope=memory sid= *

System altered.

SQL select name, value from v$spparameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count

SQL select name, value from v$system_parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 256

SQL select name, value from v$parameter where name= db_file_multiblock_read_count

NAME VALUE

—————————— ——————————

db_file_multiblock_read_count 256

v$spparameter 里的值仍然為空，v$system_parameter 的值改為了 256, 并且并且 v$parameter 里的值也是 256. 這說明什么問題？說明如果一個參數沒有執行 alter session, 那么這個參數的值在 v$parameter 和 v$system_parameter 里的值是一樣的;

當然也可以通過 alter system set … scope=BOTH sid= * 讓改動在 v$spparameter 和 v$system_parameter 里都能看到; 這也就是 scope=BOTH/MEMORY/SPFILE 的區別

oracle 11G 里有 355 個 parameter, 那到底哪個是可以通過 alter session 修改的，哪個是可以通過 alter system 修改的呢？以及那些不可以修改的呢？這個通過 v$system_parameter 就可以知道;

isses_modifiable: 是否能通過 alter session 修改. 這只有兩個值: TRUE/FALSE

issys_modifiable: 是否能通過 alter system 修改: 這有三個值: IMMEDIATE/DEFERRED/FALSE (IMMEDIATE 表示立即生效，在當前 session 就生效; DEFFERED: 表示對所有當前正在連接的 session 都不生效，只有對這之后的連接才生效. FALSE 就表示不允許 alter system 修改)

對于 ISSYS_MODIFIABLE=DEFERRED 的參數，必須通過 alter system …. deferred 才能修改; 否則都會報錯 ORA-02096

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

當使用 ASMM 或者 AMM 的時候，很多 parameter value 都是系統自動調整的，當你修改了某個參數之后又想把這個參數交回給系統自動來管理，那怎么辦呢？其實也就是恢復默認值?？梢酝ㄟ^ SQL: alter system reset name= value scope=… 注意這里使用的是 RESET 命令。reset 的命令的意思其實就是不設置了，也就是把一個 item 從 spfile 里刪除，那么如果要刪除，就一定要現有這個 item, 所以如果你要 reset 一個不存在的 parameter 的時候也會報錯?；蛘咴?RAC 里，如果設置的時候是 instance by instance 設置的，而 reset 的時候通過 alter system reset … sid= * 也會報錯;

SQL select name, value from v$spparameter where name= db_file_multiblock_read_count

NAME VALUE

————————————————– ————————————————–

db_file_multiblock_read_count

在 v$spparameter 里找不到這個 parameter value, 說明沒有設置

SQL alter system reset db_file_multiblock_read_count scope=spfile sid= *

alter system reset db_file_multiblock_read_count scope=spfile sid= *

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

這里報錯了

這是一種情況, 對于 RAC 來說，你可以 instance by instance 設置參數信息

這里看到每個 instnace 的 parameter value 不一樣。即使 value 一樣你也可以 instance by instance 設置

SQL select sid, name, value from v$spparameter where name= db_file_multiblock_read_count

SID NAME VALUE

——————————————————————————– ———————————-

racaaweb1 db_file_multiblock_read_count 64

racaaweb2 db_file_multiblock_read_count 256

SQL alter system reset db_file_multiblock_read_count scope=spfile sid= *

alter system reset db_file_multiblock_read_count scope=spfile sid= *

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

這里還是出錯了

隱含參數

Oracle 還有另外一種參數，稱為隱含參數，隱含參數的名字以下劃線_打頭。默認情況下隱含參數不會出現在上面的幾張表里，除非你修改了這些參數的值;

例子

alter system set _undo_autotune =TRUE;

這個語句修改了隱含參數_undo_autotune 的值; 這里涉及到注意點:

1) 修改隱含參數的時候，隱含參數名字需要用雙引號括起來

2) 對于這種后面沒帶 scope 的，scope 的默認值是 BOTH

3) 對于這種后面沒帶 sid 的，sid 的默認值是 *

更新之后的值如下:

SQL select name, value from v$spparameter where name= _undo_autotune

NAME VALUE

————————————————– ————————————————–

_undo_autotune TRUE

SQL select name, value from v$system_parameter where name= _undo_autotune

NAME VALUE

————————————————– ————————————————–

_undo_autotune TRUE

也就是說，所有修改過的并且當前生效的參數都可以在 v$parameter 里看到, 不管是隱含的還是非隱含的。那么如何查詢哪些沒有修改過的隱含參數呢？使用一下 SQL:

Hidden Parameter

set linesize 1000

SET VERIFY OFF

COLUMN parameter FORMAT a37

COLUMN description FORMAT a50 WORD_WRAPPED

COLUMN session_value FORMAT a10

COLUMN instance_value FORMAT a10

SELECT a.ksppinm AS parameter,

a.ksppdesc AS description,

b.ksppstvl AS session_value,

c.ksppstvl AS instance_value

FROM x$ksppi a,

x$ksppcv b,

x$ksppsv c

WHERE a.indx = b.indx

AND a.indx = c.indx

AND a.ksppinm LIKE % ||LOWER(1)|| % ESCAPE /

ORDER BY a.ksppinm;

補充

1: x$ksppi: 這里存的是所有的 parameter name; x$ksppcv 存的是 session level 的 parameter value, x$ksppsv 存的是 instance level 的 parameter value; 這個通過查詢 x$fixed_view_definition 就能看到了

2. oracle snapshot 也會記錄下這些 parameter value，所以從這里也能看到是否有變化;

3. 當修改任何一個 parameter 的時候，都會在 alert log 里有記錄，所以從這里也能看到所有被改變的 parameter;

感謝各位的閱讀，以上就是“Oracle 參數的查詢和修改方法”的內容了，經過本文的學習后，相信大家對 Oracle 參數的查詢和修改方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完