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