Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Connect to Root Container
The connection to the root container is the same as connecting to the database instance in previous versions. Connection can be established using OS authentication on the database server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [oracle@oradb ~]$ sql / as sysdba SQLcl: Release 12.2.0.1.0 RC on Sat Dec 15 15:11:41 2018 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> set sqlformat ansiconsole SQL> show con_name CON_NAME ------------------------------ CDB$ROOT |
Find Service Names
We can find service names from the V$SERVICES view.
1 2 3 4 5 6 7 | SQL> select NAME,PDB,CON_ID from v$services; NAME PDB CON_ID ORCL.svcsubnetad3.svcvcn.oraclevcn.com CDB$ROOT 1 ORCL.svcsubnetad3.svcvcn.oraclevcn.comXDB CDB$ROOT 1 SYS$BACKGROUND CDB$ROOT 1 SYS$USERS CDB$ROOT 1 pdb1.svcsubnetad3.svcvcn.oraclevcn.com PDB1 3 |
It is also possible to find service names with the lsnrctl tool.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | [oracle@oradb ~]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-DEC-2018 15:14:24 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORADB.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 12-DEC-2018 19:17:31 Uptime 2 days 19 hr. 56 min. 53 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ORADB/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oradb.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ORCL/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "7cd91ac74a8a1f40e0530320000a2fa7.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.comXDB" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Service "pdb1.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@oradb ~]$ lsnrctl service LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-DEC-2018 15:14:44 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORADB.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=1521))) Services Summary... Service "7cd91ac74a8a1f40e0530320000a2fa7.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:2 refused:0 state:ready LOCAL SERVER Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:2 refused:0 state:ready LOCAL SERVER Service "ORCL.svcsubnetad3.svcvcn.oraclevcn.comXDB" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: oradb, pid: 12265> (ADDRESS=(PROTOCOL=tcp)(HOST=oradb.svcsubnetad3.svcvcn.oraclevcn.com)(PORT=23026)) Service "pdb1.svcsubnetad3.svcvcn.oraclevcn.com" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:2 refused:0 state:ready LOCAL SERVER The command completed successfully |
Connect To Pluggable Database with Easy Connect Method
After specifying the service names, we can connect with the easy connect method as follows.
oradb.svcsubnetad3.svcvcn.oraclevcn.com -> hostname
pdb1.svcsubnetad3.svcvcn.oraclevcn.com -> pdb1 pluggable database service name
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [oracle@oradb ~]$ sqlplus ahmet/ahmet@oradb.svcsubnetad3.svcvcn.oraclevcn.com:1521/pdb1.svcsubnetad3.svcvcn.oraclevcn.com SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 15 15:17:55 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Dec 15 2018 10:40:22 +00:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show con_name CON_NAME ------------------------------ PDB1 |
Connect To Pluggable Database with Naming Method
We can connect with Naming method as follows. First we need to make sure that the TNS name exists in $ORACLE_HOME/network/admin/tnsnames.ora and that it is correct.
TNS information in the tnsnames.ora file:
1 2 3 4 5 6 7 8 | PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORADB.svcsubnetad3.svcvcn.oraclevcn.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.svcsubnetad3.svcvcn.oraclevcn.com) ) ) |
Naming Method
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [oracle@oradb admin]$ sqlplus ahmet/ahmet@PDB1 SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 15 15:21:00 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Dec 15 2018 15:17:55 +00:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show con_name CON_NAME ------------------------------ PDB1 |
Connect To Pluggable Database From Root Container
After connecting to the root container (CDB$ROOT), it is possible to switch to the corresponding pluggable databases via the alter session command or to the root container from pluggable databases.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | [oracle@oradb admin]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 15 15:22:43 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> SQL> SQL> select name from v$pdbs; NAME -------------------------------------------------------------------------------- PDB$SEED PDB1 SQL> SQL> SQL> alter session set container=PDB1; Session altered. SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 READ WRITE NO SQL> SQL> SQL> alter session set container=CDB$ROOT; Session altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select sys_context('USERENV','CON_NAME') from dual; SYS_CONTEXT('USERENV','CON_NAME') -------------------------------------------------------------------------------- CDB$ROOT |
