DBMS_SERVICE : Define Database Services in a Single Instance Database

 

The DBMS_SERVICE package in Oracle allows you to create, delete, start, and stop database services in a single instance database. It can also disconnect all sessions that connect to an instance with a service name.

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

To display all services

SELECT name,network_name FROM dba_services ORDER BY 1;

To display active services

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

To start service

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

To stop service

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

To modify service

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

To delete service

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

Disconnect Sessions

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