Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)

Part Number E10577-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

127 DBMS_SERVICE

The DBMS_SERVICE package lets you create, delete, activate and deactivate services for a single instance.

The chapter contains the following topics:


Using DBMS_SERVICE

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


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.

See Also:

For more information about Oracle Real Application Clusters, Oracle Real Application Clusters Administration and Deployment Guide.

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.


Deprecated Subprograms

For Oracle Real Applications Clusters (RAC) databases, the following DBMS_SERVICE procedures should be deprecated in 11.2, and srvctl should be used instead:

The same is true for single-instance databases managed by Oracle Restart or Oracle Clusterware.

This is because the service attributes are stored in CRS and those attributes overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes.


Constants

The DBMS_SERVICE package uses the constants shown in following tables

Table 127-1 Constants used in Calling Arguments

Name Type Value Description

GOAL_NONE

NUMBER

0

Disables Load Balancing Advisory

GOAL_SERVICE_TIME

NUMBER

1

Load Balancing Advisory is based on elapsed time for work done in the service plus available bandwidth to the service

GOAL_THROUGHPUT

NUMBER

2

Load Balancing Advisory is based on the rate that work is completed in the service plus available bandwidth to the service


Table 127-2 Constants used in Connection Balancing Goal Arguments

Name Type Value Description

CLB_GOAL_SHORT

NUMBER

1

Connection load balancing uses Load Balancing Advisory, when Load Balancing Advisory is enabled (either goal_service_time or goal_throughput). When GOAL=NONE (no load balancing advisory), connection load balancing uses an abridged advice based on CPU utilization.

CLB_GOAL_LONG

NUMBER

2

Balances the number of connections per instance using session count per service. This setting is recommended for applications with long connections such as forms. This setting can be used with Load Balancing Advisory when the connection pool is sized to accommodate gravitation within the pool itself (without adding or removing connections). The latter is the most efficient design.


Table 127-3 Constants used in TAF Failover Attribute Arguments

Name Type Value Description

FAILOVER_METHOD_NONE

VARCHAR2

0

Server side TAF is not enabled for this service

FAILOVER_METHOD_BASIC

VARCHAR2

1

Server side TAF method is BASIC. BASIC is the only value currently supported. This means that a new connection is established at failure time. It is not possible to pre-establish a backup connection. (which is to say, PRECONNECT is not supported)

FAILOVER_TYPE_NONE

NUMBER

 

Server side TAF type is NONE

FAILOVER_TYPE_SESSION

NUMBER

 

Server side TAF failover type is SESSION. At failure time, if the failover type is SESSION, TAF will re-connect to a surviving node and re-establish a vanilla database session. Customizations (for example, ALTER SESSION) must be re-executed in a failover callback.

FAILOVER_TYPE_SELECT

NUMBER

 

Server side TAF failover type is SELECT

FAILOVER_RETRIES

NUMBER

 

Number of retries to use during a failover. Specifies the number of times that TAF should attempt the re-connect and re-authenticate pair. The value must be integral and greater than 0. The maximum value is UB4MAXVAL

FAILOVER_DELAY

NUMBER

 

Number of seconds delay before trying to failover. Specifies the delay (in seconds) that TAF will incur if the re-connect / re-authentication fails. The value must be integral and greater than 0. The maximum value is UB4MAXVAL.


Usage Notes


Exceptions

The following table lists the exceptions raised by DBMS_SERVICE package.

Table 127-4 DBMS_SERVICE Exceptions

Exception Error Code Description

NULL_SERVICE_NAME

44301

The service name argument was found to be NULL

NULL_NETWORK_NAME

44302

The network name argument was found to be NULL

SERVICE_EXISTS

44303

This service name was already in existence

SERVICE_DOES_NOT_EXIST

44304

The specified service was not in existence

SERVICE_IN_USE

44305

The specified service was running

SERVICE_NAME_TOO_LONG

44306

The service name was too long

NETWORK_PREFIX_TOO_LONG

44307

The network name, excluding the domain, was too long

NOT_INITIALIZED

44308

The services layer was not yet initialized

GENERAL_FAILURE

44309

There was an unknown failure

MAX_SERVICES_EXCEEDED

44310

The maximum number of services has been reached

SERVICE_NOT_RUNNING

44311

The specified service was not running

DATABASE_CLOSED

44312

The database was closed

INVALID_INSTANCE

44313

The instance name argument was not valid

NETWORK_EXISTS

44314

The network name was already in existence

NULL_ATTRIBUTES

44315

All attributes specified were NULL

INVALID_ARGUMENT

44316

Invalid argument supplied

DATABASE_READONLY

44317

The database is open read-only

MAX_SN_LENGTH

44318

The total length of all running service network names exceeded the maximum allowable length



Summary of DBMS_SERVICE Subprograms

Table 127-5 DBMS_SERVICE Package Subprograms

Subprogram Description

CREATE_SERVICE Procedure

Creates service

DELETE_SERVICE Procedure

Deletes service

DISCONNECT_SESSION Procedure

Disconnects service

MODIFY_SERVICE Procedure

Modifies service

START_SERVICE Procedure

Activates service

STOP_SERVICE Procedure

Stops service



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.

Note:

This procedure is deprecated in databases managed by Oracle Clusterware and Oracle Restart with Release 11.2. While the procedure remains available in the package, Oracle recommends using srvctl to manage services. This is because the service attributes are stored in CRS by srvctl, and overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes and therefore are not persistent, though they do take effect immediately, until the service is next started with srvctl.

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

Table 127-6 CREATE_SERVICE Procedure Parameters

Parameter Description

service_name

Name of the service limited to 64 characters in the Data Dictionary

network_name

The network name of the service as used in SQLNet connect descriptors for client connections. This is limited to the NET service_names character set (see Oracle Database Net Services Reference).

goal

The workload management goal directive for the service. Valid values:

  • DBMS_SERVICE.GOAL_SERVICE_TIME

  • DBMS_SERVICE.GOAL_THROUGHPUT

  • DBMS_SERVICE.GOAL_NONE

dtp

Declares the service to be for DTP or distributed transactions including XA transactions

aq_ha_notifications

Determines whether HA events are sent through AQ for this service

failover_method

The TAF failover method for the service

failover_type

The TAF failover type for the service

failover_retries

The TAF failover retries for the service

failover_delay

The TAF failover delay for the service

clb_goal

Method used for Connection Load Balancing (see Table 127-2, "Constants used in Connection Balancing Goal Arguments")


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

Table 127-7 DELETE_SERVICE Procedure Parameters

Parameter Description

service_name

Name of the service limited to 64 characters in the Data Dictionary


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,
   disconnect_option    IN NUMBER DEFAULT POST_TRANSACTION); 

Parameters

Table 127-8 DISCONNECT_SESSION Procedure Parameters

Parameter Description

service_name

Name of the service limited to 64 characters in the Data Dictionary

disconnect_option

There are two options, package constants expressed as NUMBER:

  • POST_TRANSACTION = 0 : the session will disconnect after the current transaction commits or rolls back

  • IMMEDIATE = 1 : sessions will be disconnected immediately

Note: IMMEDIATE or POST_TRANSACTION will be automatically translated as 1 and 0 respectively. However, passing-in a string literal (quoted using either the ' or " characters, such as "IMMEDIATE" or 'POST_TRANSACTION') will raise an error.


Usage Notes

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.

Note:

This procedure is deprecated in databases managed by Oracle Clusterware and Oracle Restart with Release 11.2. While the procedure remains available in the package, Oracle recommends using srvctl to manage services. This is because the service attributes are stored in CRS by srvctl, and overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes and therefore are not persistent, though they do take effect immediately, until the service is next started with srvctl.

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

Table 127-9 MODIFY_SERVICE Procedure Parameters

Parameter Description

service_name

Name of the service limited to 64 characters in the Data Dictionary

goal

The workload management goal directive for the service. Valid values:

  • DBMS_SERVICE.GOAL_SERVICE_TIME

  • DBMS_SERVICE.GOAL_THROUGHPUT

  • DBMS_SERVICE.GOAL_NONE

dtp

Declares the service to be for DTP or distributed transactions including XA transactions

aq_ha_notifications

Determines whether HA events are sent through AQ for this service

failover_method

The TAF failover method for the service

failover_type

The TAF failover type for the service

failover_retries

The TAF failover retries for the service

failover_delay

The TAF failover delay for the service

clb_goal

Method used for Connection Load Balancing (see Table 127-2, "Constants used in Connection Balancing Goal Arguments")


Usage Notes

If you are using Clustered Managed Services with Oracle Clusterware, or using Oracle Restart with your single instance database, you should modify services using the srvctl command rather than DBMS_SERVICE. When the service is started by Oracle Clusterware or Oracle Restart, the service will be modified in the database to match the resource defined to either Oracle Clusterware or Oracle Restart. Any changes made with DBMS_SERVICE will be lost unless they are also made with the corresponding srvctl command.


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.

Note:

This procedure is deprecated in databases managed by Oracle Clusterware and Oracle Restart with Release 11.2. While the procedure remains available in the package, Oracle recommends using srvctl to manage services. This is because the service attributes are stored in CRS by srvctl, and overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes and therefore are not persistent, though they do take effect immediately, until the service is next started with srvctl.

Syntax

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

Parameters

Table 127-10 START_SERVICE Procedure Parameters

Parameter Description

service_name

Name of the service limited to 64 characters in the Data Dictionary

instance_name

Name of the instance where the service should be activated (optional). The instance on which to start the service. NULL results in starting of the service on the local instance. In single instance this can only be the current instance or NULL. Specify DBMS_SERVICE.ALL_INSTANCES to start the service on all configured instances.


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.

Note:

This procedure is deprecated in databases managed by Oracle Clusterware and Oracle Restart with Release 11.2. While the procedure remains available in the package, Oracle recommends using srvctl to manage services. This is because the service attributes are stored in CRS by srvctl, and overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes and therefore are not persistent, though they do take effect immediately, until the service is next started with srvctl.

Syntax

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

Parameters

Table 127-11 STOP_SERVICE Procedure Parameters

Parameter Description

service_name

Name of the service limited to 64 characters in the Data Dictionary

instance_name

Name of the instance where the service should be stopped (optional). The instance on which to stop the service. NULL results in stopping of the service locally. In single instance this can only be the current instance or NULL. The default in RAC and exclusive case is NULL. Specify DBMS_SERVICE.ALL_INSTANCES to stop the service on all configured instances.


Examples

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