[20211012]sqlnet.ora USE_DEDICATED_SERVER=on.txt

[20211012]sqlnet.ora USE_DEDICATED_SERVER=on.txt . You can configure the client profile file, sqlnet.ora, with USE_DEDICATED_SERVER=on. This adds (SERVER=dedicated) to the CONNECT_DATA section of the connect descriptor the client uses. Note: If USE_DEDICATED_SERVER is set to ON, then existing (SERVER=value) entries in connect descriptors are overwritten with (SERVER=dedicated). --//我的理解就是在clien端的sqlnet.ora配置 USE_DEDICATED_SERVER=on --//这样即使连接描述存在SERVER=XXXX，将被overwritten with (SERVER=dedicated). 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 SCOTT@book> show parameter dispatchers NAME            TYPE    VALUE --------------- ------- ---------------------------------------------------------------------------------------------------- dispatchers     string  (PROTOCOL=TCP) (SERVICE=bookXDB) max_dispatchers integer --//服务名bookXDB支持共享模式。 2.在我的客户端执行： d:\>sqlplus scott/book@192.168.100.78:1521/bookxdb SCOTT@192.168.100.78:1521/bookxdb> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------        281          1 5116:6256                SHARED    47705                     20          1 alter system kill session '281,1' immediate; --//SERVER=SHARED --//修改clien端的sqlnet.ora文件，加入： USE_DEDICATED_SERVER=on d:\>sqlplus -s -l scott/book@192.168.100.78:1521/bookxdb ERROR: ORA-12520: TNS:listener could not find available handler for requested type of server SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus --//因为在客户端的sqlnet.ora加入了USE_DEDICATED_SERVER=on,限制仅仅以专用模式登录DEDICATED. d:\>sqlplus  scott/book@192.168.100.78:1521/book SCOTT@192.168.100.78:1521/book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------          1         19 5600:3748                DEDICATED 47781                     24          9 alter system kill session '1,19' immediate; 3.重温以前一个不好的习惯,我以前也会把book服务名配置共享模式,这个我多次提到是一个不好的习惯. --//建议最好共享模式的服务名单独配置 SYS@book> alter system set dispatchers="(PROTOCOL=TCP) (SERVICE=book,bookXDB)" scope=memory; System altered. --//这样book服务名即支持共享也支持专用模式登录. d:\>sqlplus  scott/book@192.168.100.78:1521/book SCOTT@192.168.100.78:1521/book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------          1         21 6628:7344                DEDICATED 47809                     24         10 alter system kill session '1,21' immediate; --//SERVER=DEDICATED. --//注解clien端的sqlnet.ora文件的USE_DEDICATED_SERVER=on,继续测试: d:\>sqlplus  scott/book@192.168.100.78:1521/book SCOTT@192.168.100.78:1521/book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------        281          3 3664:1212                SHARED    47705                     20          1 alter system kill session '281,3' immediate; --//你可以发现ezconnect缺省选择优先shared模式登录(当然如果两者都支持的情况下),除非明确指明登录模式. --//这也是我为什么建议共享模式的服务名要单独配置. d:\>sqlplus  scott/book@192.168.100.78:1521/book:DEDICATED SCOTT@192.168.100.78:1521/book:DEDICATED> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------          1         23 864:3176                 DEDICATED 47821                     24         11 alter system kill session '1,23' immediate; 4.继续: --//在客户端tnsnames.ora配置: 78S =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))     )     (CONNECT_DATA =       (SID = book)       (SERVER = SHARED)     )   ) d:\>sqlplus  scott/book@78s SCOTT@78s> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------        281          5 6256:7020                SHARED    47705                     20          1 alter system kill session '281,5' immediate; --//SERVER=SHARED        --//取消sqlnet.ora的如下注解: USE_DEDICATED_SERVER=on --//重复测试: d:\>sqlplus  scott/book@78s SCOTT@78s> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------          1         27 7048:6020                DEDICATED 47859                     24         13 alter system kill session '1,27' immediate; --//SERVER=DEDICATED. --//即使我配置时使用SERVER = SHARED,但是在sqlnet.ora加入USE_DEDICATED_SERVER=on,这样该参数被overwritten,采用DEDICATED模式.