[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
--//可以确定前面的结果集显示是正常的.
