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

[20211012]sqlnet.ora USE_DEDICATED_SERVER=on.txt

相關文章