骨骨学习笔记

from：http://www.psoug.org/reference/dbms_service.html

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 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 v_$parameter dba_rsrc_group_mappings v_$service_events dba_scheduler_job_classes v_$service_metrics dba_services v_$service_metrics_history dbms_service_lib v_$serv_mod_act_stats dba_thresholds v_$service_stats gv$active_services v_$service_wait_classes v_$active_services v_$services v_$active_session_history 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; 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 Disconnects sessions with the named service as the current instance. dbms_service.disconnect_session(service_name IN VARCHAR2); 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); See demo STOP_SERVICE Stop a service dbms_service.stop_service(
service_name IN VARCHAR2,
instance_name IN VARCHAR2); 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;