Oracle參數的查詢和修改方法

這篇文章主要講解了“Oracle參數的查詢和修改方法”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“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參數的查詢和修改方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！