常用数据库开发工具与 oracle 可插拔数据数据库的连接
Oracle12C之后,oracle数据库的架构发生了很大的变化,多租户数据库(可插拔数据库、容器数据库)架构提高了系统资源的利用率,对数据库管理方式也发生了很大的变革,但初学者往往会遇到数据库的连接问题,下面就常用的数据库开发工具与orace可插拔数据库的连接进行探讨。
服务器环境
- 硬件 RAM:8G ,硬盘:100G,双核CPU
- 系统软件 centOS7-X64 ,oracle19C
- 环境变量 ORACLE_SID=ORCLCDB
一 sqlplus 命令行工具
- 进入根容器,然后再切换到可插拔数据库
[oracle@orc19c admin]$ sqlplus / as sysdba
#显示当前容器的名字
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
#是所有可插完数据库
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
#切换当前工作数据库
SQL> ALTER SESSION SET CONTAINER=ORCLPDB1;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB1
- 直接连接可插拔数据库
[oracle@orc19c ~]$ sqlplus sys/password@ORCLPDB1 as sysdba
#显示当前容器的名字
SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB1
二 SQL_Developer
-
连接根容器数据库
-
连接可插拔数据库
三 PLSQL Developer
- 首先在reference中对Oracle Home和OCI Library进行配置
Oracle Home
E:\Dev-Tools\PLSQL_Developer_15\instantclient_21_9
OCI library
E:\Dev-Tools\PLSQL_Developer_15\instantclient_21_9
- 在oracle home目录下创建子文件夹network和admin
E:\Dev-Tools\PLSQL_Developer_15\instantclient_21_9\network\admin
#编写TNS 配置文件tnsnames.ora
ORCLCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.100.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLCDB)
)
)
ORCLPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.31.100.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb1)
)
)
-
创建根容器数据库连接
-
创建可插拔数据库连接
四 datagrip
-
创建根容器数据库连接,注意连接类型的选择。
-
创建可插拔数据库连接
-
服务名称可以从如下途径获取
[oracle@orc19c admin]$ lsnrctl status
...
Services Summary...
Service "ORCLCDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "f82f890e5c9d2a8fe0536b641f0affe1" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
五 Navicat
-
创建根容器数据库连接
-
创建可插拔数据库连接