创建PDB方法（一）

从pdb$seed创建pdb

使用种子PDB PDB$SEED在容器数据库cdb1中创建新的PDB:pdb2
查看 pdb 状态:
SYS@cdb1>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED

2 PDB$SEED                       READ ONLY  NO
3 PDB1                           READ WRITE NO
复制

查看 pdb$seed 数据文件路径:

SYS@cdb1> alter session set container=pdb$seed;
SYS@cdb1> select file_name from cdb_data_files;
FILE_NAME

/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/undotbs01.dbf

切回 cdb1
SYS@cdb1> conn / as sysdba
SYS@cdb1> alter session set container=cdb$root;
SYS@cdb1> show pdbs

创建新 PDB 目录
SYS@cdb1> !mkdir /u01/app/oracle/oradata/cdb1/pdb2

创建 pdb2
SYS@cdb1> create pluggable database pdb2 admin user pdb2_admin identified by oracle roles=(connect) file_name_convert=(’/u01/app/oracle/oradata/cdb1/pdbseed’,’/u01/app/oracle/oradata/cdb1/pdb2’);
或者省略一样的路径/u01/app/oracle/oradata/cdb1/
SYS@cdb1> create pluggable database pdb2 admin user pdb2_admin identified by oracle roles=(connect) file_name_convert=(‘pdbseed’,‘pdb2’);
Pluggable database created.

查看新创建的pdb2
SYS@cdb1> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
复制

2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SYS@cdb1>

查看监听程序状态
$lsnrctl status
Service “pdb2” has 1 instance(s).
Instance “cdb1”, status READY, has 1 handler(s) for this service…

打开pdb2：
SYS@cdb1> alter pluggable database pdb2 open;
Pluggable database altered.
SYS@cdb1> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED

2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SYS@cdb1>

查看 pdb2 数据文件
SYS@cdb1> col file_name for a50
SYS@cdb1> select file_id,file_name,tablespace_name,con_id from cdb_data_files where con_id=4;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID

13 /u01/app/oracle/oradata/cdb1/pdb2/system01.dbf SYSTEM 4
14 /u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf SYSAUX 4
15 /u01/app/oracle/oradata/cdb1/pdb2/undotbs01.dbf UNDOTBS1 4
SYS@cdb1>

配置 tnsnames.ora
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
pdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)

pdb2 通过服务名登录
$ sqlplus sys/oracle@pdb2 as sysdba
SYS@pdb2> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED

4 PDB2 READ WRITE NO
SYS@pdb2>

SYS@pdb2> col username for a30
SYS@pdb2> select username,common,con_id from cdb_users where account_status=‘OPEN’;
USERNAME COM CON_ID

SYS YES 4
SYSTEM YES 4
PDB2_ADMIN NO 4
SYS@pdb2>

创建并指定默认表空间
SYS@pdb2> create tablespace users datafile ‘/u01/app/oracle/oradata/cdb1/pdb2/users01.dbf’ size 50m;
SYS@pdb2> alter pluggable database default tablespace users;
SYS@cdb1> select file_id,file_name,tablespace_name,con_id from cdb_data_files where con_id=4;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID

    13 /u01/app/oracle/oradata/cdb1/pdb2/system01.dbf     SYSTEM                                4
    14 /u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf     SYSAUX                                4
    15 /u01/app/oracle/oradata/cdb1/pdb2/undotbs01.dbf    UNDOTBS1                              4
    16 /u01/app/oracle/oradata/cdb1/pdb2/users01.dbf      USERS                                 4  
复制

查看临时表空间：
SYS@pdb2> select file_id,file_name,tablespace_name,con_id from cdb_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID

     4 /u01/app/oracle/oradata/cdb1/pdb2/temp012020-08-16_11-56-49-011-AM.dbf   TEMP               4
复制

增加一个tmep文件：
SYS@pdb2>alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/cdb1/pdb2/temp01.dbf’ size 10m autoextend on;
删除原来的temp文件：
SYS@pdb2>alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/cdb1/pdb2/temp012020-08-16_11-56-49-011-AM.dbf’;
再次查看临时表空间：
SYS@pdb2> select file_id,file_name,tablespace_name,con_id from cdb_temp_files;
FILE_ID FILE_NAME TABLESPACE_NAME CON_ID

     4 /u01/app/oracle/oradata/cdb1/pdb2/temp01.dbf       TEMP                                    4
复制

SYS@pdb2>
设置默认临时表空间：
SYS@pdb2> alter database default temporary tablespace temp;
查看默认的临时表空间：
SYS@PROD>select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like ‘DEFAULT_TEMP%’;
PROPERTY_NAME PROPERTY_VALUE

DEFAULT_TEMP_TABLESPACE TEMP