템프 파일

용도

• 대량의 정렬이나 해시 작업의 임시 결과 저장
• 글로벌 템프로리 테이블 저장
• 메모리에 담기에 너무 큰 결과집합 저장
• 영구 보존되는 세그먼트를 저장할 수는 없음

특징

• 템프 파일에 저장되는 데이터는 REDO LOG 기록되지 않음
• Rollback 을 대비하여 UNDO Record 는 생성 함(자세한 내용은 9장에서)
• Initial Size 를 할당하더라도 실제 사용되기 전까지 OS Filesystem 의 공간 점유 하지 않음

[RECO3]rac2:/app/oracle/ext> df -k .
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg_rac2-lv_root
                      51606140  22802376  26182324  47% /
[RECO3]rac2:/app/oracle/ext> sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 11 07:24:58 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create temporary tablespace tmp2 tempfile '/app/oracle/temp_test.dbf' size 2G;

Tablespace created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

[RECO3]rac2:/app/oracle/ext> ls -l /app/oracle/temp_test.dbf
-rw-r----- 1 oracle oinstall 2147491840 Oct 11 07:25 /app/oracle/temp_test.dbf
[RECO3]rac2:/app/oracle/ext> df -k .
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg_rac2-lv_root
                      51606140  22803420  26181280  47% /
[RECO3]rac2:/app/oracle/ext>


[RECO3]rac2:/app/oracle/ext> sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 11 07:26:22 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create tablespace test datafile '/app/oracle/data_test.dbf' size 2G;

Tablespace created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

[RECO3]rac2:/app/oracle/ext> ls -l /app/oracle/data_test.dbf
-rw-r----- 1 oracle oinstall 2147491840 Oct 11 07:27 /app/oracle/data_test.dbf

[RECO3]rac2:/app/oracle/ext> df -k .
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg_rac2-lv_root
                      51606140  24900680  24084020  51% /