27 - [Oracle 19C] V$DATAFILE vs cdb_data_files

V$ 로 조회하면 오픈되지 않은 DB까지 조회가 가능하고 cdb_ or dba_ 로 조회하면 open 된 DB만 조회가 가능하다.

 

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select name,open_mode,con_id from v$pdbs;

NAME		     OPEN_MODE	    CON_ID
-------------------- ---------- ----------
PDB$SEED	     READ ONLY		 2
ORCLPDB 	     READ WRITE 	 3

SQL> select file_name,file_id,tablespace_name,con_id
from cdb_data_files;
  2
FILE_NAME					      FILE_ID TABLESPACE_NAME	       CON_ID
-------------------------------------------------- ---------- -------------------- ----------
/u01/app/oracle/oradata/ORCL/users01.dbf		    7 USERS			    1
/u01/app/oracle/oradata/ORCL/undotbs01.dbf		    4 UNDOTBS1			    1
/u01/app/oracle/oradata/ORCL/system01.dbf		    1 SYSTEM			    1
/u01/app/oracle/oradata/ORCL/sysaux01.dbf		    3 SYSAUX			    1
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf	    9 SYSTEM			    3
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf	   10 SYSAUX			    3
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf	   11 UNDOTBS1			    3
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf	   12 USERS			    3

8 rows selected.


--Some V$ views are available even when the database is not fully open and running.

SQL> select file#,name, ts#,con_id
from V$DATAFILE
order by con_id;

     FILE# NAME 						     TS#     CON_ID
---------- -------------------------------------------------- ---------- ----------
	 1 /u01/app/oracle/oradata/ORCL/system01.dbf		       0	  1
	 3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf		       1	  1
	 4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf		       2	  1
	 7 /u01/app/oracle/oradata/ORCL/users01.dbf		       4	  1
	 6 /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf	       1	  2
	 8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf	       2	  2
	 5 /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf	       0	  2
	12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf	       5	  3
	 9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf	       0	  3
	10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf	       1	  3
	11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf	       2	  3

11 rows selected.

 

 

V$DATAFILE vs cdb_data_files 차이는 마지막 부분에 명확히 나타납니다.

 

 

SQL> alter pluggable database orclpdb close;

Pluggable database altered.

SQL>

SQL> select file#,name, ts#,con_id from V$DATAFILE order by con_id;

     FILE# NAME 						     TS#     CON_ID
---------- -------------------------------------------------- ---------- ----------
	 1 /u01/app/oracle/oradata/ORCL/system01.dbf		       0	  1
	 3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf		       1	  1
	 4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf		       2	  1
	 7 /u01/app/oracle/oradata/ORCL/users01.dbf		       4	  1
	 6 /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf	       1	  2
	 8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf	       2	  2
	 5 /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf	       0	  2
	12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf	       5	  3
	 9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf	       0	  3
	10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf	       1	  3
	11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf	       2	  3

11 rows selected.

SQL>
SQL> select file_name,file_id,tablespace_name,con_id from cdb_data_files;

FILE_NAME					      FILE_ID TABLESPACE_NAME			 CON_ID
-------------------------------------------------- ---------- ------------------------------ ----------
/u01/app/oracle/oradata/ORCL/users01.dbf		    7 USERS				      1
/u01/app/oracle/oradata/ORCL/undotbs01.dbf		    4 UNDOTBS1				      1
/u01/app/oracle/oradata/ORCL/system01.dbf		    1 SYSTEM				      1
/u01/app/oracle/oradata/ORCL/sysaux01.dbf		    3 SYSAUX				      1

SQL>

 

 

SQL> alter session set container=orclpdb;

Session altered.

SQL>
SQL> select file#,name, ts#,con_id from V$DATAFILE order by con_id;

     FILE# NAME 						     TS#     CON_ID
---------- -------------------------------------------------- ---------- ----------
	 9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf	       0	  3
	12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf	       5	  3
	11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf	       2	  3
	10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf	       1	  3

SQL>
SQL> select file_name,file_id,tablespace_name from dba_data_files;
select file_name,file_id,tablespace_name from dba_data_files
                                              *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only


SQL>
SQL> select file_name,file_id,tablespace_name,con_id from cdb_data_files;
select file_name,file_id,tablespace_name,con_id from cdb_data_files
                                                     *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only


SQL>