Though it is well documented in the Oracle 12c documentation, just to demo I wrote this post.
http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn009.htm#REFRN009
CDB_* views are container data objects. When a user connected to the root queries a CDB_* view, the query results will depend on the
The CDB_* views are owned by
By default, a user connected to the root will only see data pertaining to the root.
In a PDB, the CDB_* views only show objects visible through a corresponding DBA_* view.
In addition to all the columns found in a given DBA_* view, the corresponding CDB_* view also contains the
Data returned by these views depends on whether a given PDB is open at the time the query is issued.
In particular, in an Oracle RAC environment, data returned by these view may vary according to the instance to which a session is connected.
SQL> set time on echo on
22:27:54 SQL> select name,cdb from v$database;
NAME CDB
--------- ---
ORCL12C YES
22:28:14 SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
22:28:20 SQL> set pages 20
22:28:54 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF
10 rows selected.
22:29:03 SQL> select file_name from cdb_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF
6 rows selected.
22:29:24 SQL> select con_id,name,open_mode from v$pdbs order by 1;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDBORCL MOUNTED
22:31:08 SQL> alter pluggable database PDBORCL open read write;
Pluggable database altered.
22:31:25 SQL> select con_id,name,open_mode from v$pdbs order by 1;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDBORCL READ WRITE
22:31:32 SQL> select file_name from cdb_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF
10 rows selected.
22:31:45 SQL> spool off
Please provide your valuable suggestion/ corrections to improve
Thanks , Happy reading...
http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn009.htm#REFRN009
CDB_* Views
For every DBA_* view, a CDB_* view is defined. In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables, tablespaces, users, privileges, parameters, and so on contained in the root and in pluggable databases (PDBs).CDB_* views are container data objects. When a user connected to the root queries a CDB_* view, the query results will depend on the
CONTAINER_DATA
attribute for users for the view. The CONTAINER_DATA
clause of the SQL ALTER USER
statement is used to set and modify users' CONTAINER_DATA
attribute.The CDB_* views are owned by
SYS
, regardless of who owns the underlying DBA_* view.By default, a user connected to the root will only see data pertaining to the root.
In a PDB, the CDB_* views only show objects visible through a corresponding DBA_* view.
In addition to all the columns found in a given DBA_* view, the corresponding CDB_* view also contains the
CON_ID
column, which identifies a container whose data a given CDB_* row represents. In a non-CDB, the value of a CON_ID
column will be 0
.Data returned by these views depends on whether a given PDB is open at the time the query is issued.
In particular, in an Oracle RAC environment, data returned by these view may vary according to the instance to which a session is connected.
List of action performed
- Logged into CDB
- Queried both cbd_data_files and v$datafile , CDB_ view has only 7 rows
- Queried V$PDBS to know status off PDBs
- One PDB is in Mount state
- Opened that PDB in read write mode.
- Then we could see all the files.
SQL> set time on echo on
22:27:54 SQL> select name,cdb from v$database;
NAME CDB
--------- ---
ORCL12C YES
22:28:14 SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
22:28:20 SQL> set pages 20
22:28:54 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF
10 rows selected.
22:29:03 SQL> select file_name from cdb_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF
6 rows selected.
22:29:24 SQL> select con_id,name,open_mode from v$pdbs order by 1;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDBORCL MOUNTED
22:31:08 SQL> alter pluggable database PDBORCL open read write;
Pluggable database altered.
22:31:25 SQL> select con_id,name,open_mode from v$pdbs order by 1;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDBORCL READ WRITE
22:31:32 SQL> select file_name from cdb_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBSEED\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\UNDOTBS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSTEM01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SYSAUX01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\SAMPLE_SCHEMA_USERS01.DBF
D:\APP\TWELVEC\ORADATA\ORCL12C\PDBORCL\EXAMPLE01.DBF
10 rows selected.
22:31:45 SQL> spool off
Please provide your valuable suggestion/ corrections to improve
Thanks , Happy reading...
Masha Allah very Good and easy explanation with good examples
ReplyDelete