DBMS SERVICE

DBMS_SERVICE is a PL/SQL package that can be used to define and manage database services for a single instance. To define a RAC service (multi-instance) see srvctl.

Please note that services can also be defined from the DBCA GUI.

History

This package was introduced with Oracle 10g Release 1. Starting with 10g release 2 one can also set-up a service with TAF fail-over settings.

Functions

dbms_service.CREATE_SERVICE

SQL> exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'orderentry', NETWORK_NAME=>'db11g')

dbms_service.MODIFY_SERVICE

Modify an existing service. Example:

SQL> exec DBMS_SERVICE.MODIFY_SERVICE( -
>         service_name => 'o11gr1', -
>         goal => DBMS_SERVICE.GOAL_THROUGHPUT, -
>         failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC, -
>         failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT, -
>         failover_retries => 10, -
>         failover_delay => 1, -
>         clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);
PL/SQL procedure successfully completed.

dbms_service.START_SERVICE

SQL> exec dbms_service.START_SERVICE('orderentry')
SQL> show parameter service

dbms_service.STOP_SERVICE

SQL> exec dbms_service.STOP_SERVICE('orderentry')

dbms_service.DELETE_SERVICE

SQL> exec dbms_service.DELETE_SERVICE('orderentry')

Monitor

Use the following dictionary views to monitor services:

• dba_services - All defined services
• gv$active_services - All active (started) services

To see what service a session is connected to:

SELECT username, program, machine, service_name FROM gv$session;