Oracle | DBMS

Oracle DBMS_SERVICE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 alter system TO uwclass;

      GRANT execute ON dbms_service 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	KILL_SESSION	STOP_SERVICE
DELETE_SERVICE	MODIFY_SERVICE	TAG_SESSION
DISCONNECT_SESSION	START_SERVICE	Demo

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;