| General Information |
| Purpose |
Allows an application to manage services and sessions connected via a 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 (load balance CPU) |
NUMBER |
1 |
| CLB_GOAL_LONG (load balance connections) |
NUMBER |
2 |
| Disconnect Session |
| POST_TRANSACTION |
NUMBER |
0 |
| IMMEDIATE |
NUMBER |
1 |
| Other Attributes |
| ALL_INSTANCES |
VARCHAR2(2) |
'*' |
| 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$ |
DBMS_SYS_ERROR |
| ALL_SERVICES |
GV$ACTIVE_SERVICES |
| DBA_SERVICES |
V$PARAMETER |
| DBMS_SERVICE_LIB |
V$SESSION |
|
| Exceptions |
| Error Code |
Name |
Description |
| ORA-44301 |
null_service_name |
The service name argument was found to be NULL |
| ORA-44312 |
null_network_name |
The network name argument was found to be NULL |
| ORA-44313 |
service_exists |
This service name was already in existence |
| ORA-44314 |
service_does_not_exist |
The specified service was not in existence |
| ORA-44315 |
service_in_use |
The specified service was running |
| ORA-44316 |
service_name_too_long |
The service name was too long |
| ORA-44317 |
network_prefix_too_long |
The network name, excluding the domain, was too long |
| ORA-44318 |
not_initialized |
The services layer was not yet initialized |
| ORA-44319 |
general_failure |
There was an unknown failure |
| ORA-44310 |
max_services_exceeded |
The maximum number of services has been reached |
| ORA-44311 |
service_not_running |
The specified service was not running |
| ORA-44312 |
database_closed |
The database was closed |
| ORA-44313 |
invalid_instance |
The instance name argument was not valid |
| ORA-44314 |
network_exists |
The network name was already in existence |
| ORA-44315 |
null_attributes |
All attributes specified were NULL |
| ORA-44316 |
invalid_argument |
Invalid argument supplied |
| ORA-44317 |
database_readonly |
The database is open read-only |
| ORA-44318 |
max_sn_length |
The total length of all running service network names exceeded the maximum allowable length |
| ORA-44319 |
err_aq_service |
AQ service exception |
|
| 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 |
| Subprograms |
|
| |
| 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,
edition IN VARCHAR2 DEFAULT NULL); |
| See demo below |
| |
| DELETE_SERVICE |
| Deletes a service from the data dictionary |
dbms_service.delete_service(service_name IN VARCHAR2); |
| See demo below |
| |
| DISCONNECT_SESSION |
| 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,
edition IN VARCHAR2 DEFAULT NULL,
modify_edition IN BOOLEAN DEFAULT FALSE); |
exec dbms_service.modify_service(
service_name => 'MLIB_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,
edition => 'DEMO_ED'
modify_edition => TRUE); |
| |
| START_SERVICE |
| Activate a service |
dbms_service.start_service(
service_name IN VARCHAR2,
instance_name IN VARCHAR2 DEFAULT NULL); |
| See demo below |
| |
| STOP_SERVICE |
| Stop a service |
dbms_service.stop_service(
service_name IN VARCHAR2,
instance_name IN VARCHAR2 DEFAULT NULL); |
| See demo below |
| |
| DBMS_SERVICE Demo |
set linesize 121
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', 'orabase');
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; |
