Oracle | DBMS

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 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;