[20210203]19c登录连接改变一些参数.txt

--//昨天看了https://blog.dbi-services.com/19c-serverless-logon-trigger/,19c可以实现一种特殊的方式登录时直接改变oracle参
--//数.正好我们单位买了一台ODA,安装19c,可以测试看看.

1.环境:
> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试:
> show parameter optimizer_mode
NAME           TYPE   VALUE
-------------- ------ ----------
optimizer_mode string ALL_ROWS

> connect sys/XXXXXYYY@(DESCRIPTION=(CONNECT_DATA=(SESSION_SETTINGS=(optimizer_mode=first_rows_10))(SERVER=DEDICATED)(SERVICE_NAME=dyhis))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.46)(PORT=1521))) as sysdba

SYS@dyhis> show parameter optimizer_mode
NAME           TYPE    VALUE
-------------- ------- --------------
optimizer_mode string  FIRST_ROWS_10

--//通过连接串的SESSION_SETTINGS=(optimizer_mode=first_rows_10),改变了参数.

connect sys/XXXXXYYY@(DESCRIPTION=(CONNECT_DATA=(SESSION_SETTINGS=(sql_trace=true)(tracefile_identifier=franck))(SERVER=DEDICATED)(SERVICE_NAME=dyhis))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.46)(PORT=1521))) as sysdba

SYS@dyhis> show parameter sql_trace
NAME      TYPE    VALUE
--------- ------- -------
sql_trace boolean TRUE

SYS@dyhis> select value from v$diag_info where name='Default Trace File';
VALUE
-------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dyhis/dyhis2/trace/dyhis2_ora_29162_FRANCK.trc

--//查看跟踪文件无意间发现oracle的一个触发器:
=====================
PARSING IN CURSOR #140653736157352 len=391 dep=1 uid=0 oct=47 lid=0 tim=4805081161893 hv=2286069890 ad='b74dde98' sqlid='6s9sc8q445a42'
declare
         pdb_name varchar2(64);
         begin
           DBMS_SYSTEM.get_env ('ORACLE_PDB_SID', pdb_name);
           if(pdb_name is not null)
            then
              EXECUTE IMMEDIATE 'alter session set container = ' || '"' || pdb_name
                    || '"';
            end if;
         exception
           when others then
           NULL;
         end dbms_set_pdb;
END OF STMT
PARSE #140653736157352:c=29,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=4805081161893
EXEC #140653736157352:c=157,e=158,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=0,tim=4805081162078
CLOSE #140653736157352:c=12,e=12,dep=1,type=1,tim=4805081162119
=====================
--//你可以定义一个环境变量ORACLE_PDB_SID,这样登录直接进入某个PDB数据库.

--//作者还提供查询支持那些功能的命令:
$ strings $ORACLE_HOME/bin/oracle | grep ^DESCRIPTION/CONNECT_DATA/ | cut -d/ -f3-
COLOCATION
SID
NUMA_PG
CID/PROGRAM
SESSION_SETTINGS
MODULE_NAME
MODULE_ACTION
SERVICE_NAME
GLOBAL_NAME
CID/USER
REGION
PRESENTATION
SERVER
DUPLICITY
SEPARATE_PROCESS
SERVER_WAIT_TIMEOUT
COMMAND
DESIG
ORACLE_HOME
FAILOVER_MODE
FAILOVER_MODE/BACKUP
SESSION_STATE
USE_DBROUTER
RPC

--//作者还给出一些例子不再测试了.