Basic tasks on Pluggable Database (PDB) in Container Database (CDB)

Query to check whether the Database is Multitenant Database CDB:

1.[oracle@localhost ~]$ sqlplus sys/oracle@192.168.56.101:1521/cdb1 as sysdba

2.SQL> select NAME,DECODE(CDB,'YES','Multitenant Option Enabled','Regular 12c Database: ') "Multitenant Option ?", open_mode,con_id
       from v$database;

Query on currently connected instance:
============================
SQL> show con_name
SQL> show con_id

Information on Pluggable Databases (PDBs) in Container Database (CDB):
======================================================
SQL> select con_id, name, open_mode from v$pdbs;
SQL> col member format a45
SQL> select group#,member,con_id from v$logfile;
SQL> col name format a60
SQL> select name,con_id from v$controlfile;

SQL> set pagesize 40
SQL> col tablespace_name format A8
SQL> col file_id format 9999
SQL> col con_id format 999
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id ;

SQL> col NAME format A12
SQL> select FILE#, ts.name, ts.ts#, ts.con_id
from v$datafile d, v$tablespace ts
where d.ts#=ts.ts# and d.con_id=ts.con_id
order by 4,3;

SQL> col file_name format A50
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID
from cdb_temp_files;

SQL> col username format A22
SQL> select username, common, con_id from cdb_users
where username ='SYSTEM';

Connection Information for Container Database and Pluggable Databases:
====================================================
SQL> connect sys/oracle@192.168.56.101:1521/cdb1 as sysdba
SQL> select con_id, name, open_mode from v$pdbs;
SQL> connect sys/oracle@192.168.56.101:1521/pdb1 as sysdba
SQL> select con_id, name, open_mode from v$pdbs;
SQL> connect sys/oracle@192.168.56.101:1521/pdb2 as sysdba
SQL> alter pluggable database pdb2 open;
SQL> select con_id, name, open_mode from v$pdbs;

Information on Datafiles Of Container Database and Pluggable Databases:
=====================================================
SQL> set linesize 300
SQL> set lines 300 pages 100
SQL> col file_name format a60
SQL> connect sys/oracle@192.168.56.101:1521/cdb1 as sysdba
SQL> select file_name,tablespace_name from dba_data_files;
SQL> select file_name,tablespace_name from dba_temp_files;

Information on Datafiles Of Pluggable Databases:
=====================================
SQL> connect sys/oracle@192.168.56.101:1521/pdb1 as sysdba
SQL> select file_name,tablespace_name from dba_data_files;
SQL> select file_name,tablespace_name from dba_temp_files;

Information on Datafiles Of Pluggable Databases:
====================================
SQL> connect sys/oracle@192.168.56.101:1521/pdb2 as sysdba
SQL> select file_name,tablespace_name from dba_data_files;
SQL> select file_name,tablespace_name from dba_temp_files;

Explore the cdb instance and its background processes and the container database
==========================================================
[oracle@localhost admin]$ pgrep -lf cdb3

Explore the services : Using the lsnrctl utility determine which services are currently registered
====================================================================

[oracle@localhost admin]$ lsnrctl services

Use SYS_CONTEXT function to view the CON_NAME and CON_ID attributes of your session context.
=====================================================================
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [noncdb] ? cdb3
[oracle@localhost ~]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/cdb3 as sysdba
SQL> SELECT sys_context('userenv','CON_NAME') from dual;
SQL> SELECT sys_context('userenv','CON_ID') from dual;

View new family of views CDB_xxx
================================
SQL> col PDB_NAME format a8
SQL> col CON_ID format 999999
SQL> select PDB_ID, PDB_NAME, DBID, GUID, CON_ID from cdb_pdbs ;

View all data files of the CDB, including those of the root and all PDBs, with CDB_DATA_FILES view
========================================================================
SQL> set pagesize 40
SQL> col file_name format A50
SQL> col tablespace_name format A8
SQL> col file_id format 9999
SQL> col con_id format 999
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id ;

Use V$TABLESPACE and V$DATAFILE view
====================================
SQL> col NAME format A12
SQL> select FILE#, ts.name, ts.ts#, ts.con_id
from v$datafile d, v$tablespace ts
where d.ts#=ts.ts# and d.con_id=ts.con_id
order by 4,3;

List all common users in the CDB
========================
SQL> select distinct username from cdb_users where common ='YES';

List all local users in the CDB
======================
SQL> select distinct username from cdb_users where common ='NO';

List local users in root
================
SQL> select distinct username from dba_users where common ='NO';

Note: Notice that there is no local user in the root container because it is impossible to create any local user in the root.

List all roles and privileges in the CDB
============================
SQL> col role format A30
SQL> select role, common, con_id from cdb_roles;