oracle的臨時表空間
oracle資料庫的臨時表空間的主要用途是在資料庫進行排序運算,管理索引,訪問檢視等操作時提供臨時的運算空間,當運算完成之後系統會自動清理。當oracle資料庫要用到排序操作sort,pga中sort_area_size大小不夠時,將會把這些資料放入臨時表空間進行排序同時如果有異常情況也會被放入臨時表空間。正常來說,在完成select語句,creat index等一些使用temp表空間的排序操作之後,oracle會自動釋放臨時段的,這裡說的釋放,僅僅是將這些空間標記為空閒,並可重用,真正佔用的磁碟空間並沒有釋放,所以temp表空間可能會越來越大。
透過重啟資料庫可以釋放臨時表空間,如果不能重啟例項,而一直保持有問題的sql語句執行,temp表空間會一直增長,知道消耗完硬碟空間為止。
在臨時表空間的磁碟分配上,oracle採用的是貪心演算法,如果上次磁碟空間消耗達到1GB,那麼臨時表空間就是1GB,也就是說當前臨時表空間的大小是歷史上使用臨時表空間的最大值。
臨時表空間主要用於:
(1)create index或rebuild index
(2)order by 或group by
(3)distinct操作
(4)union或intersect或minus
(5)sort-merge joins
(6)analyze
排序時很耗資源的,不能無限增大temp表空間,關鍵是最佳化你的語句,儘量使排序減少才是上策。
檢視當前資料庫臨時表空間和臨時資料檔案使用率:
SYS@orcl 07-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
2 total-used as "free|MB",
3 total as "total|MB"
4 from
5 (select tablespace_name,sum(bytes_used)/1024/1024 used
6 from gv_$temp_space_header
7 group by tablespace_name) temp_used,
8 (select file_name,tablespace_name,sum(bytes)/1024/1024 total
9 from dba_temp_files
10 group by tablespace_name,file_name) temp_total
11 where temp_used.tablespace_name=temp_total.tablespace_name;
TABLESPACE_NAME FILE_NAME free|MB total|MB
--------------- ----------------------------------- ---------- ----------
TEMP /u01/app/oracle/oradata/orcl/temp01 12 20
.dbf
檢視當前資料庫預設的臨時表空間:
SYS@orcl 07-SEP-14>select property_name,property_value
2 from database_properties
3 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
改變臨時表空間的大小:
SYS@orcl 07-SEP-14>alter database tempfile
2 '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 25m;
Database altered.
SYS@orcl 07-SEP-14>select tablespace_name,file_name,bytes/1024/1024||'MB'
2 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024||'MB'
--------------- -------------------------------------------------- ------------------------------------------
TEMP /u01/app/oracle/oradata/orcl/temp01.dbf 25MB
將臨時表空間設定成自動擴充套件:
SYS@orcl 07-SEP-14>alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;
Database altered.
向臨時表空間增加資料檔案:
SYS@orcl 07-SEP-14>alter tablespace temp
2 add tempfile '/u01/app/oracle/oradata/orcl/temp01a.dbf' size 10m;
Tablespace altered.
YS@orcl 07-SEP-14>select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
TEMP /u01/app/oracle/oradata/orcl/temp01a.dbf
TEMP /u01/app/oracle/oradata/orcl/temp01.dbf
檢視臨時檔案的大小,自動擴充套件性:
SYS@orcl 07-SEP-14>select file_name,tablespace_name,bytes/1024/1024||'MB',
2 autoextensible from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ---------------
BYTES/1024/1024||'MB' AUT
------------------------------------------ ---
/u01/app/oracle/oradata/orcl/temp01a.dbf TEMP
10MB NO
/u01/app/oracle/oradata/orcl/temp01.dbf TEMP
25MB YES
oracle資料庫不能drop預設的臨時表空間
SYS@orcl 07-SEP-14>drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
建立新的臨時表空間
SYS@orcl 07-SEP-14>create temporary tablespace temp2
2 tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 20m
3 reuse autoextend on next 1m maxsize unlimited;
Tablespace created.
改變預設的臨時表空間為temp2
SYS@orcl 07-SEP-14>alter database default temporary tablespace temp2;
Database altered.
檢視預設的臨時表空間:
SYS@orcl 07-SEP-14>select property_name,property_value
2 from database_properties
3 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2
SYS@orcl 07-SEP-14>drop tablespace temp;
Tablespace dropped.
SYS@orcl 07-SEP-14>select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/orcl/temp02.dbf TEMP2
在oracle資料庫中,我們可以建立多個臨時表空間,並把它們組成一個臨時表空間組,這樣應用資料用於排序時可以使用組裡的多個臨時表空間,一個臨時表空間組至少有一個臨時表空間,其最大個數是沒有限制的。注意,組的名字不能和其中某個表空間的名字相同。
臨時表空間組是在建立臨時表空間時透過指定group字句建立的,如果刪除組中所有的臨時表空間,那麼這個組也將會消失。
我們可以把一個表空間從 一個組轉移到另一個組,也可以從一個組中刪除臨時表空間或者新增臨時表空間。
使用臨時表空間組有如下優點:
1.可以同時指定多個臨時表空間,避免由於臨時表空間不足造成的磁碟排序問題。
2.當一個使用者同時有多個會話時,可以使得它們使用不同的臨時表空間。
3.並行操作中,不同的從屬程式可以使用不同的臨時表空間。
SYS@orcl 07-SEP-14>create temporary tablespace tempts1 tempfile
2 '/u01/app/oracle/oradata/orcl/tempts01.dbf' size 5m tablespace group group1;
Tablespace created.
SYS@orcl 07-SEP-14>create temporary tablespace tempts2 tempfile
2 '/u01/app/oracle/oradata/orcl/tempts02.dbf' size 5m tablespace group group2;
Tablespace created.
檢視臨時表空間組dba_tablespace_groups檢視:
SYS@orcl 07-SEP-14>select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
--------------- ---------------
GROUP1 TEMPTS1
GROUP2 TEMPTS2
在臨時表空間中進行shrink(oracle11g新功能)
查詢當前臨時表空間和臨時資料檔案:
SYS@orcl 08-SEP-14>col tablespace_name for a15
SYS@orcl 08-SEP-14>col file_name for a35
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
2 total-used as "free|MB",
3 total as "total|MB"
4 from
5 (select tablespace_name,sum(bytes_used)/1024/1024 used
6 from gv_$temp_space_header
7 group by tablespace_name) temp_used,
8 (select file_name,tablespace_name,sum(bytes)/1024/1024 total
9 from dba_temp_files
10 group by tablespace_name,file_name) temp_total
11 where temp_used.tablespace_name=temp_total.tablespace_name;
TABLESPACE_NAME FILE_NAME free|MB total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2 /u01/app/oracle/oradata/orcl/tempts 4 5
02.dbf
TEMPTS1 /u01/app/oracle/oradata/orcl/tempts 4 5
01.dbf
TEMP2 /u01/app/oracle/oradata/orcl/temp02 18 20
.dbf
將temp2表空間收縮為15m:
SYS@orcl 08-SEP-14>alter tablespace temp2 shrink space keep 15m;
Tablespace altered.
再次檢視臨時表空間和臨時資料檔案:
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
2 total-used as "free|MB",
3 total as "total|MB"
4 from
5 (select tablespace_name,sum(bytes_used)/1024/1024 used
6 from gv_$temp_space_header
7 group by tablespace_name) temp_used,
8 (select file_name,tablespace_name,sum(bytes)/1024/1024 total
9 from dba_temp_files
10 group by tablespace_name,file_name) temp_total
11 where temp_used.tablespace_name=temp_total.tablespace_name;
TABLESPACE_NAME FILE_NAME free|MB total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2 /u01/app/oracle/oradata/orcl/tempts 4 5
02.dbf
TEMPTS1 /u01/app/oracle/oradata/orcl/tempts 4 5
01.dbf
TEMP2 /u01/app/oracle/oradata/orcl/temp02 15 16
.dbf
temp2的資料檔案縮小為15m。
[oracle@localhost orcl]$ ll
total 156872
-rw-r----- 1 oracle oinstall 52429312 Sep 8 00:44 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Sep 8 00:44 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep 8 01:02 redo03.log
-rw-r----- 1 oracle oinstall 16785408 Sep 8 00:55 temp02.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 7 21:27 tempts01.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 7 21:28 tempts02.dbf
oracle的臨時表不佔用任何表空間,而且不同的session之間互相看不到對方的資料。
在會話結束後表中的資料會自動清空,如果選了delete rows,則在提交的時候即清空資料,preserve則一直到會話結束。
commit後保留臨時資料:
SYS@orcl 08-SEP-14>create global temporary table tablename(
2 col1 varchar2(10),
3 col2 number
4 )on commit preserve rows;
Table created.
commit後不保留臨時資料:
SYS@orcl 08-SEP-14>create global temporary table tablename1(
2 col1 varchar2(10),
3 col2 number
4 )on commit delete rows;
Table created.
透過重啟資料庫可以釋放臨時表空間,如果不能重啟例項,而一直保持有問題的sql語句執行,temp表空間會一直增長,知道消耗完硬碟空間為止。
在臨時表空間的磁碟分配上,oracle採用的是貪心演算法,如果上次磁碟空間消耗達到1GB,那麼臨時表空間就是1GB,也就是說當前臨時表空間的大小是歷史上使用臨時表空間的最大值。
臨時表空間主要用於:
(1)create index或rebuild index
(2)order by 或group by
(3)distinct操作
(4)union或intersect或minus
(5)sort-merge joins
(6)analyze
排序時很耗資源的,不能無限增大temp表空間,關鍵是最佳化你的語句,儘量使排序減少才是上策。
檢視當前資料庫臨時表空間和臨時資料檔案使用率:
SYS@orcl 07-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
2 total-used as "free|MB",
3 total as "total|MB"
4 from
5 (select tablespace_name,sum(bytes_used)/1024/1024 used
6 from gv_$temp_space_header
7 group by tablespace_name) temp_used,
8 (select file_name,tablespace_name,sum(bytes)/1024/1024 total
9 from dba_temp_files
10 group by tablespace_name,file_name) temp_total
11 where temp_used.tablespace_name=temp_total.tablespace_name;
TABLESPACE_NAME FILE_NAME free|MB total|MB
--------------- ----------------------------------- ---------- ----------
TEMP /u01/app/oracle/oradata/orcl/temp01 12 20
.dbf
檢視當前資料庫預設的臨時表空間:
SYS@orcl 07-SEP-14>select property_name,property_value
2 from database_properties
3 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
改變臨時表空間的大小:
SYS@orcl 07-SEP-14>alter database tempfile
2 '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 25m;
Database altered.
SYS@orcl 07-SEP-14>select tablespace_name,file_name,bytes/1024/1024||'MB'
2 from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/1024/1024||'MB'
--------------- -------------------------------------------------- ------------------------------------------
TEMP /u01/app/oracle/oradata/orcl/temp01.dbf 25MB
將臨時表空間設定成自動擴充套件:
SYS@orcl 07-SEP-14>alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;
Database altered.
向臨時表空間增加資料檔案:
SYS@orcl 07-SEP-14>alter tablespace temp
2 add tempfile '/u01/app/oracle/oradata/orcl/temp01a.dbf' size 10m;
Tablespace altered.
YS@orcl 07-SEP-14>select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
TEMP /u01/app/oracle/oradata/orcl/temp01a.dbf
TEMP /u01/app/oracle/oradata/orcl/temp01.dbf
檢視臨時檔案的大小,自動擴充套件性:
SYS@orcl 07-SEP-14>select file_name,tablespace_name,bytes/1024/1024||'MB',
2 autoextensible from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ---------------
BYTES/1024/1024||'MB' AUT
------------------------------------------ ---
/u01/app/oracle/oradata/orcl/temp01a.dbf TEMP
10MB NO
/u01/app/oracle/oradata/orcl/temp01.dbf TEMP
25MB YES
oracle資料庫不能drop預設的臨時表空間
SYS@orcl 07-SEP-14>drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
建立新的臨時表空間
SYS@orcl 07-SEP-14>create temporary tablespace temp2
2 tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 20m
3 reuse autoextend on next 1m maxsize unlimited;
Tablespace created.
改變預設的臨時表空間為temp2
SYS@orcl 07-SEP-14>alter database default temporary tablespace temp2;
Database altered.
檢視預設的臨時表空間:
SYS@orcl 07-SEP-14>select property_name,property_value
2 from database_properties
3 where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2
SYS@orcl 07-SEP-14>drop tablespace temp;
Tablespace dropped.
SYS@orcl 07-SEP-14>select file_name,tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/orcl/temp02.dbf TEMP2
在oracle資料庫中,我們可以建立多個臨時表空間,並把它們組成一個臨時表空間組,這樣應用資料用於排序時可以使用組裡的多個臨時表空間,一個臨時表空間組至少有一個臨時表空間,其最大個數是沒有限制的。注意,組的名字不能和其中某個表空間的名字相同。
臨時表空間組是在建立臨時表空間時透過指定group字句建立的,如果刪除組中所有的臨時表空間,那麼這個組也將會消失。
我們可以把一個表空間從 一個組轉移到另一個組,也可以從一個組中刪除臨時表空間或者新增臨時表空間。
使用臨時表空間組有如下優點:
1.可以同時指定多個臨時表空間,避免由於臨時表空間不足造成的磁碟排序問題。
2.當一個使用者同時有多個會話時,可以使得它們使用不同的臨時表空間。
3.並行操作中,不同的從屬程式可以使用不同的臨時表空間。
SYS@orcl 07-SEP-14>create temporary tablespace tempts1 tempfile
2 '/u01/app/oracle/oradata/orcl/tempts01.dbf' size 5m tablespace group group1;
Tablespace created.
SYS@orcl 07-SEP-14>create temporary tablespace tempts2 tempfile
2 '/u01/app/oracle/oradata/orcl/tempts02.dbf' size 5m tablespace group group2;
Tablespace created.
檢視臨時表空間組dba_tablespace_groups檢視:
SYS@orcl 07-SEP-14>select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
--------------- ---------------
GROUP1 TEMPTS1
GROUP2 TEMPTS2
在臨時表空間中進行shrink(oracle11g新功能)
查詢當前臨時表空間和臨時資料檔案:
SYS@orcl 08-SEP-14>col tablespace_name for a15
SYS@orcl 08-SEP-14>col file_name for a35
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
2 total-used as "free|MB",
3 total as "total|MB"
4 from
5 (select tablespace_name,sum(bytes_used)/1024/1024 used
6 from gv_$temp_space_header
7 group by tablespace_name) temp_used,
8 (select file_name,tablespace_name,sum(bytes)/1024/1024 total
9 from dba_temp_files
10 group by tablespace_name,file_name) temp_total
11 where temp_used.tablespace_name=temp_total.tablespace_name;
TABLESPACE_NAME FILE_NAME free|MB total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2 /u01/app/oracle/oradata/orcl/tempts 4 5
02.dbf
TEMPTS1 /u01/app/oracle/oradata/orcl/tempts 4 5
01.dbf
TEMP2 /u01/app/oracle/oradata/orcl/temp02 18 20
.dbf
將temp2表空間收縮為15m:
SYS@orcl 08-SEP-14>alter tablespace temp2 shrink space keep 15m;
Tablespace altered.
再次檢視臨時表空間和臨時資料檔案:
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
2 total-used as "free|MB",
3 total as "total|MB"
4 from
5 (select tablespace_name,sum(bytes_used)/1024/1024 used
6 from gv_$temp_space_header
7 group by tablespace_name) temp_used,
8 (select file_name,tablespace_name,sum(bytes)/1024/1024 total
9 from dba_temp_files
10 group by tablespace_name,file_name) temp_total
11 where temp_used.tablespace_name=temp_total.tablespace_name;
TABLESPACE_NAME FILE_NAME free|MB total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2 /u01/app/oracle/oradata/orcl/tempts 4 5
02.dbf
TEMPTS1 /u01/app/oracle/oradata/orcl/tempts 4 5
01.dbf
TEMP2 /u01/app/oracle/oradata/orcl/temp02 15 16
.dbf
temp2的資料檔案縮小為15m。
[oracle@localhost orcl]$ ll
total 156872
-rw-r----- 1 oracle oinstall 52429312 Sep 8 00:44 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Sep 8 00:44 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep 8 01:02 redo03.log
-rw-r----- 1 oracle oinstall 16785408 Sep 8 00:55 temp02.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 7 21:27 tempts01.dbf
-rw-r----- 1 oracle oinstall 5251072 Sep 7 21:28 tempts02.dbf
oracle的臨時表不佔用任何表空間,而且不同的session之間互相看不到對方的資料。
在會話結束後表中的資料會自動清空,如果選了delete rows,則在提交的時候即清空資料,preserve則一直到會話結束。
commit後保留臨時資料:
SYS@orcl 08-SEP-14>create global temporary table tablename(
2 col1 varchar2(10),
3 col2 number
4 )on commit preserve rows;
Table created.
commit後不保留臨時資料:
SYS@orcl 08-SEP-14>create global temporary table tablename1(
2 col1 varchar2(10),
3 col2 number
4 )on commit delete rows;
Table created.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29800581/viewspace-1266255/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle臨時表空間相關Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法!Oracle
- oracle 臨時表空間的增刪改查Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 檢視oracle臨時表空間佔用率的檢視Oracle
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- mysql臨時表,臨時表空間,ibtmp1表空間暴增原因初探MySql
- oracle建立使用者,表空間,臨時表空間,分配許可權步驟詳解Oracle
- 4.2.1.8規劃臨時表空間
- 刪除臨時表空間組
- MySQL InnoDB臨時表空間配置MySql
- 消除臨時表空間暴漲的方法
- 2.5.7 建立預設臨時表空間
- Oracle 12C RMAN備份佔用大量臨時表空間Oracle
- MySQL 5.7 新特性 共享臨時表空間及臨時表改進MySql
- 臨時表空間和回滾表空間使用率查詢
- SQLServer如何釋放tempdb臨時表空間SQLServer
- Oracle 查詢佔用臨時表空間大的歷史會話和SQLOracle會話SQL
- 臨時表空間被佔滿的原因查詢
- Oracle表空間Oracle
- oracle 表空間Oracle
- oracle 臨時表的使用Oracle
- oracle表空間的整理Oracle
- MYSQL造資料佔用臨時表空間MySql
- 增加oracle表空間Oracle
- oracle temp 表空間Oracle
- 臨時表空間ORA-1652問題解決
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- ORACLE臨時表總結Oracle
- Oracle 批量建表空間Oracle
- Oracle清理SYSAUX表空間OracleUX
- 臨時表空間使用率過高的解決辦法
- Oracle的表空間quota詳解Oracle
- Oracle OCP(47):表空間的建立Oracle
- 查詢過去一段時間內某條sql使用的臨時表空間大小SQL
- Oracle 臨時表 OracleDataAdapter 批次更新OracleAPT
- 12C關於CDB、PDB 臨時temp表空間的總結
- Oracle Temp 表空間切換Oracle