# 查看当前数据库是否为CDB
SQL> SELECT name, cdb, con_id FROM v$database;

NAME CDB CON_ID
——— — ———-
ORCL NO 0

# 创建CDB数据库
$ dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname orclcdb \
-sid orclcdb \
-responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword Oracle123 \
-systemPassword Oracle123 \
-createAsContainerDatabase true \
-numberOfPDBs 1 \
-pdbName pdb1 \
-pdbAdminPassword Oracle123 \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 2048 \
-storageType FS \
-datafileDestination /oracle/app/oracle/oradata

Copying database files
1% complete
2% complete
…
100% complete
Creating and starting Oracle instance
1% complete
…
100% complete
Completing Database Creation
1% complete
…
100% complete
Look at the log file “/oracle/app/oracle/cfgtoollogs/dbca/orclcdb/orclcdb.log” for further details.

# 验证CDB创建成功
SQL> SELECT name, cdb, con_id FROM v$database;

NAME CDB CON_ID
——— — ———-
ORCLCDB YES 0

# 查看CDB中的容器
SQL> SELECT con_id, name, open_mode FROM v$containers ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE

# 连接到CDB
SQL> conn sys/Oracle123@orclcdb as sysdba
Connected.

# 创建PDB
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_admin IDENTIFIED BY Oracle123;

Pluggable database created.

# 查看PDB状态
SQL> SELECT con_id, name, open_mode FROM v$containers ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 MOUNTED

# 打开PDB
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;

Pluggable database altered.

# 查看PDB状态
SQL> SELECT con_id, name, open_mode FROM v$containers ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 READ WRITE

# 使用种子PDB创建PDB
SQL> CREATE PLUGGABLE DATABASE pdb3 FROM pdb$seed;

Pluggable database created.

# 打开PDB
SQL> ALTER PLUGGABLE DATABASE pdb3 OPEN;

Pluggable database altered.

# 查看PDB状态
SQL> SELECT con_id, name, open_mode FROM v$containers ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 READ WRITE
5 PDB3 READ WRITE

# 创建资源计划
SQL> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan => ‘cdb_plan’,
comment => ‘CDB Resource Plan’);
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => ‘cdb_plan’,
pluggable_database => ‘pdb1’,
shares => 3,
utilization_limit => 80);
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => ‘cdb_plan’,
pluggable_database => ‘pdb2’,
shares => 2,
utilization_limit => 60);
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => ‘cdb_plan’,
pluggable_database => ‘pdb3’,
shares => 1,
utilization_limit => 40);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

PL/SQL procedure successfully completed.

# 启用资源计划
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘cdb_plan’ SCOPE=BOTH;

System altered.

# 查看资源计划
SQL> SELECT
plan,
pluggable_database,
shares,
utilization_limit
FROM dba_cdb_rsrc_plan_directives
ORDER BY plan, pluggable_database;

PLAN PLUGGABLE_DATABASE SHARES UTILIZATION_LIMIT
———- ——————– ——- —————–
cdb_plan pdb1 3 80
cdb_plan pdb2 2 60
cdb_plan pdb3 1 40

# 查看所有PDB
SQL> SELECT con_id, name, open_mode, total_size, free_space
FROM v$pdbs
ORDER BY con_id;

CON_ID NAME OPEN_MODE TOTAL_SIZE FREE_SPACE
—— ——— ———- ———- ———-
2 PDB$SEED READ ONLY 1048576 524288
3 PDB1 READ WRITE 1048576 524288
4 PDB2 READ WRITE 1048576 524288
5 PDB3 READ WRITE 1048576 524288

# 关闭PDB
SQL> ALTER PLUGGABLE DATABASE pdb2 CLOSE;

Pluggable database altered.

# 查看PDB状态
SQL> SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 MOUNTED
5 PDB3 READ WRITE

# 打开PDB
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;

Pluggable database altered.

# 查看PDB状态
SQL> SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 READ WRITE
5 PDB3 READ WRITE

# 删除PDB
SQL> ALTER PLUGGABLE DATABASE pdb3 CLOSE;

Pluggable database altered.

SQL> DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

Pluggable database dropped.

# 查看PDB状态
SQL> SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;

CON_ID NAME OPEN_MODE
—— ——— ———-
2 PDB$SEED READ ONLY
3 PDB1 READ WRITE
4 PDB2 READ WRITE

# 创建公共用户
SQL> CREATE USER c##admin IDENTIFIED BY Oracle123 CONTAINER = ALL;

User created.

SQL> GRANT DBA TO c##admin CONTAINER = ALL;

Grant succeeded.

# 查看公共用户
SQL> SELECT username, common, con_id FROM dba_users WHERE common = ‘Y’;

USERNAME COMMON CON_ID
———— — ———-
C##ADMIN Y 0

# 创建本地用户
SQL> ALTER SESSION SET CONTAINER = pdb1;

Session altered.

SQL> CREATE USER local_user IDENTIFIED BY Oracle123;

User created.

SQL> GRANT CONNECT, RESOURCE TO local_user;

Grant succeeded.

# 查看本地用户
SQL> SELECT username, common, con_id FROM dba_users WHERE common = ‘N’;

USERNAME COMMON CON_ID
———— — ———-
LOCAL_USER N 3

# 切换到PDB
SQL> ALTER SESSION SET CONTAINER = pdb2;

Session altered.

# 查看本地用户
SQL> SELECT username, common, con_id FROM dba_users WHERE common = ‘N’;

USERNAME COMMON CON_ID
———— — ———-
PDB_ADMIN N 4

# 查看PDB性能统计
SQL> SELECT
con_id,
name,
open_mode,
total_size,
free_space,
round(free_size / total_size * 100, 2) AS free_space_pct
FROM v$pdbs
ORDER BY con_id;

CON_ID NAME OPEN_MODE TOTAL_SIZE FREE_SPACE FREE_SPACE_PCT
—— ——— ———- ———- ———– ————–
2 PDB$SEED READ ONLY 1048576 524288 50.00
3 PDB1 READ WRITE 1048576 524288 50.00
4 PDB2 READ WRITE 1048576 524288 50.00

# 查看PDB会话统计
SQL> SELECT
con_id,
username,
status,
count(*)
FROM v$session
WHERE con_id > 2
GROUP BY con_id, username, status
ORDER BY con_id;

CON_ID USERNAME STATUS COUNT(*)
—— ————- ——– ———-
3 SYS ACTIVE 1
4 PDB_ADMIN ACTIVE 1

# 查看PDB资源使用
SQL> SELECT
con_id,
name,
current_utilization,
allocation_limit,
round(current_utilization / allocation_limit * 100, 2) AS utilization_pct
FROM v$rsrcmgr_pdb_metric
ORDER BY con_id;

CON_ID NAME CURRENT_UTILIZATION ALLOCATION_LIMIT UTILIZATION_PCT
—— ——— ——————- —————– —————-
3 PDB1 20 80 25.00
4 PDB2 15 60 25.00

# 查看CDB性能统计
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name LIKE ‘%session%’
AND value > 0
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;

NAME VALUE
—————————————- ———-
logons cumulative 1000
session cursor cache hits 500
opened cursors cumulative 500
session logical reads 100000
session uga memory 10000
session pga memory 5000

# 查看CDB资源使用
SQL> SELECT
name,
total_size,
free_size,
used_size,
round(used_size / total_size * 100, 2) AS used_pct
FROM v$sgainfo
WHERE name IN (‘Database Buffers’, ‘Shared Pool’, ‘Large Pool’);

NAME TOTAL_SIZE FREE_SIZE USED_SIZE USED_PCT
—————— ———– ———- ——— ———-
Database Buffers 1048576 524288 524288 50.00
Shared Pool 524288 262144 262144 50.00
Large Pool 65536 32768 32768 50.00

# 查看CDB会话统计
SQL> SELECT
con_id,
username,
status,
count(*)
FROM v$session
GROUP BY con_id, username, status
ORDER BY con_id;

CON_ID USERNAME STATUS COUNT(*)
—— ————- ——– ———-
0 SYS ACTIVE 1
1 C##ADMIN ACTIVE 1
3 SYS ACTIVE 1
4 PDB_ADMIN ACTIVE 1