[20180529]模擬會話引數變化.txt

[20180529]模擬會話引數變化.txt

--//連結提到http://www.itpub.net/thread-2102533-1-1.html,修改引數cursor_sharing,第二天會發生變化.
--//我猜測可能是rac例項斷開,有連上,看看這樣是否引數會發生變化.

--//測試參考連結: http://blog.itpub.net/267265/viewspace-761721/

1.環境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//在client端tnsnames.ora檔案增加如下:
78G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = book)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

2.測試:
R:\>sqlplus scott/book@78G

SCOTT@78G> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
         1         11 7212:8180                DEDICATED 58305       24          5 alter system kill session '1,11' immediate;

SCOTT@78G> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);
SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
book                           SELECT        BASIC      NO

SCOTT@78G> alter session set optimizer_index_cost_adj=50;
Session altered.

SCOTT@78G> show parameter optimizer_index_cost_adj
NAME                     TYPE    VALUE
------------------------ ------- -----
optimizer_index_cost_adj integer 50
--//已經修改引數.

SCOTT@78G> select * from dba_objects ;
...
87016 rows selected.
--//執行過程中,重啟資料庫看看,執行startup force;
--//在資料庫啟動過程中可以發現使用者會話hang,等待資料庫啟動完成後,繼續顯示執行結果集和.

SCOTT@78G> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- ------------------------------------------------
       300         11 7212:8180                DEDICATED 58441       47          4 alter system kill session '300,11' immediate;
--//sid已經發生了變化,服務端程式號也發生了變化.

SCOTT@78G> show parameter optimizer_index_cost_adj
NAME                      TYPE     VALUE
------------------------- -------- -----------
optimizer_index_cost_adj  integer  100
--//會話引數發生了變化.

SCOTT@78G> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);
SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
book                           SELECT        BASIC      YES
--//FAILED_OVER變成了Yes ,說明發生國failer_over.

SCOTT@78G> select count(*) from dba_objects;
  COUNT(*)
----------
     87016

--//可以確定前面的結果集顯示是正常的.