Oracle DBMS_SERVICE
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
NOREPLAY NUMBER 2
Other Attributes
ALL_INSTANCES VARCHAR2(2) '*'
TAF Failover Methods
FAILOVER_METHOD_NONE VARCHAR2(5) 'NONE'
FAILOVER_METHOD_BASIC VARCHAR2(6) 'BASIC'
TAF Failover Restore
FAILOVER_RESTORE_NONE VARCHAR2(5) 'NONE'
FAILOVER_RESTORE_BASIC VARCHAR2(6) 'LEVEL1'
TAF Failover Stop Option Attributes
STOP_OPTION_NONE VARCHAR2(5) 'NONE'
STOP_OPTION_IMMEDIATE VARCHAR2(6) 'IMMEDIATE'
STOP_OPTION_TRANSACTIONAL VARCHAR2(6) 'TRANSACTIONAL'
TAF Failover Types
FAILOVER_TYPE_NONE VARCHAR2(5) 'NONE'
FAILOVER_TYPE_SESSION VARCHAR2(8) 'SESSION'
FAILOVER_TYPE_SELECT VARCHAR2(7) 'SELECT'
FAILOVER_TYPE_TRANSACTION VARCHAR2(12) 'TRANSACTION'
Data Types TYPE svc_parameter_array IS TABLE OF VARCHAR2(100)
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_APP_CONT_ADMIN DBMS_SERVICE_ERR SERVICE$
DBMS_DISRUPT DBMS_SERVICE_LIB SVC_PARAMETER_LIST_T
DBMS_GSM_CLOUDADMIN DBMS_SERVICE_PRVT SVC_PARAMETER_T
DBMS_GSM_DBADMIN DBMS_SYS_ERROR V$PARAMETER
DBMS_SERVICE_CONST PLITBLM V$SESSION
Documented Yes
Exceptions
Error Code Reason
ORA-01013 err_intr
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
ORA-44786 err_pdb_imp
ORA-44791 err_inv_stop
ORA-44793 err_inv_intl
First Available 10.1
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, GSMADMIN_INTERNAL roles and SYSRAC
Source {ORACLE_HOME}/rdbms/admin/dbmssrv.sql
Subprograms
 
CREATE_SERVICE
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_service, NONE);
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_service, NONE);
See Demos Below
 
DELETE_SERVICE
Deletes a service from the data dictionary dbms_service.delete_service(service_name IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(delete_service, NONE);
See Demos Below
 
DISCONNECT_SESSION (new 20c parameter)
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,
guid              IN IN VARCHAR2 DEFAULT NULL);
PRAGMA SUPPLEMENTAL_LOG_DATA(disconnect_session, NONE);
exec dbms_service.disconnect_session('MLIBSERV');
 
KILL_SESSION
Kills sessions that connect to the local instance with the specified service name dbms_service.kill_session(
service_name IN VARCHAR2,
kill_option  IN NUMBER DEFAULT IMMEDIATE);
PRAGMA SUPPLEMENTAL_LOG_DATA(kill_session, NONE);
TBD
 
MODIFY_SERVICE
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(modify_service, NONE);
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(modify_service, NONE);
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(start_service, NONE);
See Demos Below
 
STOP_SERVICE
Stop a service dbms_service.stop_service(
service_name  IN VARCHAR2,
instance_name IN VARCHAR2 DEFAULT NULL,
stop_option   IN VARCHAR2 DEFAULT NULL,
drain_timeout IN NUMBER   DEFAULT NULL,
replay        IN BOOLEAN  DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(stop_service, NONE);
See Demos Below
 
TAG_SESSION
Tag all sessions connected with a specific service with a GUID dbms_service.tag_session(
service_name IN VARCHAR2,
guid         IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(tag_session, READ_ONLY);
TBD
 
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
Built-in Functions
Built-in Packages
DBMS_MONITOR
DBMS_SCHEDULER
DBMS_SERVICE_CONST
DBMS_SERVICE_ERR
DBMS_SERVICE_PRVT
How Can I #12
Real Application Clusters RAC
What's New In 19c
What's New In 20c-21c

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