[20210203]19c登入連線改變一些引數.txt

[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

--//作者還給出一些例子不再測試了.