Oracle DBMS_SERVICE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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.
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','MLIBSERV' , 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

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-2014 Daniel A. Morgan All Rights Reserved