Oracle DBMS_SERVICE_PRVT
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 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);
Dependencies
DBA_SERVICES DBMS_SERVICE_ERR SERVICE$
DBMS_GSM_CLOUDADMIN 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
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 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);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_service, NONE);


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);
PRAGMA SUPPLEMENTAL_LOG_DATA(delete_service, NONE);
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
Computes the hash value for a service name dbms_service_prvt.get_topology(service_name IN VARCHAR2) RETURN NUMBER;
PRAGMA SUPPLEMENTAL_LOG_DATA(get_hash, READ_ONLY);
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 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')
FROM dual;

DBMS_SERVICE_PRVT.GET_TOPOLOGY('SYS$BACKGROUND')
-------------------------------------------------
test21db
 
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;
PRAGMA SUPPLEMENTAL_LOG_DATA(is_java_service, READ_ONLY);
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 dbms_service_prvt.migrate_to_12_2;
PRAGMA SUPPLEMENTAL_LOG_DATA(migrate_to_12_2, NONE);
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(modify_service, NONE);


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

col name format a55
col network_name format a55

SELECT name, network_name
FROM dba_services;

NAME                                                    NETWORK_NAME
------------------------------------------------------- -----------------------------------------------------
SYS$BACKGROUND
SYS$USERS
test21dbXDB                                             test21dbXDB
test21db_iad25g.sub07212035450.testvcn01.oraclevcn.com test21db_iad25g.sub07212035450.testvcn01.oraclevcn.com
 
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);
PRAGMA SUPPLEMENTAL_LOG_DATA(rename_pdb_attribute, NONE);
This has the potential to be 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);
PRAGMA SUPPLEMENTAL_LOG_DATA(start_service, NONE);
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 DEFAULT NULL,
drain_timeout    IN NUMBER   DEFAULT NULL,
replay           IN BOOLEAN  DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(stop_service, NONE);
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 21c
What's New In 23c

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