Oracle Multitenant -How to Create,Stop,Start,Delete,modify a Database Service Using DBMS_SERVICE in Oracle Database

Oracle Multitenant -

 How to Create,Stop,Start,Delete,modify a Database Service Using DBMS_SERVICE in Oracle Database

Oracle database has PL/SQL package called DBMS_SERVICE which is introduced in Oracle 10g, and has been extended with later releases.  DBMS_SERVICE is used to create,stop,start and define database services.

The dbms_service package has the following stored procedures.

• create_service
• start_service
• stop_service
• delete_service
• disconnect_service
• modify_service
• activate_service

Set session to PDB

alter session set container=PDB ;

Check available Service

 Display informations about existing services using dba_services view as follows.

COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,

       network_name

FROM   dba_services

ORDER BY 1;

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;

/

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;

/

Delete a Service

The DELETE_SERVICE procedure removes an existing service.

BEGIN

  DBMS_SERVICE.delete_service(

    service_name => 'my_new_service'

  );

END;

/

Disconnect a Service

The DISCONNECT_SERVICE procedure removes an existing service.

BEGIN

  DBMS_SERVICE.disconnect_service(

    service_name => 'my_new_service'

  );

END;

/

Save state the PDB. 

Save state the PDB. Other wise service needs to be manually started after PDB open each time.

 SQL> alter pluggable database save state;  

           Pluggable database altered.