116 DBMS_SERVICE

Using DBMS_SERVICE

This section contains topics which relate to using the DBMS_SERVICE package.

• Overview

• Security Model

• Constants

• Exceptions

---

Overview

DBMS_SERVICE supports the management of services in the RDBMS for the purposes of workload measurement, management, prioritization, and XA/and distributed transaction management.

Oracle Real Application Clusters (RAC) has a functionality to manage service names across instances. This package allows the creation, deletion, starting and stopping of services in both RAC and a single instance. Additionally it provides the ability to disconnect all sessions which connect to the instance with a service name when RAC removes that service name from the instance.

---

Security Model

Privileges

The client using this package should have the ALTER SYSTEM execution privilege and the V$SESSION table read privilege.

Schemas

This package should be installed under SYS schema.

Roles

The EXECUTE privilege of the package is granted to the DBA role only.

---

Constants

The DBMS_SERVICE package uses the constants shown in following tables

• Constants used in calling arguments are described in Table 116-1, "Constants used in Calling Arguments"

• Constants used in connection balancing goal arguments are described inTable 116-2, "Constants used in Connection Balancing Goal Arguments"

• Constants used TAF failover attribute arguments are described inTable 116-3, "Constants used in TAF Failover Attribute Arguments"

Usage Notes

• If a TAF callback has been registered, then the failover retries and failover delay are ignored. If an error occurs, TAF will continue to re-attempt the connect and authentication as long as the callback returns a value of OCI_FO_RETRY. Any delay should be coded into the callback logic

• Server side TAF settings override client-side counterparts that might be configured in TNS connect descriptors. If TAF is not configured on the client side, then at a minimum, the failover type must be set to enable TAF. If the failover type is set on the server side, then the failover method will default to BASIC. Delay and retries are optional and may be specified independently.

---

Exceptions

The following table lists the exceptions raised by DBMS_SERVICE package.

Summary of DBMS_SERVICE Subprograms

---

CREATE_SERVICE Procedure

This procedure creates a service name in the data dictionary. Services are also created in the data dictionary implicitly when you set the service in the service_name parameter or by means of the ALTER SYSTEM SET SERVICE_NAMES command.

Syntax

DBMS_SERVICE.CREATE_SERVICE(
   service_name        IN VARCHAR2, 
   network_name        IN VARCHAR2,
   goal                IN NUMBER DEFAULT NULL,
   dtp                 IN BOOLEAN DEFAULT NULL,
   aq_ha_notifications IN BOOLEAN DEFAULT NULL,
   failover_method     IN VARCHAR2 DEFAULT NULL,
   failover_type       IN VARCHAR2 DEFAULT NULL,
   failover_retries    IN NUMBER DEFAULT NULL,
   failover_delay      IN NUMBER DEFAULT NULL,
   clb_goal            IN NUMBER DEFAULT NULL);

Parameters

Examples

DBMS_SERVICE.CREATE_SERVICE('ernie.us.oracle.com','ernie.us.oracle.com');

---

DELETE_SERVICE Procedure

This procedure deletes a service from the data dictionary.

Syntax

DBMS_SERVICE.DELETE_SERVICE(
   service_name   IN VARCHAR2);

Parameters

Examples

DBMS_SERVICE.DELETE_SERVICE('ernie.us.oracle.com');

---

DISCONNECT_SESSION Procedure

This procedure disconnects sessions with the named service at the current instance.

Syntax

DBMS_SERVICE.DISCONNECT_SESSION(
   service_name   IN VARCHAR2);

Parameters

Usage Notes

• This procedure can be used in the context of a single instance as well as with Oracle Real Application Clusters.

• This subprogram does not return until all corresponding sessions are disconnected. Therefore, use the DBMS_JOB package or put the SQL session in background if the caller does not want to wait for all corresponding sessions disconnected.

Examples

This disconnects sessions with service_name 'ernie.us.oracle.com'.

DBMS_SERVICE.DISCONNECT_SESSION('ernie.us.oracle.com');

---

MODIFY_SERVICE Procedure

This procedure modifies an existing service.

Syntax

DBMS_SERVICE.MODIFY_SERVICE(
   service_name        IN VARCHAR2, 
   goal                IN NUMBER DEFAULT NULL,
   dtp                 IN BOOLEAN DEFAULT NULL,
   aq_ha_notifications IN BOOLEAN DEFAULT NULL,
   failover_method     IN VARCHAR2 DEFAULT NULL,
   failover_type       IN VARCHAR2 DEFAULT NULL,
   failover_retries    IN NUMBER DEFAULT NULL,
   failover_delay      IN NUMBER DEFAULT NULL,
   clb_goal            IN NUMBER DEFAULT NULL);

Parameters

---

START_SERVICE Procedure

This procedure starts a service. This procedure alters the service_name IOP to contain this service_name. In RAC, implementing this option will act on the instance specified.

Syntax

DBMS_SERVICE.START_SERVICE(
   service_name  IN VARCHAR2, 
   instance_name IN VARCHAR2);

Parameters

Examples

DBMS_SERVICE.START_SERVICE('ernie.us.oracle.com');

---

STOP_SERVICE Procedure

This procedure stops a service, altering the service_name IOP to remove this service_name. In RAC this will call out to CRS to stop the service optionally on the instance specified.

Syntax

DBMS_SERVICE.STOP_SERVICE(
   service_name   IN VARCHAR2,
   instance_name  IN VARCHAR2);

Parameters

Examples

DBMS_SERVICE.STOP_SERVICE('ernie.us.oracle.com');