How to identity am i connected PDB or CDB?
In Oracle Database 12c, we can still find this information from V$INSTANCE. Breath a sigh of relief, for the moment! What if I connect to a container database and then issue an ALTER SESSION command to move into a different container, i.e. as PDB? Will I get the name of the PDB that I move into from the V$INSTANCE view or do I need to look somewhere else? Lets take a look.
Connect to the container database as usual and use the V$INSTANCE view to see where I’m at.
[oracle@r12c ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 11 10:52:33 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select instance_name, version, status, con_id from v$instance;
INSTANCE_NAME VERSION STATUS CON_ID
---------------- ----------------- ------------ ----------
cdb1 12.1.0.2.0 OPEN 0
Lets move into a PDB. I have created the ORCL.
SQL>alter session set container=ORCL;
Session altered.
Did I actually move containers? How can I tell? Instead of selecting INSTANCE_NAME from V$INSTANCE; we have two new SHOW commands that will provide us the information we are looking for. These command are quite simple (SHOW CON_ID & SHOW CON_NAME).
SQL> show con_id;
CON_ID
------------------------------
4
SQL> show con_name
CON_NAME
------------------------------
ORCL
This view provides similar information as V$INSTANCE does; yet is only specific to PDBs. If we wanted to find out the container_id, name, open_mode and size of the PDB we are currently connected to, we can use this query:
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- ------------------------------ ---------- ----------
4 ORCL READ WRITE 2945187840
SQL>
What is interesting to point out, is that since we used an ALTER SESSION statement to switch to PDB1, the V$PDBS view only lists the PDB we are currently working in. If we want to get a complete list of PDBs within the container database, we need to go back to the root container database and run the same SQL statement.
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY 838860800
4 ORCL READ WRITE 2945187840
SQL>
In Oracle Database 12c, we can still find this information from V$INSTANCE. Breath a sigh of relief, for the moment! What if I connect to a container database and then issue an ALTER SESSION command to move into a different container, i.e. as PDB? Will I get the name of the PDB that I move into from the V$INSTANCE view or do I need to look somewhere else? Lets take a look.
Connect to the container database as usual and use the V$INSTANCE view to see where I’m at.
[oracle@r12c ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 11 10:52:33 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select instance_name, version, status, con_id from v$instance;
INSTANCE_NAME VERSION STATUS CON_ID
---------------- ----------------- ------------ ----------
cdb1 12.1.0.2.0 OPEN 0
Lets move into a PDB. I have created the ORCL.
SQL>alter session set container=ORCL;
Session altered.
Did I actually move containers? How can I tell? Instead of selecting INSTANCE_NAME from V$INSTANCE; we have two new SHOW commands that will provide us the information we are looking for. These command are quite simple (SHOW CON_ID & SHOW CON_NAME).
SQL> show con_id;
CON_ID
------------------------------
4
SQL> show con_name
CON_NAME
------------------------------
ORCL
This view provides similar information as V$INSTANCE does; yet is only specific to PDBs. If we wanted to find out the container_id, name, open_mode and size of the PDB we are currently connected to, we can use this query:
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- ------------------------------ ---------- ----------
4 ORCL READ WRITE 2945187840
SQL>
What is interesting to point out, is that since we used an ALTER SESSION statement to switch to PDB1, the V$PDBS view only lists the PDB we are currently working in. If we want to get a complete list of PDBs within the container database, we need to go back to the root container database and run the same SQL statement.
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id, name, open_mode, total_size from v$pdbs;
CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY 838860800
4 ORCL READ WRITE 2945187840
SQL>
No comments:
Post a Comment