Database Reference
In-Depth Information
DBMS_SERVICE.DISCONNECT_SESSION
affects all sessions of the local instance using a certain
service, such that you need to connect to the instance hosting the client in order to disconnect
all sessions. This procedure has an undocumented parameter
DISCONNECT_OPTION
. The default
value for this parameter is the numeric constant
DBMS_SERVICE.POST_TRANSACTION
. It can also
take the value
DBMS_SERVICE.IMMEDIATE
. These constants have the same meaning as the keywords
POST_TRANSACTION
and
IMMEDIATE
supported by the SQL statement
ALTER
SYSTEM
DISCONNECT
SESSION
. Let's disconnect all the sessions that were established via the service TAF_INST_SVC.
SQL> EXEC dbms_service.disconnect_session('TAF_INST_SVC')
Beware that
DBMS_SERVICE.DISCONNECT_SESSION
completes successfully, even when a non-
existent service name is passed. Session disconnection takes effect immediately, except for
sessions that have an open transaction.
SQL> SELECT inst_id, sid, serial#, audsid, logon_time, service_name,
failover_type, failover_method, failed_over
FROM gv$session
WHERE username='APP_USER';
INST_ID SID SERIAL# AUDSID LOGON_TIME SERVICE_NAME
------- --- ------- ------ ------------------- ------------
2 143 4139 120036 05.08.2007 19:27:58 TAF_INST_SVC
FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------- --------------- -----------
SELECT BASIC NO
Once APP_USER attempts to retrieve the remaining rows from the
SELECT
statement, the
disconnection is detected and a new connection to an instance, which still offers the requested
service, is opened. Repeating the
SELECT
from
GV$SESSION
yields this:
INST_ID SID SERIAL# AUDSID LOGON_TIME SERVICE_NAME
------- --- ------- ------ ------------------- ------------
1 151 1219 130018 05.08.2007 19:31:35 TAF_INST_SVC
FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
------------- --------------- -----------
SELECT BASIC YES
APP_USER's session has been reconnected to instance 1, as evidenced by
INST_ID=1
, a
different
AUDSID
, and a later
LOGON_TIME
. The value of
AUDSID
must not necessarily increment
after a reconnect due to sequence caching in all instances. TAF also reconnects disconnected
sessions that had open transactions. For such sessions, disconnection takes place when they
commit or roll back. The procedure
DBMS_SERVICE.DISCONNECT_SESSION
behaves in the same
way as
ALTER
SYSTEM
DISCONNECT
SESSION
POST_TRANSACTION
, in that it allows open transactions
to complete before disconnecting the session. In fact, it is no more than a layer on top of
ALTER
SYSTEM
DISCONNECT
SESSION
. This undocumented fact is given away by the source file
$ORACLE_HOME/
rdbms/admin/dbmssrv.sql
.
Integration between the new features of
DBMS_SERVICE
and the vintage features associated
with the parameter
SERVICE_NAMES
is not anywhere near seamless. The statement
ALTER
SYSTEM
SET
SERVICE_NAMES
adds a service to
DBA_SERVICES
, whereas removal of the same service from
SERVICE_NAMES
does not remove it from
DBA_SERVICES
. It merely stops the service and removes
it from
V$ACTIVE_SERVICES
.
