-- 进入 cdb
sqlplus /nolog
conn sys/sys as dba
-- 查看数据库基本信息
select name,cdb from v$database;
-- 查看容器的相关信息
select con_id,name from v$containers;
-- 查看数据文件的路径
select con_id,file_name from cdb_data_files order by 1;
-- 显示当前连接容器信息
show con_id con_name user
-- 显示所有 pdb
show pdbs
--start 和 stop 根容器
-- 启动数据
- --CDB
- sqlplus / as sysdba
- startup
- --PDB(所有 PDB)
- alter pluggable database all open;
-- 启动 pdb1 库
alter pluggable database pdb1 open;
-- 关闭数据库
- --CDB
- shutdown immediate
- --pdb1
- alter pluggable database pdb1 close;
- alter pluggable database pdb1 close immediate;
-- 关闭所有 pdb
alter pluggable database all close immediate;
-- 创建公用用户 (sys 和 system 在可插拔中就是自动创建的公用用户)
sqlplus / as sysdba
create user c#dba identified by alex;
-- 创建公用角色
sqlplus / as sysdba
- create role c##dbaprivs container=all;
- grant dba to c##dbaprivs container=all;
-- 公用角色给公用用户
grant c##dbaprivs to c#dba container=all;
-- 切换容器
alter session set container=ehrpdb;
-- 切换回根容器
alter session set container=cdb$root;
--2 创建可插拔数据库
--2.1 克隆种子容器
- create pluggable database cpcdb
- admin user cpcadm identified by cpcadm
- file_name_convert=('/oracle/app/oracle/oradata/edw/pdbseed','/oradata/edw/cpcdb/datafile/');
--2.2 直接创建数据库
CREATE PLUGGABLE DATABASE hrdb
ADMIN USER hrdba IDENTIFIED BY hrdba
STORAGE (MAXSIZE 100G MAX_SHARED_TEMP_SIZE 2G)
- DEFAULT TABLESPACE hrdb DATAFILE '/oradata/EDW/hrdb/datafile/hrdb001.dbf' SIZE 512M AUTOEXTEND on next 8M maxsize 10G;
- CREATE PLUGGABLE DATABASE cpcdb
ADMIN USER cpcdba IDENTIFIED BY cpcdba
STORAGE (MAXSIZE 100G MAX_SHARED_TEMP_SIZE 2G)
DEFAULT TABLESPACE tbscpc DATAFILE '/oradata/EDW/cpcdb/datafile/tbscpc001.dbf' SIZE 512M AUTOEXTEND on next 8M maxsize 10G;
--2.3 创建数据案例
- create pluggable database cpcdb
- admin user cpcadm identified by cpcadm
- file_name_convert=('/oracle/app/oracle/oradata/edw/pdbseed','/oradata/edw/cpcdb/datafile/');
- create pluggable database ncdb
- admin user ncadm identified by ncadm
- file_name_convert=('/oradata/edw/pdbseed','/oradata/edw/ncdb/datafile/');
- CREATE PLUGGABLE DATABASE ncdb
- ADMIN USER ncdba IDENTIFIED BY ncdba
- STORAGE (MAXSIZE 100G MAX_SHARED_TEMP_SIZE 2G);
-- 访问 ncdb 的插拔数据库
sqlplus sys/ncdba@oraerp:1521/ncdb as sysdba
-- 关闭 PDB 数据库
- alter pluggable database ncdb close immediate;
- alter pluggable database hrdb close immediate;
- alter pluggable database cpcdb close immediate;
- alter pluggable database edwpdb close immediate;
-- 删除 PDB 数据库
- DROP PLUGGABLE DATABASE ncdb INCLUDING DATAFILES;
- DROP PLUGGABLE DATABASE hrdb INCLUDING DATAFILES;
- DROP PLUGGABLE DATABASE cpcdb INCLUDING DATAFILES;
- DROP PLUGGABLE DATABASE edwpdb INCLUDING DATAFILES;
-- 创建 pdb 案例
- --1,cpcdb
- create pluggable database cpcdb
- admin user cpcadm identified by cpcadm
- file_name_convert=('/oradata/edw/pdbseed/','/oradata/edw/cpcdb/');
- --2,ncdb
- create pluggable database ncdb
- admin user ncadm identified by ncadm
- file_name_convert=('/oradata/edw/pdbseed/','/oradata/edw/ncdb/');
- --3,hrdb
- create pluggable database hrdb
- admin user hradm identified by hradm
- file_name_convert=('/oradata/edw/pdbseed/','/oradata/edw/hrdb/');
- alter pluggable database all open;
-- 登录 cpcdb
$sqlplus sys/Oracleedw13@192.168.2.218:1531/cpcdb as sysdba
$sqlplus sys/OraEdw!23@192.168.1.105:1533/ncdb as sysdba -- 含有! 需要单独输入密码
来源: http://www.bubuko.com/infodetail-2563993.html