By default, there are two PDBs created. One is the default database created during installation (PDBORCL12C) and the other one is a seed database which you can use to create new PDBs. To find out the list of PDBs, you can query the v$pdb or use the show pdbs command:
c:\> sqlplus /nolog
SQL> conn /as sysdba
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
--------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL12C MOUNTED
SQL> select name, open_mode,con_id from v$pdbs;
NAME OPEN_MODE CON_ID
------------ ---------- ----------
PDB$SEED READ ONLY 2
PDBORCL12C MOUNTED 3
SQL> col NAME format A12
SQL> select FILE#, ts.name, ts.ts#, ts.con_id
2 from v$datafile d, v$tablespace ts
3 where d.ts#=ts.ts#
4 and d.con_id=ts.con_id
5 order by 4,3;
FILE# NAME TS# CON_ID
---------- ------------ ---------- ----------
1 SYSTEM 0 1
3 SYSAUX 1 1
5 UNDOTBS1 2 1
6 USERS 4 1
2 SYSTEM 0 2
4 SYSAUX 1 2
7 SYSTEM 0 3
8 SYSAUX 1 3
9 USERS 3 3
10 EXAMPLE 4 3
10 rows selected.
The second SQL query shows the database files associated with each database. You can see that the CDB (CON_ID=1) has SYSTEM, SYSAUX, UNDOTBS1 and USERS files. The seed PDB, pdb$seed(CON_ID=2), has two SYSETM and SYSAUX files. The pdborcl12c database created by default (CON_ID=3) has SYSTEM, SYSAUX, USERS and EXAMPLE data files.
If we query the v$datafile view, we can verify the details for each PDBs. The following example list the database files for PDB
SQL> select name from v$datafile where con_id = 3;
NAME
----------------------------------------------------------------------------
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SYSTEM01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SYSAUX01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\SAMPLE_SCHEMA_USERS01.DBF
C:\APP\ORACLE_USER\ORADATA\ORCL12C\PDBORCL12C\EXAMPLE01.DBF
Step 2: Create a new pluggable database
Now, we can create a pluggable database. If you are not sure that you are in the container database, you can use the following command:
SQL> alter session set container=cdb$root;
Session altered.
The we can create a new pluggable database from the seed database. You can also copy from an existing PDB.
Why do I get the "ORA-65081: database or pluggable database is not open in read only mode." error?
You need to make sure the exisiting database is in open and read only mode before creating PDB from it. The following command will be helpful.
SQL>alter pluggable database pdb1 close;
Pluggable database altered.
SQL>alter pluggable database pdb2 open read only;
Pluggable database altered.
SQL> create pluggable database pdbdemo admin user admin identified by admin
file_name_convert = ('pdbseed', 'pdbdemo');
Pluggable database created.
SQL> select pdb_name from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
PDBORCL12C
PDB$SEED
PDBDEMO
SQL> col name format a30
SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;
NAME OPEN_MODE INST_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 1
PDBORCL12C MOUNTED 1
PDBDEMO MOUNTED 1
SQL> alter pluggable database pdbdemo open;
Pluggable database altered.
SQL> select Name, Open_Mode, Inst_ID from gv$PDBs;
NAME OPEN_MODE INST_ID
------------------------------ ---------- ----------
PDB$SEED READ ONLY 1
PDBORCL12C MOUNTED 1
PDBDEMO READ WRITE 1
SQL> select name, con_id from v$active_services order by 1;
NAME CON_ID
------------------------------ ----------
SYS$BACKGROUND 1
SYS$USERS 1
orcl12c.us.oracle.com 1
orcl12cXDB 1
pdbdemo.us.oracle.com 4
pdborcl12c.us.oracle.com 3
6 rows selected.
If logging into a PDB, the operations are pretty much the same as a database in Oracle Database 11g. The default tablespace for the newly created PDB from the seed is SYSTEM.
SQL> alter session set container=pdbdemo;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDBDEMO
SQL> show user
USER is "SYS"
SQL> select property_value from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
-------------------------------------------------------------------------
SYSTEM
Why do I get the "ORA-65016: FILE_NAME_CONVERT must be specified" error when creating pluggable database?
The file_name_convert specifies how new data filenames are derived from those of the seed or from an exsiting PDB. During the PDB creation Oracle Database 13c actually only copies 2 database files, for the System and the Sysaux tablespaces, to the location of new PDB. The other database files for undo, redo, and so on are global for the whole CDB and belongs to the container called CDB$Root.
Step 3: Dropping PDBs
If you want to remove a PDB created, you can run the following command in the CDB. The PDB to be deleted have to be closed before deletion:
SQL> alter session set container=cdb$root;
Session altered.
SQL> drop pluggable database pdbdemo including datafiles;
drop pluggable database seed_copy including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDBDEMO is not closed on all instances.
SQL> alter pluggable database pdbdemo close;
Pluggable database altered.
SQL> drop pluggable database pdbdemo including datafiles;
Pluggable database dropped.