This article describes how to create database services in single instance databases using the DBMS_SERVICE package.
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:
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
------------------------------ ------------------------------
my_new_service my_new_service
SELECT name,network_name FROM v$active_services ORDER BY 1;
No rows selected
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
------------------------------ ------------------------------
my_new_service my_new_service
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
------------------------------ ------------------------------
my_new_service my_new_service
SELECT name,network_name FROM v$active_services ORDER BY 1;
No rows selected
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;
No rows selected
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;
/
