Oracle DBMS_SERVICE_PRVT
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
DBMS_GSM_DBADMIN DBMS_SERVICE_PRVT_LIB SVC_PARAMETER_LIST_T
DBMS_SERVICE PLITBLM SVC_PARAMETER_T
DBMS_SERVICE_CONST SERVICE$ V$PDBS
DBMS_SERVICE_ERR    
Documented No
First Available 12.1.0
Security Model Owned by SYS with with EXECUTE granted to the DBA and GSMADMIN_INTERNAL roles
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;
 
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);
exec dbms_service_prvt.stop_service('MLIBSERV');

Related Topics
DBMS_SERVICE
DBMS_SERVICE_CONST
DBMS_SERVICE_ERR
Packages

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