Managing Service Names with DBMS_SERVICE

Services or Service names in Oracle specify one or more names that clients can use to connect to an Oracle Database. It is a logical representation of a database. When an instance starts, it registers itself with the listener using one or more service names. They are a good way of separating various application connections to an Oracle database, creating a separate service for every application.

To manage your services, Oracle recommends that you use the SRVCTL or GDSCTL command line utilities, or the DBMS_SERVICE package. Using services is much better than using Oracle SIDs for client connections. It is particularly important for clustered environments. Prior to Oracle database 19c, the service name parameter was available to define database services. From 19c, the DBMS_SERVICE package can be used to the same effect.

USING DBMS_SERVICE

We create a new service using the CREATE_SERVICE procedure. There are two overloads that allow you to amend various features of the service. One overload accepts a 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.

TO CREATE A SERVICE;

BEGIN
  dbms_service.create_service(
   service_name => 'MY_NEW_SERVICE'
  ,network_name => 'MY_NEW_SERVICE'
);
END;
/
PL/SQL procedure successfully completed.

VERIFY SERVICE EXISTS

COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT 
       name, 
       network_name 
FROM 
      dba_services
ORDER BY 1;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
MY_NEW_SERVICE                 MY_NEW_SERVICE
SYS$BACKGROUND
SYS$USERS

SYS$BACKGROUND and SYS$USERS are default internal services that exist in every database.

TO START THE SERVICE

BEGIN
 dbms_service.start_service (
  service_name => 'my_new_service'
 );
END;
/
PL/SQL procedure successfully completed.

-- verify the service is active
SELECT 
      name, 
      network_name 
FROM 
  v$active_services;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
MY_NEW_SERVICE                 MY_NEW_SERVICE
SYS$BACKGROUND
SYS$USERS

Add service to the tnsnames file

vi /etc/tnsnames.ora

add an entry for your new service in the tnsnames.ora file as below

my_new_service =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = IPC_LISTENER_KEY))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = my_new_service )
    )
  )

Test Connection to the service

We can attempt a connection to an active service to verify it works.

$ sqlplus test_user/test_password@my_new_service

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

SQL>

CREATE RESTART TRIGGER

By default, user created services become inactive on database restarts. A trigger can be used to ensure the service persists beyond database restarts.

CREATE OR REPLACE TRIGGER AUTO_START_SERVICE
    AFTER STARTUP ON DATABASE
DECLARE
  v_service_count INTEGER;
  v_service_name dba_services.name%TYPE;
BEGIN
  v_service_name := '<service_name>' ;
--
  SELECT count(*)
  INTO v_service_count
  FROM dba_services
  WHERE name = v_service_name
   AND NOT EXISTS
    (SELECT 'X'
     FROM v$active_services
     WHERE name = v_service_name
    ) ;
--
  IF (v_service_count = 1) THEN
    dbms_service.start_service(v_service_name);
  END IF;
--
END;

TO STOP A SERVICE

The steps below can be used to stop the service from running.

BEGIN
  dbms_service.stop_service (
    service_name => 'MY_NEW_SERVICE'
 );
END;
/
PL/SQL procedure successfully completed.

column name for a30
column network_name for a30

-- verify it still exists
SELECT 
          name, 
          network_name 
FROM 
        dba_services;

NAME                           NETWORK_NAME
------------------------------ ------------------------------
MY_NEW_SERVICE                 MY_NEW_SERVICE
SYS$BACKGROUND
SYS$USERS

--verify it's not active
SELECT 
       name, 
       network_name 
FROM 
      v$active_services;
NAME                           NETWORK_NAME
------------------------------ ------------------------------
SYS$BACKGROUND
SYS$USERS

TO DELETE A SERVICE

If you no longer need the service, it can be deleted using the commands below

BEGIN
  dbms_service.delete_service (
    service_name => 'my_new_service'
  );
END;
/
PL/SQL procedure successfully completed.

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
• IMMEDIATE: Sessions disconnect immediately.
• NOREPLAY: Sessions disconnect immediately, and are flagged not to be replayed by application continuity.

Here is an example of its usage;

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

CONCLUSION

This post talked about services in an Oracle database and showed how the dbms_service package can be used to define, activate, deactivate, and drop database services or connections to the service. Services are a neat tool for a DBA and can be employed to help isolate connections from specific applications. This can be very useful when diagnosing specific application sessions on databases where multiple applications connect to the same database.

Thanks for reading. Kindly leave a comment.

Cheers, Harris 🎊

Reference

oracle docs