Oracle DBMS_SERVICE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Enables an application to manage services and sessions connected via a service name.
AUTHID DEFINER
Constants
Name Data Type Value
Calling Arguments
GOAL_NONE NUMBER 0
GOAL_SERVICE_TIME NUMBER 1
GOAL_THROUGHPUT NUMBER 2
Connection Balancing Goal
CLB_GOAL_SHORT (load balance CPU) NUMBER 1
CLB_GOAL_LONG (load balance connections) NUMBER 2
Disconnect Session
POST_TRANSACTION NUMBER 0
IMMEDIATE NUMBER 1
Other Attributes
ALL_INSTANCES VARCHAR2(2) '*'
TAF Failover Attributes
FAILOVER_METHOD_NONE VARCHAR2(5) 'NONE'
FAILOVER_METHOD_BASIC VARCHAR2(6) 'BASIC'
FAILOVER_TYPE_NONE VARCHAR2(5) 'NONE'
FAILOVER_TYPE_SESSION VARCHAR2(8) 'SESSION'
FAILOVER_TYPE_SELECT VARCHAR2(7) 'SELECT'
Data Types TYPE svc_parameter_array IS TABLE OF VARCHAR2(30)
INDEX BY VARCHAR2(100);

CREATE TYPE svc_parameter_t IS OBJECT (
param_name VARCHAR2(30),
param_value VARCHAR2(100));
/

CREATE OR REPLACE TYPE svc_parameter_list_t
IS VARRAY(30) OF svc_parameter_t;
/
Dependencies
DBMS_GSM_CLOUDADMIN DBMS_SERVICE_PRVT_LIB SVC_PARAMETER_LIST_T
DBMS_SERVICE_CONST GV$ACTIVE_SERVICES SVC_PARAMETER_T
DBMS_SERVICE_ERR PLITBLM V$SESSION
DBMS_SERVICE_PRVT SERVICE$  
Documented Yes
Exceptions
Error Code Reason
ORA-44301 err_null_service_name
ORA-44302 err_null_network_name
ORA-44303 err_service_exists
ORA-44304 err_service_does_not_exist
ORA-44305 err_service_in_use
ORA-44306 err_service_name_too_long
ORA-44307 err_network_prefix_too_long
ORA-44308 err_not_initialized
ORA-44309 err_general_failure
ORA-44310  err_max_services_exceeded
ORA-44311 err_service_not_running
ORA-44312 err_database_closed
ORA-44313 err_invalid_instance
ORA-44314 err_network_exists
ORA-44315 err_null_attributes
ORA-44316 err_invalid_argument
ORA-44317 err_database_readonly
ORA-44318 err_max_sn_length
ORA-44319 err_aq_service
ORA-44320 err_glb_service
ORA-44771 err_invalid_pdb_name
ORA-44772 err_crs_api
ORA-44773 err_pdb_closed
ORA-44774 err_pdb_invalid
ORA-44775 err_pdb_name
ORA-44776 err_pdb_exp
ORA-44777 err_pdb_fail
ORA-44778 err_tg_rettm
ORA-44779 err_tg_repto
ORA-44780 err_tg_co
ORA-44781 err_tg_aq
ORA-44782 err_crs_fail
ORA-44783 err_mxrlbsvc
ORA-44784 err_delint
ORA-44785 err_tg_dbsvc
First Available 10gR1
Object Privileges GRANT execute ON dbms_service TO <schema_name>;
GRANT alter system TO <schema_name>;
GRANT select ON v_$session TO <schema_name>;
GRANT execute ON dbms_service TO uwclass;
GRANT alter system TO uwclass;
GRANT select ON v_$session TO uwclass;
Security Model Owned by SYS with EXECUTE granted to the DBA and GSMADMIN_INTERNAL roles and SYSRAC.
Source {ORACLE_HOME}/rdbms/admin/dbmssrv.sql
Subprograms
 
CREATE_SERVICE (new 12.1 overload)
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_names parameter or by means of ALTER SYSTEM SET service_names

Overload 1
dbms_service.create_service(
service_name    IN VARCHAR2,
network_name    IN VARCHAR2,
parameter_array IN svc_parameter_array);
DECLARE
 param_array dbms_service.svc_parameter_array;
BEGIN
  param_array('FAILOVER_TYPE') := 'TRANSACTION';
  param_array('REPLAY_INITIATION_TIMEOUT'):=900;
  params('RETENTION_TIMEOUT') :=86400;
  params('FAILOVER_DELAY') :=5;
  params('FAILOVER_RETRIES') :=5;
  params('COMMIT_OUTCOME') :='TRUE';
  params('aq_ha_notifications') :='TRUE';

  dbms_service.create_service('MLIBSERV','MLIBNET' , param_array);
END;
/
Overload 2 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,
edition             IN VARCHAR2 DEFAULT NULL);
See Demos Below
 
DELETE_SERVICE
Deletes a service from the data dictionary dbms_service.delete_service(service_name IN VARCHAR2);
See Demos Below
 
DISCONNECT_SESSION
Disconnects sessions with the named service as the current instance dbms_service.disconnect_session(
service_name      IN VARCHAR2,
disconnect_option IN NUMBER DEFAULT post_transaction);
exec dbms_service.disconnect_session('MLIBSERV');
 
IMPORT_CRS_SERVICES (new 12.1)
Imports all the CRS managed services of a PDB that have previously been exported to the user's currently connected PDB dbms_service.import_crs_services;
exec dbms_service.import_crs_services;
 
MODIFY_SERVICE (new 12.1 overload)
Modify an existing service

Used for managing RAC and DataGuard service failovers

Overload 1
dbms_service.modify_service(
service_name    IN VARCHAR2,
parameter_array IN svc_parameter_array);
exec dbms_service.create_service('MLIBSERV', 'MLIBSERV');

DECLARE
 param_array dbms_service.svc_parameter_array;
BEGIN
  param_array('FAILOVER_TYPE') := 'TRANSACTION';
  param_array('REPLAY_INITIATION_TIMEOUT'):=900;
  params('RETENTION_TIMEOUT') :=86400;
  params('FAILOVER_DELAY') :=5;
  params('FAILOVER_RETRIES') :=5;
  params('COMMIT_OUTCOME') :='TRUE';
  params('aq_ha_notifications') :='TRUE';

  dbms_service.modify_service('MLIBSERV', param_array);
END;
/
Overload 2 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,
edition             IN VARCHAR2 DEFAULT NULL,
modify_edition      IN BOOLEAN  DEFAULT FALSE);
exec dbms_service.modify_service(
service_name        => 'MLIBSERV',
goal                => DBMS_SERVICE.GOAL_THROUGHPUT,
aq_ha_notifications => TRUE,
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,
edition             => 'DEMO_ED'
modify_edition      =>
TRUE);
 
START_SERVICE
Activate a service dbms_service.start_service(
service_name  IN VARCHAR2,
instance_name IN VARCHAR2 DEFAULT NULL);
See Demos Below
 
STOP_SERVICE
Stop a service dbms_service.stop_service(
service_name  IN VARCHAR2,
instance_name IN VARCHAR2 DEFAULT NULL);
See Demos Below
 
DBMS_SERVICE Demo
Services 101 conn sys@pdbdev as sysdba

set linesize 121
col username format a20
col schemaname format a20
col program format a20
col service_name format a20

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

desc dba_services

col name format a42
col network_name format a42

SELECT name,network_name, creation_date, clb_goal
FROM dba_services;

-- for RAC
col failover_method format a30
col failover_type format a30

SELECT name, aq_ha_notifications, failover_method, failover_type
FROM dba_services;
Function Demo conn sys@pdbdev as sysdba

set linesize 121
col name format a30
col network_name format a30

SELECT service_id, name,network_name, creation_date
FROM dba_services;

exec dbms_service.create_service('MLIBSERV', 'mlib.org');

SELECT service_id, name,network_name, creation_date
FROM dba_services;

SELECT service_id, name, network_name
FROM gv$active_services;

-- exec dbms_service.start_service('MLIBSERV', 'orabase');

-- SELECT service_id, name, network_name
-- FROM gv$active_services;

exec dbms_service.stop_service('MLIBSERV', 'orabase');

SELECT service_id, name, network_name
FROM gv$active_services;

SELECT service_id, name,network_name, creation_date
FROM dba_services;

exec dbms_service.delete_service('MLIBSERV');

SELECT service_id, name,network_name, creation_date
FROM dba_services;

Related Topics
DBMS_MONITOR
DBMS_SCHEDULER
DBMS_SERVICE_CONST
DBMS_SERVICE_ERR
DBMS_SERVICE_PRVT
How Can I #12
Packages
Real Application Clusters RAC
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved