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

Home » Articles » 10g » Here

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.

Related articles.

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.

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.