19C新特性--通过连接字符串以及触发器修改回话参数
根据业务需要，有些时候我们需要修改回话参数，但是应用代码无法进行修改，我们可以通过一下两种方式来进行修改
一:通过连接字符串--19c可行
1.通过字符串直接连接数据库
在19c上测试，这种方式是可行的，但是在11g上测试未果
[oracle@localhost ~]$ sqlplus dbmon/dbmon@"(DESCRIPTION=(CONNECT_DATA=(SESSION_SETTINGS=(optimizer_mode=first_rows_10)(statistics_level=all))(SERVER=DEDICATED)(SERVICE_NAME=ceshi))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.30)(PORT=1521)))"
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 16 23:20:35 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Mon Aug 16 2021 22:37:59 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string FIRST_ROWS_10
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level string TYPICAL
statistics_level string ALL
11g测试
[oracle@oracle11g ~]$ sqlplus dbmon/dbmon@"(DESCRIPTION=(CONNECT_DATA=(SESSION_SETTINGS=(optimizer_mode=first_rows_10)(statistics_level=all))(SERVER=DEDICATED)(SERVICE_NAME=orcl))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.33)(PORT=1521)))"
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 15 00:16:36 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
2：通过连接字符串的方式连修改回话参数
下面这种方式不通过监听的方式连接数据库
pdbcs=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=BEQ)
(PROGRAM=oracle)
(ARGV0=oracleCDB1)
(ARGS='(DESCRIPTION=(SILLY_EXAMPLE=TRUE)(LOCAL=YEP)(ADDRESS=(PROTOCOL=beq)))')
)
)
(CONNECT_DATA=
(SESSION_SETTINGS=(container=ceshi)(optimizer_mode=first_rows_10))
(SERVICE_NAME = ceshi)
(INSTANCE_NAME = orcl)
)
)
下面方式通过监听连接数据库
pdbcs1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.30)(PORT = 1521))
)
(CONNECT_DATA =
(SESSION_SETTINGS=(container=ceshi)(optimizer_mode=first_rows_10))
(SERVICE_NAME = ceshi)
(INSTANCE_NAME = orcl)
)
)
这里不进行演示登录之后回话参数的变化。
二:通过触发器--11g可行
create or replace trigger dbmon.set_session_settings after logon on dbmon.schema
begin
execute immediate 'alter session set optimizer_mode=first_rows_10';
end;
/
1.创建触发器
SQL> create or replace trigger dbmon.set_session_settings after logon on dbmon.schema
2 begin
3 execute immediate 'alter session set optimizer_mode=first_rows_10';
4 end;
5 /
Trigger created.
2.查看当前会话参数，并切换用户后再次查看回话参数
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> conn dbmon/dbmon
Connected.
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string FIRST_ROWS_10
