Databases Reference
In-Depth Information
To delete a service, it must be stopped on all instances. The default service, which matches
the name of the database suffixed by the database domain, cannot be deleted. It appears as
though it could be stopped with
DBMS_SERVICE.STOP_SERVICE
, which completes without error, but
crosschecking with the services summary from
lsnrctl
services
listener_name
and
V$ACTIVE_
SERVICES
reveals that it was not stopped. It would have been more appropriate to introduce a
new error message such as “default service cannot be stopped”. For example, my DBMS instance
with the settings
db_name=TEN
and
db_domain=oradbpro.com
automatically has an instance service
name
TEN.oradbpro.com
in
DBA_SERVICES
and
V$ACTIVE_SERVICES
, which can neither be stopped
nor deleted.
Lessons Learned
Disruptions in database service due to scheduled maintenance or manual load rebalancing
may be much ameliorated by configuring TAF and disconnecting sessions gracefully. Session
disconnection can occur on three levels: instance level, service level, and session level. The
commands and levels are summarized in Table 33-1.
Table 33-1.
Statements for Session Disconnection
Level
SQL Statement or PL/SQL
Procedure Call
Client
Program
SHUTDOWN
TRANSACTIONAL
Instance
SQL*Plus,
Oracle11
g
JDBC
DBMS_SERVICE.DISCONNECT_SESSION(
Service
SERVICE_NAME=>'
name
',
DISCONNECT_OPTION=>
option
);
6
Any
Session
ALTER
SYSTEM
DISCONNECT
SESSION
'sid,
serial#'
POST_TRANSACTION;
Any
While graceful disconnection at instance and session level was already implemented in
Oracle9
i
, disconnection at service level was first introduced in Oracle10
g
. The features may
even be used to hide an instance restart from database clients, given that the clients are configured
and known to work with TAF. In Oracle10
g
and subsequent releases, TAF should be configured on
the server-side with
DBMS_SERVICE
.
6
The parameter
DISCONNECT_OPTION
is not available in release 10.2.0.1. The default value of this parameter
is
DBMS_SERVICE.POST_TRANSACTION
. The disconnect option
DBMS_SERVICE.IMMEDIATE
disconnects sessions
immediately.
6.
