Skip to content


How to Use DBMS_SERVICE to Create a New Service?

The default service name of a database is the DB_UNIQUE_NAME, if we'd like to add some other entries to service different kinds of user, we have two options, one is to use DBMS_SERVICE to create a service in database, the other is to use srvctl add service in cluster.

To create a new service to a pluggable database (PDB) by DBMS_SERVICE, there's a little different.

Let's see how we add a service to the database by DBMS_SERVICE.

For single-instance, we can only use DBMS_SERVICE to add services to the database. Let's see the life cycle of a database service.


Create a service for the database

SQL> exec dbms_service.create_service('ERP', 'ERP');

PL/SQL procedure successfully completed.

The service is added to the database, no matter a single-instance or a RAC database.

There're more variations on adding a failover service to the cluster.


The new service won't start automatically after creation, you have to start it manually.

Start the service

SQL> exec dbms_service.start_service('ERP');

PL/SQL procedure successfully completed.


For RAC database, you should start the services on all instances in the cluster:

SQL> exec dbms_service.start_service('ERP', DBMS_SERVICE.ALL_INSTANCES);

PL/SQL procedure successfully completed.


Since the running status of service is not persistent across database restarts, so you need a trigger in order to start the service automatically.

create or replace trigger START_SERVICES after startup on database

Check services

Let's check online services of the database.

SQL> column name format a30;
SQL> column network_name format a30;
SQL> select name, network_name from v$active_services;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
ORCL                           ORCL
ERP                            ERP

For RAC databases, you may see the information like this:

SQL> select inst_id, name, network_name from gv$active_services;

   INST_ID NAME                           NETWORK_NAME
---------- ------------------------------ ------------------------------
         1 ORCL                           ORCL
         1 ERP                            ERP
         2 ORCL                           ORCL
         2 ERP                            ERP


If you want to remove service from the database, you have to stop it first.

SQL> exec dbms_service.stop_service('ERP');

PL/SQL procedure successfully completed.

For RAC database, you should stop the services on all instances in the cluster:

SQL> exec dbms_service.stop_service('ERP', DBMS_SERVICE.ALL_INSTANCES);

PL/SQL procedure successfully completed.


Then delete it.

SQL> exec dbms_service.delete_service('ERP');

PL/SQL procedure successfully completed.

If you don't stop it before deleting it, you shall see ORA-44305: service is running.

We have completed the life cycle of a database service by the above 4 steps.

In a multitenant environment, the service adding procedure for pluggable databases (PDB) is almost the same.

Leave a Reply

Your email address will not be published. Required fields are marked *