How to access oracle PDB remotely

oracleoracle-12cpython

I have create oracle 12c instance with 2 PDBs, I am able to connect to CDB from python using cx_Oracle module but not sure how to connect to PDB.

Best Answer

My environment (18c XE docker):

sqlplus "sys/oracle@localhost:1521/XE as sysdba"
sys@XE> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
         4 XEPDB2                         MOUNTED
sys@XE>

You will find the corresponding SERVICE_NAME for PDBS by:

sys@XE> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME                                     PDB                      CON_ID
---------- ---------------------------------------- -------------------- ----------
         1 SYS$BACKGROUND                           CDB$ROOT                      1
         4 xe                                       CDB$ROOT                      1
         3 xeXDB                                    CDB$ROOT                      1
         2 SYS$USERS                                CDB$ROOT                      1
         8 XEPDB1                                   XEPDB1                        3

5 rows selected.

You may define the SERVICE_NAME the way you want for your PDB. It is a good practice to name your database service. You can also configure your database to expose multiple SERVICE_NAMEs for the same database. For example at a TV station you have multiple channels. Their content is different so they go into separate schemas (CNN_NATIONAL, CNN_INTERNATIONAL).

sqlplus user/pass@something/CNN_NATIONAL
sqlplus user/pass@something/CNN_INTERNATIONAL

The point is you only need ONE Oracle Database to serve multiple applications separated by schemas. Now that we have containers we can have even better isolation/separation.

sys@XE> @g XEPDB1

sys:XEPDB1@XE> exec dbms_service.CREATE_SERVICE('cnn_int_prod','cnn-int-prod.cnn.com');
                                     
PL/SQL procedure successfully completed.                                   
                                     
Elapsed: 00:00:00.05             
sys:XEPDB1@XE> exec dbms_service.start_service('cnn_int_prod');                                                                                        

PL/SQL procedure successfully completed.                                 
                                                                           
Elapsed: 00:00:00.02                                                    

sys:XEPDB1@XE> select service_id,name,network_name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME                           NETWORK_NAME                   PDB                      CON_ID
---------- ------------------------------ ------------------------------ -------------------- ----------
         8 XEPDB1                         XEPDB1                         XEPDB1                        3
         1 SERVEMEWELL                    pdbprod1.us.oracle.com         XEPDB1                        3
         2 cnn_int_prod                   cnn-int-prod.cnn.com           XEPDB1                        3

3 rows selected.


sqlplus system/oracle@localhost:1521/cnn-int-prod.cnn.com

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 11 15:21:12 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Aug 11 2020 14:58:37 +02:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

system@XEPDB1>    

Best of luck!
Related Question