CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SERVICE
Version 11.1
 
General Information
Purpose Allows an application to manage services and sessions connected with a specific service name.
Source {ORACLE_HOME}/rdbms/admin/dbmssrv.sql
First Available 10.1

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 NUMBER 1
CLB_GOAL_LONG NUMBER 2
Disconnect Session
POST_TRANSACTION NUMBER 0
IMMEDIATE NUMBER 1
TAF Failover Attributes
FAILOVER_METHOD_NONE VARCHAR2(5) 'NONE'
FAILOVER_METHOD_BASIC  VARCHAR2(6) 'BASIC'
FAILOVER_TYPE_NONE

VARCHAR2(5)

'NONE'
FAILOVER_TYPE_SESSION

VARCHAR2(8)

'SESSION'
FAILOVER_TYPE_SELECT

VARCHAR2(7)

'SELECT'
FAILOVER_RETRIES

NUMBER

 
FAILOVER_DELAY

NUMBER

 

Dependencies
service$  
all_services gv$active_services
dba_services v_$active_services
dbms_service_lib v_$parameter
dbms_sys_error v_$session

Exceptions
Error Code Name Description
-44301 null_service_name The service name argument was found to be NULL
-44312 null_network_name The network name argument was found to be NULL
-44313 service_exists This service name was already in existence
-44314 service_does_not_exist The specified service was not in existence
-44315 service_in_use The specified service was running
-44316 service_name_too_long The service name was too long
-44317 network_prefix_too_long The network name, excluding the domain, was too long
-44318 not_initialized The services layer was not yet initialized
-44319 general_failure There was an unknown failure
-44310 max_services_exceeded The maximum number of services has been reached
-44311 service_not_running The specified service was not running
-44312 database_closed The database was closed
-44313 invalid_instance The instance name argument was not valid
-44314 network_exists The network name was already in existence
-44315 null_attributes All attributes specified were NULL
-44316 invalid_argument Invalid argument supplied
-44317 database_readonly The database is open read-only
-44318 max_sn_length The total length of all running service network names exceeded the maximum allowable length
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 execute ON dbms_service TO uwclass;
GRANT alter system TO uwclass;
GRANT select ON v_$session TO uwclass;

Services 101
conn / 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;
Security Model Execute is granted to the DBA role
 
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
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);
See demo
 
DELETE_SERVICE
Deletes a service from the data dictionary dbms_service.delete_service(service_name IN VARCHAR2);
See demo
 
DISCONNECT_SESSION  (new disconnect_option parameter in 11g)
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);
exec dbms_service.disconnect_session('UW');
 
MODIFY_SERVICE

Modify an existing service

Used for managing RAC and DataGuard service failovers
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);
exec dbms_service.modify_service(
service_name => 'PSOUG_SOA', 
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);
 
START_SERVICE
Activate a service dbms_service.start_service(
service_name  IN VARCHAR2, 
instance_name IN VARCHAR2 DEFAULT NULL);
See demo
 
STOP_SERVICE
Stop a service dbms_service.stop_service(
service_name  IN VARCHAR2, 
instance_name IN VARCHAR2 DEFAULT NULL);
See demo
 
DBMS_SERVICE Demo
set linesize 140
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('UW', 'u.washington.edu');

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('UW', 'orabase');

-- SELECT service_id, name, network_name
-- FROM gv$active_services;

exec dbms_service.stop_service('UW');

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('UW');

SELECT service_id, name,network_name, creation_date
FROM dba_services;
 
Related Topics
DBMS_MONITOR
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [85 users online]    © 2010 psoug.org