Oracle | DBMS_SERVICE

Oracle DBMS_SERVICE_PRVT Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose This package allows an application to manage services and sessions connected with a specific service name. The difference to the previous package is that additional parameters like the global flag can be set.

Oracle Real Application Cluster (RAC) has a functionality to manage service names across instances. This package allows the creation, deletion, starting and stopping of services in both RAC and single instance. Additionally it provides the ability to disconnect all sessions which connect to the instance with a service name when RAC removes that service name from the instance.

This package was specifically defined with GSM in mind. Prospective users are advised to talk to the file owner before using this package.

AUTHID DEFINER

Data Types TYPE svc_parameter_array IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100); -- TYPEs below here are independent objects not defined within the package TYPE services_dt_array_type FORCE IS VARRAY(64000) OF (INTEGER) NOT PERSISTABLE; TYPE services_err_array_type FORCE IS VARRAY(64000) OF (VARCHAR2(32767)) NOT PERSISTABLE; TYPE services_name_array_type FORCE IS VARRAY(64000) OF (VARCHAR2(32767)) NOT PERSISTABLE; TYPE services_replay_array_type FORCE IS VARRAY(64000) OF (INTEGER) NOT PERSISTABLE; TYPE services_stopt_array_type FORCE IS VARRAY(64000) OF INTEGER) NOT PERSISTABLE; TYPE svc_parameter_t FORCE IS OBJECT ( param_name VARCHAR2(100), param_value VARCHAR2(100)); TYPE svc_parameter_list_t IS VARRAY(100) OF svc_parameter_t;

Dependencies

DBA_SERVICES	DBMS_SERVICE_PRVT_LIB	SERVICES_NAME_ARRAY_TYPE
DBMS_GSM_CLOUDADMIN	GV$ACTIVE_SERVICES	SERVICES_REPLAY_ARRAY_TYPE
DBMS_GSM_DBADMIN	GV$INSTANCE	SERVICES_STOPT_ARRAY_TYPE
DBMS_GSM_UTILITY	PLITBLM	SVC_PARAMETER_LIST_T
DBMS_OUTPUT	SERVICE$	SVC_PARAMETER_T
DBMS_SERVICE	SERVICES_DT_ARRAY_TYPE	V$PDBS
DBMS_SERVICE_CONST	SERVICES_ERR_ARRAY_TYPE	X$KSUSE
DBMS_SERVICE_ERR

Documented No

Exceptions

Error Code	Reason
ORA-44304	Service <service_name> does not exist

First Available 12.1

Security Model Owned by SYS with with EXECUTE granted to SYSRAC and the DBA and GSMADMIN_INTERNAL roles.

Source {ORACLE_HOME}/rdbms/admin/dbmssrv.sql

Subprograms

BEGIN_LIVE_MIGRATION	GET_STATUS_LIVE_MIGRATION	RENAME_PDB_ATTRIBUTE
CREATE_SERVICE	GET_TOPOLOGY	START_SERVICE
DELETE_SERVICE	IS_JAVA_SERVICE	START_SERVICES_BATCH
END_LIVE_MIGRATION	MIGRATE_TO_12_2	STOP_SERVICE
GET_HASH	MODIFY_SERVICE	STOP_SERVICES_BATCH

BEGIN_LIVE_MIGRATION (new 23ai)

Undocumented dbms_service_prvt.begin_live_migration(timeout_in_ms IN NUMBER);

exec dbms_service_prvt.begin_live_migration(10);

      

      PL/SQL procedure successfully completed.

CREATE_SERVICE

Creates a new service$ entry for this service name

dbms_service_prvt.create_service(

      service_name       IN VARCHAR2,

      network_name       IN VARCHAR2,

      cluster_attributes IN svc_parameter_array,

      db_attributes      IN svc_parameter_array,

      is_called_by_crs   IN BOOLEAN DEFAULT FALSE,

      srvc_context       IN NUMBER  DEFAULT 1);

Cluster Attributes
available	A comma separated list of available databases
locality	Service region locality. Must be ANYWHERE or LOCAL_ONLY
region_failover	Service is enabled for region failover
role	Database role the database must be in to start this service
preferred	A comma separated list of preferred databases
preferred_all	All databases in the pool are preferred

Data Guard Broker Atributes
failover_primary	Enable service to failover to primary. This is only applicable to services with the role PHYSICAL_STANDBY.
lag	Specifes the lag of the service
policy	Management policy for the service. Can be automatic or manual.
tafpolicy	TAF client policy

Database Attributes
aq_ha_notifications	Determines whether HA events are sent via AQ for this service.
commit_outcome	Persist outcome of transactions
dtp	Declares the service to be for DTP or distributed transactions.
edition	The initial session edition
failover_delay	The TAF failover delay for the service
failover_method	The TAF failover method for the service
failover_retries	The TAF failover retries for the service
failover_type	The TAF failover type for the service
global	Global service
goal	The workload management goal directive of the service. Valid values -- are : DBMS_SERVICE.GOAL_SERVICE_TIME, -- DBMS_SERVICE.GOAL_THROUGHPUT, -- DBMS_SERVICE.GOAL_NONE.
is_called_by_crs	Is this function invoked by CRS? (MUST ONLY BE SET BY CRS)
pdb	The initial pdb
retention_timeout	Timeout when the transaction outcome is retained
replay_initiation_timeout	Timeout when replayed is disabled
session_state_consistency	Consistency of session state: static or dynamic
sql_translation_name	Name of SQL translation unit
srvc_context	Which service context does this apply to (DB and/or OCR)?

col network_name format a20

      col edition format a30

      

      SELECT name, network_name, pdb, edition

      FROM dba_services;

      

      DECLARE

       sn VARCHAR2(30) := 'MLIBSERV';

       nn VARCHAR2(30) := 'MLIBSERV';

       ca dbms_service_prvt.svc_parameter_array;

       da dbms_service_prvt.svc_parameter_array;

      BEGIN

        dbms_service_prvt.create_service(sn, nn, ca, da, NULL, 1);

      END;

      /

DELETE_SERVICE

Marks a service$ entry as deleted dbms_service_prvt.delete_service( service_name IN VARCHAR2, is_called_by_crs IN BOOLEAN DEFAULT FALSE, srvc_context IN NUMBER DEFAULT 1);

SELECT name, network_name, pdb, edition

      FROM dba_services;

      

      exec dbms_service_prvt.delete_service('MLIBSERV');

      

      SELECT name, network_name, pdb, edition

      FROM dba_services;

END_LIVE_MIGRATION (new 23ai)

Undocumented dbms_service_prvt.end_live_migration;

exec dbms_service_prvt.end_live_migration;

      

      PL/SQL procedure successfully completed.

GET_HASH

Computes the hash value for a service name dbms_service_prvt.get_topology(service_name IN VARCHAR2) RETURN NUMBER;

SELECT name

      FROM v$services

      ORDER BY 1;

      

      SELECT dbms_service_prvt.get_hash('SYS$BACKGROUND')

      FROM dual;

      

      DBMS_SERVICE_PRVT.GET_HASH('SYS$BACKGROUND')

      --------------------------------------------

                                         165959219

GET_STATUS_LIVE_MIGRATION (new 23ai)

Undocumented dbms_service_prvt.get_status_live_migration RETURN VARCHAR2;

SELECT dbms_service_prvt.get_status_live_migration;

      

      GET_STATUS_LIVE_MIGRATION

      -------------------------

      NONE

      

      1 row selected.

      

      ret_str:NONErc:0

GET_TOPOLOGY

The topology function returns the instances the specified service is currently active dbms_service_prvt.get_topology(service_name IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_service_prvt.get_topology('SYS$BACKGROUND');

      

      DBMS_SERVICE_PRVT.GET_TOPOLOGY('SYS$BACKGROUND')

      -------------------------------------------------

      ASRA23ai

IS_JAVA_SERVICE

Returns TRUE if any of the session connected with this service have ever used java in the database dbms_service_prvt.is_java_service(service_name IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF dbms_service_prvt.is_java_service('SYS$BACKGROUND') THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

      FALSE

      

      PL/SQL procedure successfully completed.

MIGRATE_TO_12_2

Updates the service data dictionary tables to 12.2

Why you would wan to to migrate to 12cR2, if you are in 23ai, is something you should think about before running this proc. Then don't do it. dbms_service_prvt.migrate_to_12_2;

exec dbms_service_prvt.migrate_to_12_2;

MODIFY_SERVICE

Modifies an existing service

dbms_service.modify_service(

      service_name       IN VARCHAR2,

      cluster_attributes IN svc_parameter_array,

      db_attributes      IN svc_parameter_array,

      is_called_by_crs   IN BOOLEAN DEFAULT FALSE,

      srvc_context       IN NUMBER  DEFAULT 1);

CLUSTER_ATTRIBUTE Values		Service Names
available		aq_ha_notifications
failover_primary		commit_outcome
lag		drain_timeout (in 23ai doc but not in script)
locality		dtp
policy		edition
preferred		failover_delay
preferred_all		failover_method
region_failover		failover_restore (in 23ai doc but not in script)
role		failover_retries
tafpolicy		failover_type
		goal
		is_called_by_crs
		placement_policy (in 23ai doc but not in script)
		pdb (in script but not in the 23ai doc)
		replay_initiation_timeout
		retention_timeout
		session_state_consistency
		sql_translation_name
		svc_context
		stop_option (in 23ai doc but not in script)
		true_cache_service (in 23ai doc but not in script)

DECLARE

       sn VARCHAR2(30) := 'MLIBSERV';

       ca dbms_service_prvt.svc_parameter_array;

       da dbms_service_prvt.svc_parameter_array;

      BEGIN

        da('EDITION') := 'ORA$BASE';

        dbms_service_prvt.modify_service(sn,  ca, da, FALSE, 1);

      END;

      /

      NAME                                                  NETWORK_NAME   PDB      EDITION

      ----------------------------------------------------- -------------- -------- ----------

      SYS$BACKGROUND                                                       CDB$ROOT

      SYS$USER                                                             CDB$ROOT

      ASRA23aiXDB                                           
      <same as name> CDB$ROOT

      ASRA23ai_qrd.iad.sub06161709240.asravcn.oraclevcn.com <same as name> CDB$ROOT

      

      -- package output is the same as from this following query

      col name format a55

      col network_name format a55

      

      SELECT name, network_name, pdb, edition

      FROM dba_services;

RENAME_PDB_ATTRIBUTE

Changes the pdb_name of all qualifying services to new_pdb_name dbms_service_prvt.rename_pdb_attribute( pdb_name IN VARCHAR2, new_pdb_name IN VARCHAR2);

This has the potential to be dangerous so we are not creating a demo and suggest you not do so unless you have a throw-away database.

START_SERVICE

In single instance starts the service with this service_name. In RAC will optionally start the service only on the instance specified. dbms_service_prvt.start_service( service_name IN VARCHAR2, all_nodes IN BOOLEAN DEFAULT FALSE, is_called_by_crs IN BOOLEAN DEFAULT FALSE);

exec dbms_service_prvt.start_service('MLIBSERV');

      

      PL/SQL procedure successfully completed.

START_SERVICES_BATCH (new 23ai)

Undocumented dbms_service_prvt.start_services_batch( services IN sys.services_name_array_type, services_err OUT sys.services_err_array_type);

TBD

STOP_SERVICE

In single instance it stops the service specified by service_name. In RAC will call out to CRS to stop the service, optionally on the instance specified. Calls clscrs_stop_resource. dbms_service_prvt.stop_service( service_name IN VARCHAR2, all_nodes IN BOOLEAN DEFAULT FALSE, is_called_by_crs IN BOOLEAN DEFAULT FALSE, stop_option IN VARCHAR2 DEFAULT NULL, drain_timeout IN NUMBER DEFAULT NULL, replay IN BOOLEAN DEFAULT TRUE);

exec dbms_service_prvt.stop_service('MLIBSERV');

      

      PL/SQL procedure successfully completed.

STOP_SERVICES_BATCH (new 23ai)

Undocumented

Overload 1 dbms_service_prvt.stop_services_batch( services IN sys.services_name_array_type, services_err OUT sys.services_err_array_type);

TBD

Overload 2 dbms_service_prvt.stop_services_batch( services IN sys.services_name_array_type, services_err OUT sys.services_err_array_type, crs_call IN BOOLEAN, stop_opt_arr IN sys.services_name_array_type, dt_arr IN sys.services_dt_array_type, replay_arr IN sys.services_replay_array_type);

TBD