8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

DBMS_SERVICE : Define Database Services in a Single Instance Database

This article describes how to create database services in single instance databases using the DBMS_SERVICE package. This package was introduced in Oracle 10g, and has been extended with later releases.

In a multitenant database services can be created in a CDB or a PDB. In these examples we will just create them in the CDB, so it looks similar to a non-CDB instance.

Create a Service
Start a Service
Modify a Service
Stop a Service
Delete a Service
Disconnect Sessions

Create a Service

We create a new service using the CREATE_SERVICE procedure. There are two overloads allowing you to amend a number of features of the service. One overload accepts an parameter array, while the other allows you to set some parameters directly. The only mandatory parameters are the the SERVICE_NAME and the NETWORK_NAME, which represent the internal name of the service in the data dictionary and the name of the service presented by the listener respectively.

BEGIN
  DBMS_SERVICE.create_service(
    service_name => 'my_new_service',
    network_name => 'my_new_service'
  );
END;
/

We can display information about existing services using the {CDB|DBA|ALL}_SERVICES views. We can see if the service is started by checking the {G}V$ACTIVE_SERVICES view.

COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT name,
       network_name
FROM   dba_services
ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS
cdb1                           cdb1
cdb1XDB                        cdb1XDB
my_new_service                 my_new_service

SQL>


SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS
cdb1                           cdb1
cdb1XDB                        cdb1XDB
pdb1                           pdb1

SQL>

Start a Service

The START_SERVICE procedure starts an existing service, making it available for connections via the listener.

BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'my_new_service'
  );
END;
/

We can see the service is now active.

SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS
cdb1                           cdb1
cdb1XDB                        cdb1XDB
my_new_service                 my_new_service
pdb1                           pdb1

SQL>

Modify a Service

The MODIFY_SERVICE procedure allows us to alter parameters of an existing service. Like the CREATE_SERVICE procedure, there are two overloads allowing you to amend a number of features of the service. One overload accepts an parameter array, while the other allows you to set some parameters directly.

BEGIN
  DBMS_SERVICE.modify_service(
    service_name => 'my_new_service',
    goal         => DBMS_SERVICE.goal_throughput
  );
END;
/

Stop a Service

The STOP_SERVICE procedure stops an existing service, so it is no longer available for connections via the listener.

BEGIN
  DBMS_SERVICE.stop_service(
    service_name => 'my_new_service'
  );
END;
/

The service is still present, but it is no longer active.

COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT name,
       network_name
FROM   dba_services
ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS
cdb1                           cdb1
cdb1XDB                        cdb1XDB
my_new_service                 my_new_service

SQL>


SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS
cdb1                           cdb1
cdb1XDB                        cdb1XDB
pdb1                           pdb1

SQL>

Delete a Service

The DELETE_SERVICE procedure removes an existing service.

BEGIN
  DBMS_SERVICE.delete_service(
    service_name => 'my_new_service'
  );
END;
/

We can see it's not longer listed as an available service.

COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT name,
       network_name
FROM   dba_services
ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS
cdb1                           cdb1
cdb1XDB                        cdb1XDB

SQL>

Disconnect Sessions

The DISCONNECT_SESSION procedure disconnects all sessions currently connected to the service. The disconnection can take one of three forms, indicated by package constants.

POST_TRANSACTION : Sessions disconnect once their current transaction ends with a commit or rollback. This is the default value (0).
IMMEDIATE : Sessions disconnect immediately. Value (1).
NOREPLAY : Sessions disconnect immediately, and are flagged not to be replayed by application continuity. Value (2).

Here is an example of its usage.

BEGIN
  DBMS_SERVICE.disconnect_session(
   service_name      => 'my_new_service',
   disconnect_option => DBMS_SERVICE.immediate
  );
END;
/

For more information see:

Hope this helps. Regards Tim...

Back to the Top.