[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模式.