Oracle 12/19C多租户关于CDB、PDB的常用运维SQL语句

1、查看当前容器和当前用户
show con_id con_name user
select sys_context('userenv', 'con_name'),sys_context('userenv','session_user') from dual;

2、查看所有PDB
show pdbs
select con_id, dbid, guid, name , open_mode from v$pdbs;

3、启动和关闭PDB

启动PDB
alter pluggable database all open; --开启所有PDB
alter pluggable database PDBNAME open; --开启名称为PDBNAME的PDB
alter session set container=PDBNAME;–切换名称为PDBNAME的PDB里面去开启
startup

关闭PDB：
alter pluggable database all close immediate;      --关闭所有PDB,不加immediate就是默认的normal
alter pluggable database PDBNAME close immediate;  –-关闭名称为PDBNAME的PDB,不加immediate就是默认的normal
alter session set container=PDBNAME;               –-切换名称为PDBNAME的PDB里面去关闭
shutdown immediate

4、创建PDB的多种方法

创建一个新的PDB：（已经存在db_create_file_dest的情况下）
create pluggable database test admin user admin identified by admin;
alter pluggable database test open; --将test打开

根据seed模板创建一个新PDB(不存在db_create_file_dest的情况下,必须加上file_name_convert)
create pluggable database pdb1 admin user pdb1 identified by pdb1 file_name_convert=('/u01/app/oracle/oradata/ocp/pdbseed','/u01/app/oracle/oradata/ocp1/pdb1');
alter pluggable database pdb1 open; --将test打开

克隆PDB
create pluggable database test2 from test; --test必须是打开的，才可以作为克隆源
alter pluggable database test2 open; --然后打开这个pdb

create pluggable database pdb2 from pdb1
path_prefix='/disk2/oracle/pdb2'
file_name_convert=('/disk1/oracle/pdb1/','/disk2/oracle/pdb2/') storage (maxsize 2G max_shared_temp_size 100M);

create pluggable database pdb3 from pdb2
path_prefix='+DATA/CDB/pdb3'
storage (maxsize 2G max_shared_temp_size 100M);

删除PDB
alter pluggable database test2 close immediate; --关闭之后才能删除
drop pluggable database test2 including datafiles; --删除PDB test2并同时把数据文件也从磁盘上删除了,必须加上including datafiles，不加including datafiles或加keep datafiles都会报错ORA-65179: cannot keep datafiles for a pluggable database that is not unplugged

unplug拔下某个数据库（必须要into到具体的xml格式的文件）
  alter pluggable database pocp1 close immediate;                      –-之后执行cdb_data_files查不到该pdb的数据文件，但是该pdb的数据文件还在, show pdbs还可以看到该pdb
  alter pluggable database pocp1 unplug into '/home/oracle/pocp1.xml'; –-之后执行cdb_data_files查不到该pdb的数据文件，但是该pdb的数据文件还在, show pdbs还可以看到该pdb
  drop pluggable database pocp1; 
--unplug后drop database不加including datafiles就是默认的keep datafiles;该pdb的数据文件还在,show pdbs看不到该pdb;
--加了including datafiles的话,该pdb的数据文件被删除了
drop pluggable database pocp1=drop pluggable database pocp1 keep datafiles

插上这个数据库（上面的拔下操作执不执行第三步drop操作的话，执行如下语句会报错ORA-65012: Pluggable database pocp1 already exists，
上面的拔下操作执第三步时加了including datafiles则执行如下语句会报错ORA-19505: failed to identify file,ORA-27037: unable to obtain file status)

SQL> create pluggable database pocp1 using '/home/oracle/pocp1.xml' nocopy tempfile reuse;
SQL> alter pluggable database pocp1 open;

插上一个数据库时可以修改PDB名称，/home/oracle/pocp1.xml是pocp1创建的，该xml文件中显示POCP1，但是可以利用该xml创建一个其他名称的PDB，比如TDB101
SQL>create pluggable database TDB101 using '/home/oracle/pocp1.xml' nocopy tempfile reuse;

5、查看pdb 和 cdb的数据文件

--查看所有数据文件

col file_name for a80;
col pdb_name for a15;
set linesize 400;
select d.con_id,p.pdb_name,d.file_id,d.tablespace_name,d.file_name 
from cdb_data_files d,
cdb_pdbs p 
where p.pdb_id(+)=d.con_id
order by d.con_id;

查询CDB$ROOT的数据文件
select file_name from cdb_data_files where con_id=1;

查询某个PDB的数据文件
select file_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and b.pdb_name='XX'

查询某个数据文件对应的PDB
select pdb_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and a.file_name like '%XX%';

查询PDB$SEED的数据文件
alter session set "_exclude_seed_cdb_view"=FALSE;
select file_name from cdb_data_files where con_id=2;

每个PDB执行如下语句只能看到自己这个容器下的数据文件，CDB才能看到所有容器的数据文件
select file_name,file_id,CON_ID from cdb_data_files;

每个容器执行如下语句只能看到自己这个容器下的数据文件，CDB也只能看到自己这个容器的数据文件
select file_name,file_id,from dba_data_files;

PDB下查cdb_data_files和dba_data_files的结果是一样，都是PDB自己的文件

PDB下show pdbs只能看到自己

6、查看pdb下的表
col owner for a20;
col table_name for a50;
col pdb_name for a15;
set linesize 400;
set pagesize 400;
select p.pdb_id,p.pdb_name,t.owner,t.table_name
from cdb_pdbs p,cdb_tables t
where p.pdb_id = t.con_id
and t.owner not in ('SYS','SYSTEM','DBSNMP','WMSYS','APPQOSSYS','XDB','OUTLN')
--and p.pdb_name = 'PDB2'
order by p.pdb_id,t.owner,t.table_name;

7、查看pdb历史（它是什么创建的）
--cdb_pdb_history视图提供了pdb怎么来的，怎么被创建的，什么时候被创建
col pdb_name format a10
col db_name format a10
col cloned_from_pdb_name for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select con_id,db_name,pdb_name,operation,op_timestamp,cloned_from_pdb_name
from cdb_pdb_history 
order by CON_ID;

8、确定有哪些服务
select name,service_id,con_name,con_id from v$active_services
order by  name;

9、查看pdb可修改参数
select * from v$parameter 
where ispdb_modifiable='TRUE'
--and name like 'optim%';
复制