How to Switch Service Name to Another PDB

In this case, we have two PDBs in a container and a general service name needs to be be switched between them back and forth. First of all, we connect to the container by SYS.

SQL> conn / as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ERP1                           READ WRITE NO
         4 ERP2                           READ WRITE NO

Remove Service from First PDB

Switch to the first PDB.


SQL> alter session set container=ERP1;

Session altered.

Stop service.

You have to stop the service before deleting it, otherwise, you'll see ORA-44305.

SQL> exec dbms_service.stop_service('ERPGEN', DBMS_SERVICE.ALL_INSTANCES);

PL/SQL procedure successfully completed.

Delete service.

SQL> exec dbms_service.delete_service('ERPGEN');

PL/SQL procedure successfully completed.

Restart First PDB

To avoid error ORA-44773, we should restart the original PDB.

Close and open the first PDB.

SQL> alter pluggable database ERP1 close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database ERP1 open instances=all;

Pluggable database altered.

Add Service to Second PDB

Switch to the second PDB.

SQL> alter session set container=ERP2;

Session altered.

Add service.

SQL> exec dbms_service.create_service('ERPGEN', 'ERPGEN');

PL/SQL procedure successfully completed.

Start service on all instances.

SQL> exec dbms_service.start_service('ERPGEN', DBMS_SERVICE.ALL_INSTANCES);

PL/SQL procedure successfully completed.