流漓 · 客

Appearance

CDB、PDB ​

创建容器 ​

bash
docker run -d \
--name oracle \
-p 1521:1521 \
-e ORACLE_SID=LLK \
-e ORACLE_PDB=JWZH \
-e ORACLE_PWD=Liulike#123 \
-v $(pwd)/data/:/data \
-v $(pwd)/database/:/opt/oracle/oradata \
oracle/database:12.2.0.1-ee

创建用户 ​

从Oracle 12C开始引入了CDB与PDB的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。CDB全称为Container Database，中文翻译为数据库容器，PDB全称为Pluggable Database，即可插拔数据库。在ORACLE 12C之前，实例与数据库是一对一或多对一关系（RAC）：即一个实例只能与一个数据库相关联，数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后，实例与数据库可以是一对多的关系。下面是官方文档关于CDB与PDB的关系图。

image-20250320165054370

通常在CDB上建立的用户是common user，新建用户名前要加C##。在PDB上创建的用户是local user。

在CDB上创建用户 ​

连接并创建用户 ​

bash
$ docker exec -it oracle bash
[oracle@f6a23ccb3202 data]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 20 09:01:30 2025
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,cdb from v$database;
NAME      CDB
--------- ---
JWZH      YES

SQL> create user C##test identified by testpass;
User created.

授权 ​

sql
# 授予C##test用户DBA、CONNECT、RESOURCE、CREATE VIEW的权限
SQL> grant dba,connect,resource,create view to C##test;
Grant succeeded.

# 允许C##test用户创建并启动一个数据库会话
SQL> grant create session to C##test;
Grant succeeded.

# 授予C##test用户查询数据库中任何表的权限
SQL> grant select any table to C##test;
Grant succeeded.

# 授予C##test用户更新数据库中任何表的权限
SQL> grant update any table to C##test;
Grant succeeded.

# 授予C##test用户向数据库中任何表插入数据的权限
SQL> grant insert any table to C##test;
Grant succeeded.

# 授予C##test用户从数据库中任何表删除数据的权限
SQL> grant delete any table to C##test;
Grant succeeded.

删除用户 ​

sql
SQL> drop user C##test cascade;
User dropped.

在PDB上创建用户 ​

查看PDB name并进入 ​

sql
SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

  PDB_ID   PDB_NAME   DBID         STATUS     CREATION_SCN
--------- ---------- ------------ -------- ------------------
    3         JZ      3071733832   NORMAL      1441806
    2      PDB$SEED   2786965489   NORMAL      1408718

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

 CON_ID      DBID        NAME     OPEN_MODE
-------- ------------ ---------- ------------
    2     2786965489   PDB$SEED   READ ONLY
    3     3071733832      JZ      READ WRITE

SQL>  alter session set container=JZ;
Session altered.

返回CDB

sql
ALTER SESSION SET CONTAINER = CDB$ROOT;

在PDB上创建用户并授权 ​

sql
SQL> create user test2 identified by test2pass;
User created.

SQL> grant dba,connect,resource,create view to test2;
Grant succeeded.

SQL> grant select any table to test2;
Grant succeeded.

登录方式 sqlplus 用户名/密码@PDB ​

bash
[oracle@f6a23ccb3202 ~]$ sqlplus test2/test2pass@JZ
SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 20 09:24:47 2025
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SpringBoot中配置连接方式 ​

CDB模式 ​

properties
spring.datasource.url=jdbc:oracle:thin:@192.168.10.10:1521:ORCL
spring.datasource.username=C##test
spring.datasource.password=testpass

PDB模式 ​

properties
spring.datasource.url=jdbc:oracle:thin:@//192.168.10.10:1521/JZ
spring.datasource.username=test2
spring.datasource.password=test2pass

数据泵大致流程 ​

创建数据目录 ​

bash
mkdir -p /opt/oracle/data

进入PDB ​

sql
sqlplus / as sysdba
alter session set container=JWZH;

创建用户 ​

sql
create user LIULIKE identified by sys;
grant dba to LIULIKE;
alter user LIULIKE identified by Liulike#123;

创建表空间 ​

sql
create TABLESPACE LIULIKE_DATA  
DATAFILE '/opt/oracle/data/LIULIKE.def' 
SIZE 1G
autoextend on next 100m 
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 128K;

更新用户表空间 ​

sql
ALTER USER LIULIKE DEFAULT TABLESPACE LIULIKE_DATA;

更新system用户 ​

sql
# 返回CDB
ALTER SESSION SET CONTAINER = CDB$ROOT;
alter user system identified by system;
grant dba to system;

创建目录路径 ​

sql
create directory data_dir as '/data';
Grant read,write on directory data_dir to system;
grant exp_full_database,imp_full_database to system;

导入/导出数据 ​

bash
impdp system/system@JWZH directory=data_dir table_exists_action=replace dumpfile=liulike.dmp full=y logfile=liulike.log
expdp system/system@JWZH schemas=USER1,USER2,...,USERn dumpfile=liulike.dmp directory=data_dir logfile=liulike.log