1.元数据查询
CDB中包含了所有容器的元数据信息,因此可以在CDB中查看所有的PDB元数据,而不必切入PDB中
1.1.查看当前会话所在容器
-- 查看當前所在容器,上邊的在sqlplus中執行,下邊的可以在工具中執行
SHOW CON_NAME;
SELECT con_id, name, con_uid FROM v$containers WHERE con_id = SYS_CONTEXT('USERENV', 'CON_ID');
1.2.查看是否为CDB(CDB OR NONE-CDB)
-- 查看是否為CDB數據庫(在CDB或者PDB中都可以)
select name, created, cdb, con_id from v$database;
-- 需在CDB中以sys執行
select name, open_mode,open_time from v$pdbs;
1.3.查看每个PDB的数据文件
SELECT d.con_ID,
p.PDB_NAME,
p.PDB_NAME,
d.TABLESPACE_NAME,
d.FILE_NAME
FROM CDB_PDBS P, CDB_DATA_FILES D
WHERE p.PDB_ID(+) = d.CON_ID and pdb_name='PDB1'
ORDER BY d.con_id;
1.4.查看每个PDB数据库对象
SELECT p.PDB_ID,
p.PDB_NAME,
t.OWNER,
t.TABLE_NAME
FROM CDB_PDBS p, CDB_TABLES t -- 要查看其他对象,可以把CDB_TABLES换成其他视图
WHERE p.PDB_ID= t.CON_ID
AND p.PDB_NAME='PDB1'
ORDER BY t.TABLE_NAME;
1.5.查看每个PDB创建时间
/* Formatted on 2023/5/23 上午 11:32:52 (QP5 v5.163.1008.3004) */
SELECT DB_NAME,
CON_ID,
PDB_NAME,
OPERATION,
OP_TIMESTAMP,
CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
2.PDB管理
2.1.克隆一个PDB
-- 保证开启归档,PDB使用本地UNDO
CREATE PLUGGABLE DATABASE pdb2 FROM pdbl
PATH_PREFIX = '/disk2/oracle/pdb2'
FILE_NAME_CONVERT = ('/diskl/oracle/pdbl/', '/disk2/oracle/pdb2/');
CREATE PLUGGABLE DATABASE pdb2 FROM pdbl
FILE_NAME_CONVERT = ('/diskl/oracle/pdbl/', '/disk2/oracle/pdb2/') STORAGE (MAXSIZE 2G
MAX_SHARED_TEMP_SIZE 100M);
CREATE PLUGGABLE DATABASE pdb2 FROM pdbl@pdb1_link;
2.2.拔出和删除PDB
-- 拔掉PDB
ALTER PLUGGABLE DATABASE dwpdb UNPLUG INTO '/oracle/data/dwpdb.xml';
-- 删除PDB
DROP PLUGGABLE DATABASE dwpdb KEEP DATAFILES;
DROP PLUGGABLE DATABASE dwpdb INCLUDING DATAFILES;
3.容器间切换
show pdbs;
alter session set container=PDB1;
alter session set container=CDB$ROOT;
4.打开或关闭PDB
-- 下面仅操作pdb_ss
alter session set container=pdb_ss;
alter pluggable database close immediate;
alter pluggable database open read write;
-- 下面操作所有的pdb
alter session set container=CDBSROOT;
alter pluggable database ALL open read write;
alter pluggable database pdb_ss, pdbl close;
-- 启动pdb
startup pluggable database pdbl open; -- read/write
startup pluggable database pdbl open read only;
startup pluggable database pdbl force; -- 重启
标签:NAME,CDB,--,ID,pdbl,sql,Oracle,PDB,19C
From: https://www.cnblogs.com/monkey6/p/17479189.html