How to Create and Stop Start a Database Service Using DBMS_SERVICE in Oracle Database

Mehmet Salih Deveci

5 years ago

Hi,

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.

create_service
start_service
stop_service
delete_service
disconnect_service
modify_service
activate_service

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;

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/