Oracle DBMS_SERVICE_PRVT
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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(30) INDEX BY VARCHAR2(100);
Dependencies
DBA_SERVICES DBMS_SERVICE_PRVT_LIB SVC_PARAMETER_LIST_T
DBMS_GSM_DBADMIN GV$ACTIVE_SERVICES SVC_PARAMETER_T
DBMS_SERVICE GV$INSTANCE V$PDBS
DBMS_SERVICE_CONST PLITBLM X$KSUSE
DBMS_SERVICE_ERR SERVICE$  
Documented No
First Available 12.1.0
Security Model Owned by SYS with with EXECUTE granted to the DBA and GSMADMIN_INTERNAL roles and SYSRAC.
Source {ORACLE_HOME}/rdbms/admin/dbmssrv.sql
Subprograms
 
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;
 
GET_HASH (new 18c)
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_TOPOLOGY
The topology function returns on which 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')
FROM dual;

DBMS_SERVICE_PRVT.GET_TOPOLOGY('SYS$BACKGROUND')
-------------------------------------------------
orabase18
 
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;
/
 
MIGRATE_TO_12_2 (new 18c)
Updates the service data dictionary tables to 12.2 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   DB_ATTRIBUTES Values
available   aq_ha_notifications
failover_primary   commit_outcome
lag   dtp
locality   edition
policy   failover_delay
preferred   failover_method
preferred_all   failover_retries
region_failover   failover_type
role   global
tafpolicy   is_called_by_crs
    pdb
    replay_initiation_timeout
    retention_timeout
    session_state_consistency
    sql_translation_name
    srvc_context
col network_name format a20
col edition format a30

SELECT name, network_name, pdb, edition
FROM dba_services;

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

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 look of being potentially rather dangerous so I am electing to not create a demo and suggest you not do so either 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');
 
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,
drain_timeout    IN NUMBER,
replay           IN BOOLEAN);
exec dbms_service_prvt.stop_service('MLIBSERV');

Related Topics
Built-in Functions
Built-in Packages
DBMS_SERVICE
DBMS_SERVICE_CONST
DBMS_SERVICE_ERR
Real Application Clusters
What's New In 12cR2
What's New In 18cR3

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