oracle12.2 CDB PDB基本管理操作

 

 

容器间切换 

切换到对应的PDBS
SQL> alter session set container=pdb1;
Session altered.
SQL> alter database open;

切换到cdb$root下：

SQL>conn / as sysdba

可以用os认证、ezconnect方式连接到cdb$root环境下：
SQL> conn / as sysdba
Connected.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> conn sys/oracle@hostname:1521/cdb1 as sysdba
Connected.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
用ezconnect连接PDBS
SQL> conn sys/oracle@hostname:1521/pdb1 as sysdba
Connected.
SQL> show con_name;
CON_NAME
------------------------------
PDB1

 12c 创建公用用户和本地用户 

创建公用用户admin, 需要使用C##或者c##作为该用户名的开头。

SQL > create user c##admin identified by admin;

创建PDB

SQL>conn / as sysdba

第一种方式:使用seed pdb新建pdb1

SQL> create pluggable database pdb1 admin user adminpdb1 identified by adminpdb1 roles=(connect) file_name_convert=('/u01/app/oracle/oradata/chicago/pdbseed/','/u01/app/oracle/oradata/chicago/pdb1/');

Pluggable database created.   #这里在创建pdb1的同时创建了本地用户adminpdb1

#######################################################

CREATE PLUGGABLE DATABASE pdb1 admin user adminpdb1 identified by adminpdb1 roles=(connect)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE tbs_pdb1
DATAFILE '/u01/app/oracle/oradata/chicago/pdb1/pdb_1.dbf' SIZE 250M AUTOEXTEND ON
PATH_PREFIX = '/u01/app/oracle/oradata/chicago/pdb1/'
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/chicago/pdbseed/',
'/u01/app/oracle/oradata/chicago/pdb1/');

#######################################################

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 MOUNTED
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDB1 READ WRITE NO

第二种方式:使用pdb1新建pdb2

 SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 PATH_PREFIX = '/u01/app/oracle/oradata/chicago/pdb2/' FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/chicago/pdb1/','/u01/app/oracle/oradata/chicago/pdb2/');

可以看出创建pdb1时创建的用户、表空间等也被复制过来，如果不想复制数据则可以使用NO DATA子句：

 CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 PATH_PREFIX = '/u01/app/oracle/oradata/chicago/pdb2/' FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/chicago/pdb1/','/u01/app/oracle/oradata/chicago/pdb2/') NODATA;

使用NO DATA子句创建pdb2，拷贝除了表数据之外的内容，包括表结构