24 - [Oracle 19C] To query temps files

A tempfile is a file that is part of an Oracle database. 
Tempfiles are used with TEMPORARY TABLESPACES

Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for 
hash joins in SQL. For SQL with millions of rows returned, the sort operation is too large for the RAM 
area and must occur on disk.  
The temporary tablespace is where this takes place

 

 

SQL> show con_name

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

SQL> col name for a20
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> col tablespace_name for a15
SQL> select file_name,file_id,tablespace_name,con_id
from cdb_temp_files  2  ;

FILE_NAME					      FILE_ID TABLESPACE_NAME	  CON_ID
-------------------------------------------------- ---------- --------------- ----------
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf 	    3 TEMP		       3
/u01/app/oracle/oradata/ORCL/temp01.dbf 		    1 TEMP		       1

SQL>

 

 

  dba_ 테이블을 통해서 자신의 컨테이너에 있는 정보만 조회

 

SQL> select file_name,file_id,tablespace_name
from dba_temp_files;
  2
FILE_NAME					      FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ---------------
/u01/app/oracle/oradata/ORCL/temp01.dbf 		    1 TEMP

SQL>

 

 

  컨테이너를 변경하면 dba_ 테이블을 통해서 자신의 컨테이너에 있는 정보만 조회 가능함

 

SQL> alter session set container=orclpdb;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB

SQL> select file_name,file_id,tablespace_name
from dba_temp_files;
  2
FILE_NAME					      FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ---------------
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf 	    3 TEMP

SQL>

 

 

  컨테이너를 변경했으므로 cdb_ 테이블을 조회해도 자신의 컨테이너에 있는 정보만 조회 가능함

 

SQL> select file_name,file_id,tablespace_name,con_id
from cdb_temp_files;
  2
FILE_NAME					      FILE_ID TABLESPACE_NAME	  CON_ID
-------------------------------------------------- ---------- --------------- ----------
/u01/app/oracle/oradata/ORCL/orclpdb/temp01.dbf 	    3 TEMP		       3

SQL>