To determine the current container ID:
--Check PDB
select b.con_id, a.pdb_name, a.status, b.open_mode from
dba_pdbs a, v$pdbs b where a.pdb_name=b.name order by b.con_id;
select name from v$datafile where con_id=2;
--OPEN/CLOSE OF PDBS
ALTER PLUGGABLE DATABASE PDB_SANA close;
Drop pluggable database PDB_SANA including datafiles;
ALTER PLUGGABLE DATABASE PDB_SANA close;
--CREATE PDB DB
create pluggable database pdb_lab admin user pdb_lab_admin identified by pdb_lab_admin;
create pluggable database pdb_lab admin user pdb_lab_admin identified by pdb_lab_admin;
roles=(DBA)
create pluggable database pdb_lab1
admin user pdb_lab1_admin identified by pdb_lab1_admin
default tablespace pdb_lab1_tbs
storage(maxsize 2 max_shared_temp_size 2g);
create pluggable database pdb_lab2
admin user pdb_lab2_admin identified by pdb_lab2_admin
default tablespace pdb_lab2_tbs
datafile '/oraundo/pdb/pdb_lab2_001.dat'
size 1g
file_name_convert=('/oraundo/pdb/datafile/o1_mf_system_cm4zwq8g_.dbf','/oraundo/pdb/oradata/system01.dbf')
storage (maxsize 5G MAX_SHARED_temp_size 5G)
PATH_PREFIX='/oraundo/pdb/';
create pluggable database PDB12C1 admin user daniel identified by daniel FILE_NAME_CONVERT = (
-- SYSTEM Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_system_8xmq0g7t_.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/system01.dbf',
-- SYSAUX Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_sysaux_8xmq0g3g_.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/sysaux01.dbf',
-- TEMP Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/pdbseed_temp01.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/temp01.dbf');
--PLUG/UNPLUG of PDBs
Begin
DBMS_PDB.DESCRIBE(
PDB_DESCR_FILE=>'/u01/app/oracle/pdb_lab.xml',
PDB_NAME => 'PDB_LAB');
END;
/
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
Create pluggable database PDB_LAB1 using '/u01/app/oracle/pdb_lab.xml' nocopy tempfile reuse;
Create pluggable database PDB_LAB1 as clone using '/u01/app/oracle/pdb_lab.xml' nocopy tempfile reuse;
SHOW CON_ID
To determine the current container name:
SHOW CON_NAME
Determining Whether a Database is a CDB
SELECT CDB FROM V$DATABASE;
Viewing Identifying Information About Each Container in a CDB
COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
Viewing Container ID, Name, and Status of Each PDB
COLUMN PDB_NAME FORMAT A15
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
Viewing the Open Mode of Each PDB
COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;
Showing the Tables Owned by Specific Schemas in Multiple PDBs
COLUMN PDB_NAME FORMAT A15
COLUMN OWNER FORMAT A15
COLUMN TABLE_NAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2 AND t.OWNER IN('HR','OE') AND p.PDB_ID = t.CON_ID ORDER BY p.PDB_ID;
--Check PDB
select b.con_id, a.pdb_name, a.status, b.open_mode from
dba_pdbs a, v$pdbs b where a.pdb_name=b.name order by b.con_id;
select name from v$datafile where con_id=2;
--OPEN/CLOSE OF PDBS
ALTER PLUGGABLE DATABASE PDB_SANA close;
Drop pluggable database PDB_SANA including datafiles;
ALTER PLUGGABLE DATABASE PDB_SANA close;
--CREATE PDB DB
create pluggable database pdb_lab admin user pdb_lab_admin identified by pdb_lab_admin;
create pluggable database pdb_lab admin user pdb_lab_admin identified by pdb_lab_admin;
roles=(DBA)
create pluggable database pdb_lab1
admin user pdb_lab1_admin identified by pdb_lab1_admin
default tablespace pdb_lab1_tbs
storage(maxsize 2 max_shared_temp_size 2g);
create pluggable database pdb_lab2
admin user pdb_lab2_admin identified by pdb_lab2_admin
default tablespace pdb_lab2_tbs
datafile '/oraundo/pdb/pdb_lab2_001.dat'
size 1g
file_name_convert=('/oraundo/pdb/datafile/o1_mf_system_cm4zwq8g_.dbf','/oraundo/pdb/oradata/system01.dbf')
storage (maxsize 5G MAX_SHARED_temp_size 5G)
PATH_PREFIX='/oraundo/pdb/';
create pluggable database PDB12C1 admin user daniel identified by daniel FILE_NAME_CONVERT = (
-- SYSTEM Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_system_8xmq0g7t_.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/system01.dbf',
-- SYSAUX Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/o1_mf_sysaux_8xmq0g3g_.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/sysaux01.dbf',
-- TEMP Tablespace
'/opt/oracle/app/oradata/CDB12C/datafile/pdbseed_temp01.dbf',
'/opt/oracle/app/oradata/PDB12C1/datafile/temp01.dbf');
--PLUG/UNPLUG of PDBs
Begin
DBMS_PDB.DESCRIBE(
PDB_DESCR_FILE=>'/u01/app/oracle/pdb_lab.xml',
PDB_NAME => 'PDB_LAB');
END;
/
DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
Create pluggable database PDB_LAB1 using '/u01/app/oracle/pdb_lab.xml' nocopy tempfile reuse;
Create pluggable database PDB_LAB1 as clone using '/u01/app/oracle/pdb_lab.xml' nocopy tempfile reuse;
Showing the Users in Multiple PDBs
COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;
Showing the Data Files for Each PDB in a CDB
COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;
Showing the Temp Files in a CDB
COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;
Showing the Services Associated with PDBs
COLUMN NETWORK_NAME FORMAT A30
COLUMN PDB FORMAT A15
COLUMN CON_ID FORMAT 999
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL AND
CON_ID > 2 ORDER BY PDB;
To list the initialization parameters that are modifiable in each container:
SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE = 'TRUE' ORDER BY NAME;
SELECT CON_NAME_TO_ID('HRPDB') FROM DUAL;
