Oracle 19C常用sql

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;  -- 重启