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