I will explain How to Create and Stop Start a Database Service Using DBMS_SERVICE in Oracle Database in this post.
Oracle database has PL/SQL package called DBMS_SERVICE which is introduced in Oracle 10g, and has been extended with later releases. DBMS_SERVICE is used to create,stop,start and define database services.
Database services are very important for workload measurement,management, prioritization, XA and distributed transaction management.
For example; You have 4 database services ( HR, SALES,ORDER and INVOICE ), you want to disconnect all sessions of HR without stopping database, then you can stop only HR Service instead of all database.
The DBMS_SERVICE package is used both Single Instance and RAC databases. You can manage service names across instances. For example; HR and SALES services are started on Node 1 and ORDER and INVOICE are started on Node 2.
The dbms_service package has the following stored procedures.
You can create a service as follows.
BEGIN DBMS_SERVICE.CREATE_SERVICE( service_name => 'SERVICE_NAME', network_name => 'SERVICE_NAME_NETWORK' ); END; /
You can start an existing service as follows.
BEGIN DBMS_SERVICE.START_SERVICE( service_name => 'SERVICE_NAME' ); END; /
You can stop an existing service as follows.
BEGIN DBMS_SERVICE.STOP_SERVICE( service_name => 'SERVICE_NAME' ); END; /
You can delete an existing service as follows.
BEGIN DBMS_SERVICE.DELETE_SERVICE( service_name => 'SERVICE_NAME' ); END; /
All corresponding sessions are disconnected using DISCONNECT_SERVICE procedure as follows.
BEGIN DBMS_SERVICE.DISCONNECT_SERVICE ( service_name => 'SERVICE_NAME' ); END; /
You can display informations about existing services using dba_services view as follows.
SELECT name,network_name FROM dba_services;
You can display informations about active services using v$active_services view as follows.
SELECT name,network_name FROM v$active_services;
You can display statistics about services using v$service_stats view as follows.
select * from gv$service_stats;