oracle12c管理命令1

查看是否是CDB数据库：

select name,cdb from v$database;

查看所有PDB数据库信息：

col name for a30

set lines 150

select con_id, dbid, guid, name , open_mode from v$pdbs;

查看所有可插拔数据库状态：

col pdb_name for a30

select pdb_id,pdb_name,status from cdb_pdbs;

查看容器内的所有数据库：

select con_id,name from v$containers;

查看当前容器ID,名称和用户

show con_id con_name user

查看容器内的所有数据文件：

col file_name for a70

select con_id,file_name from cdb_data_files order by 1;

打开可插拔数据库：

alter pluggable database salespdb open;

关闭可插拔数据库：

alter pluggable database dkpdb close immediate;

启动所有可插拔数据库：

alter pluggable database all open;

关闭所有可插拔数据库：

alter pluggable database all close immediate;

关闭所有可插拔数据库，即关闭实例：

shutdown immediate;

创建公用用户：

create user c##dba identified by dba;

查询oracle CDB中所有容器的空间信息:

set lines 132 pages 100

col con_name form a15 head "Container|Name"

col tablespace_name form a15

col fsm form 999,999,999,999 head "Free|Space Meg."

col apm form 999,999,999,999 head "Alloc|Space Meg."

--

compute sum of fsm apm on report

break on report on con_id on con_name on tablespace_name

--

with x as (SELECT c1.con_id,cf1.tablespace_name,SUM(cf1.bytes)/1024/1024 fsm

FROM cdb_free_space cf1,v$containers c1

WHERE cf1.con_id=c1.con_id

GROUP BY c1.con_id,cf1.tablespace_name),

y as (SELECT c2.con_id,cd.tablespace_name,SUM(cd.bytes)/1024/1024 apm

FROM cdb_data_files cd,v$containers c2

WHERE cd.con_id=c2.con_id

GROUP BY c2.con_id,cd.tablespace_name)

SELECT x.con_id,v.name con_name,x.tablespace_name,x.fsm,y.apm

FROM x,y,v$containers v

WHERE x.con_id =y.con_id

AND x.tablespace_name=y.tablespace_name

AND v.con_id =y.con_id

UNION

SELECT vc2.con_id,vc2.name,tf.tablespace_name,null,SUM(tf.bytes)/1024/1024

FROM v$containers vc2,cdb_temp_files tf

WHERE vc2.con_id=tf.con_id

GROUP BY vc2.con_id,vc2.name,tf.tablespace_name

ORDER BY 1,2;

切换子容器：

alter session set container=ORACLE12PDB1;

切换根容器：

alter session set container=cdb$root;

克隆种子容器：

CREATE PLUGGABLE DATABASE salespdb

ADMIN USER salesadm IDENTIFIED BY oracle

FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB/pdbseed','/u01/app/oracle/oradata/CDB/salespdb');

克隆现存可插拔数据库：

alter pluggable database salespdb close;

alter pluggable database salespdb open read only:

CREAT PLUGGABLE DATABASE salespdb2

FROM salespdb

FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB/salespdb','/u01/dbfile/CDB/salespdb2')

STORAGE(MAXSIZE 6G MAX_SHARED_TEMP_SIZE 100M);

删除可插拔数据库：

drop pluggable database dkpdb including datafiles;

了解更多内容，请关注：