Tuesday, August 6, 2013

Oracle 12c multitenant (Part2)

One might be curious what's inside the CDB. To view information about a container in a CDB. As you can see there are four containers inside this CDB namely CDB$ROOT, PDB$SEED, PDB12c, SALESPDB.


COLUMN NAME FORMAT A8

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

 
 CON_ID       DBID    CON_UID GUID
-------- ------ ---------- ---------- --------------------------------
CDB$ROOT      1 4021232689          1 DD7C48AA5A4504A2E04325AAE80A403C
PDB$SEED      2 4065253802 4065253802 E32E698EA24E0CA2E043C83C16AC771B
PDB12C        3  474800360  474800360 E32E71077E390EC3E043C83C16AC372F
SALESPDB      4 2267593339 2267593339 E330F6DC0A04184BE043C83C16AC6564
 
To view the status,
COLUMN PDB_NAME FORMAT A15
 
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

 
PDB_ID PDB_NAME        STATUS
------ --------------- -------------
     2 PDB$SEED        NORMAL
     3 PDB12C          NORMAL
     4 SALESPDB        NORMAL



COLUMN NAME FORMAT A15
COLUMN RESTRICTED FORMAT A10
COLUMN OPEN_TIME FORMAT A30
 
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;



NAME     OPEN_MODE  RESTRICTED OPEN_TIME
-------- ---------- ---------- ------------------------------
PDB$SEED READ ONLY  NO         05-AUG-13 02.54.14.169 AM
PDB12C   MOUNTED
SALESPDB READ WRITE NO         05-AUG-13 04.32.06.770 AM


Showing users in multiple PDB.
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;


To display various datafiles
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;





To show the tempfile
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;


To show the services
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;


I will write more on the cloning of PDB.

No comments: