Oracle-臨時表空間(組)解讀

以前的整理

哪些情況下的操作會使用到臨時表空間

首先我們要明確下哪些情況下的操作會使用到臨時表空間：

排序操作 比如select或dml(ddl)語句中包含order by之類;

create index

create pk constraint (其實這個跟create index類似，因為創(chuàng)建主鍵約束時默認會同時創(chuàng)建index)

enable constraint操作

create table語句

temp表空間的作用

temp表空間的作用，temp表空間主要是用作需要排序的操作。

1.臨時表空間 是用于在進行排序操作(如大型查詢,創(chuàng)建索引和聯(lián)合查詢期間存儲臨時數(shù)據(jù))每個用戶都有一個臨時表空間。

2.對于大型操作頻繁,(大型查詢,大型分類查詢,大型統(tǒng)計分析等),應指定單獨的臨時表空間,以方便管理。

3.分配用戶單獨臨時表空間,一般是針對 大型產品數(shù)據(jù)庫,OLTP數(shù)據(jù)庫,數(shù)據(jù)庫倉庫對于小型產品不需要單獨制定臨時表空間,使用默認臨時表空間。

正常情況下，一個sql執(zhí)行之后，返回結果后系統(tǒng)會自動收回分配給這個用戶的空間，以便可以把此部分空間再分配給其他用戶。

臨時表空間信息

（查詢用戶需要具備dba權限）

select * from dba_tablespaces where tablespace_name = 'TEMP'; --自動擴展字段autoextendsible(yes/no) select * from dba_temp_files; select * from v$tempfile ;

1

2

3

4

5

6

7

select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;

1

需要說明的是：

1、sql語句完成之后，需要檢查記錄的準確性。

2、盡量不要在視圖中進行order by ，這是一個非常耗費資源的操作。

Temporary Tablespacs 說明

臨時表空間主要用途是在數(shù)據(jù)庫進行排序運算、管理索引、訪問視圖等操作

時提供臨時的運算空間，當運算完成之后系統(tǒng)會自動清理。

當 oracle 里需要用到sort 的時候， PGA 中 sort_area_size 大小不夠時，將會把數(shù)據(jù)放入臨時表空間里進行排序，同時如果有異常情況的話，也會被放入臨時表空間。

正常來說，在完成 Select 語句、 create index 等一些使用 TEMP 表空間的排序操作后， oracle 是會自動釋放掉臨時段的。

注意這里的釋放，僅僅是將這些空間標記為空閑，并可重用，真正占用的磁盤空間并沒有釋放。 所以 Temp 表空間可能會越來越大。

排序是很耗資源的， Temp 表空間滿了,關鍵是優(yōu)化你的語句，盡量使排序減少才是上策.

Temp 表空間的操作

創(chuàng)建臨時表空間

create temporary tablespace TEMP tempfile '/oradata/cc/temp01.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; --默認的是local ，可以不加，另外一種是dictionary(數(shù)據(jù)字典管理)

1

2

3

4

5

6

You can use ALTER TABLESPACE to add a tempfile, take a tempfile offline, or bring a tempfile online, as illustrated in the following examples:

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/cc/temp02.dbf' SIZE 18M REUSE; SQL>ALTER TABLESPACE TEMPFILE TEMPFILE OFFLINE; SQL>ALTER TABLESPACE TEMPFILE TEMPFILE ONLINE;

1

2

3

4

不可以將 Temp 表空間 offline，但是可以將 tempfile offline。 V$TEMPFILE 顯示了 tempfile 的狀態(tài)。

The ALTER DATABASE statement can be used to alter tempfiles.

SQL>ALTER DATABASE TEMPFILE '/oradata/cc/temp02.dbf' OFFLINE; SQL>ALTER DATABASE TEMPFILE '/oradata/cc/temp02.dbf' ONLINE;

1

2

3

改變臨時表空間大小

alter database tempfile '/oradata/cc/temp01.dbf' resize 1024M;

1

擴展臨時表空間

方法一、增大臨時文件大小：

SQL> alter database tempfile ‘/oradata/cc/temp01.dbf’ resize 100m;

1

方法二、將臨時數(shù)據(jù)文件設為自動擴展：

SQL> alter database tempfile ‘/oradata/cc/temp01.dbf’ autoextend on next 5m maxsize unlimited;

1

方法三、向臨時表空間中添加數(shù)據(jù)文件：

SQL> alter tablespace temp add tempfile ‘/oradata/cc/temp02.dbf’ size 100m;

1

Temp 表空間過大的處理方法

11g的shrink方法更加簡單快捷，如果是11g的話，建議使用shrink.

替換 Temp 表空間

查看目前 Temp 表空間的信息

SQL> select name from v$tempfile; NAME -------------------------------------------------- /oradata/cc/temp01.dbf

1

2

3

4

5

6

SQL> select username,temporary_tablespace from dba_users; USERNAME TEMPORARY_TABLESPACE ------------------------ ------------------------------ SYS TEMP SYSTEM TEMP UCC TEMP CC TEMP .........

1

2

3

4

5

6

7

8

9

關于用戶這塊是要特別注意的，如果我們將默認的 Temp 表空間指向其他的

名稱，那么這些用戶的信息就會失效。

所以，我們替換時，

要么創(chuàng)建一個臨時的Temp 表空間中轉一下，這樣切換之后，我們的 temp 空間名稱不變，

要么改變名稱，同時更新相關用戶的 default temp 表空間。

這里用中轉的方法來測試.

Temp 表空間必須是 uniform 的， undo 必須是 autoallocate 的。默認情況 下 uniform 是 1M。

創(chuàng)建 SQL

SQL>CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata/cc/temp02.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

1

2

3

SQL>alter database default temporary tablespace temp2;

1

SQL>drop tablespace temp including contents and datafiles;

1

SQL>CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/cc/temp02.dbf' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

1

2

3

SQL>alter database default temporary tablespace temp;

1

SQL>drop tablespace temp2 including contents and datafiles;

1

SQL>alter user dave temporary tablespace temp;

1

2

對臨時表空間進行shrink

11g中針對臨時表空間過大的問題推出了SHRINK方法，使用這種方法可以非常便捷的自動化完成縮小臨時表空間或臨時文件的目的。

SQL> select * from dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE --------- --------- --------------- ---------- TEMP 1073741824 248512512 1069547520

1

2

3

4

5

官方說明dba_temp_free_space視圖是11g中新增加的視圖，使用這個視圖可以很方便的得到臨時表空間的使用情況。

當排序操作完成， 占用的空間并沒有釋放，僅僅是將它標記為空閑，并可重用,可以使用 shrink 來釋放沒有使用的空間。

shrink 是一個 online 的操作，不影響其他的查詢.

–將temp表空間收縮為20M

SQL>alter tablespace temp shrink space keep 20M;

1

或者

SQL> alter tablespace temp shrink space; Tablespace altered.

1

2

3

操作之前，查詢下大小，可以方便的比較出效果。

select * from dba_temp_free_space;

1

SHRINK同樣可以作用到具體的臨時文件

SQL> select file#,name,bytes/1024/1024 MB from v$tempfile; FILE# NAME MB ---------- ---------------------- ---------- 1 /oradata/cc/temp01.dbf 1024 SQL> alter tablespace temp shrink tempfile '/oradata/cc/temp01.dbf' keep 100m; Tablespace altered SQL> select file#,name,bytes/1024/1024 MB from v$tempfile; FILE# NAME MB ---------- -------------------- -------------- 1 /oradata/cc/temp01.dbf 100.992187

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

或者

SQL>ALTER TABLESPACE temp SHRINK TEMPFILE '/oradata/cc/temp01.dbf ';--不指定大小，自動將表空間的臨時文件縮小到最小可能的大小

1

2

3

更改系統(tǒng)的默認臨時表空間

查詢默認臨時表空間

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

1

修改默認臨時表空間

alter database default temporary tablespace temp02;

1

所有用戶的默認臨時表空間都將切換為新的臨時表空間：

select username,temporary_tablespace,default_tablespace from dba_users;

1

更改某一用戶的臨時表空間：

alter user scott temporary tablespace temp02;

1

刪除臨時表空間

刪除臨時表空間的一個數(shù)據(jù)文件：

alter database tempfile '/oradata/cc/temp01.dbf' drop;

1

刪除臨時表空間(徹底刪除)：

drop tablespace temp including contents and datafiles cascade constraints;

1

查看臨時表空間的使用情況

GV_$TEMP_SPACE_HEADER視圖必須在sys用戶下才能查詢 ,擁有DBA權限的用戶也不行，必須sys用戶

GV_$TEMP_SPACE_HEADER視圖記錄了臨時表空間的使用大小與未使用的大小

dba_temp_files視圖的bytes字段記錄的是臨時表空間的總大小

SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent" FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total WHERE temp_used.tablespace_name = temp_total.tablespace_name

1

2

3

4

5

6

7

8

9

10

11

SQL> conn sys/system as sysdba Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as sys@cc AS SYSDBA SQL> SELECT temp_used.tablespace_name, 2 total - used as "Free", 3 total as "Total", 4 round(nvl(total - used, 0) * 100 / total, 3) "Free percent" 5 FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used 6 FROM GV_$TEMP_SPACE_HEADER 7 GROUP BY tablespace_name) temp_used, 8 (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total 9 FROM dba_temp_files 10 GROUP BY tablespace_name) temp_total 11 WHERE temp_used.tablespace_name = temp_total.tablespace_name 12 ; TABLESPACE_NAME Free Total Free percent ------------------------------ ---------- ---------- TEMP 787 1024 76.855

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

tempfile 數(shù)據(jù)文件重命名的步驟：

（ 1）將 tempfile offline

（ 2）在操作系統(tǒng)上重命名 tempfile

（ 3）使用 alter database rename file 更新控制文件

臨時表空間組

概述

Oracle 10g之前，同一用戶的多個會話只可以使用同一個臨時表空間，因為在給定的時間只有一個臨時表空間默認給用戶，為了解決這個潛在的瓶頸，Oracle支持臨時表空間組即包含多個臨時表空間的集合。

臨時表空間組邏輯上就相當于一個臨時表空間。

操作

SQL>create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 10M; SQL>create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10M; SQL>create temporary tablespace temp3 tempfile '/u01/app/oracle/oradata/orcl/temp03.dbf' size 10M;

1

2

3

4

5

SQL>select name from v$tempfile; NAME ---------------------------------------------------- /u01/app/oracle/oradata/orcl/temp01.dbf /u01/app/oracle/oradata/orcl/temp02.dbf /u01/app/oracle/oradata/orcl/temp01.dbf

1

2

3

4

5

6

7

8

9

10

11

12

SQL>select tablespace_name from dba_tablespaces where contents='TEMPORARY'; TABLESPACE_NAME ------------------------------------------------------------- TEMP1 TEMP2 TEMP3

1

2

3

4

5

6

7

8

9

10

11

添加temp1，temp2，temp3到臨時表空間組tempgrp中

SQL>alter tablespace temp1 tablespace group tempgrp; SQL>alter tablespace temp2 tablespace group tempgrp; SQL>alter tablespace temp3 tablespace group tempgrp;

1

2

3

4

5

啟用臨時表空間組

SQL>alter database default temporary tablespace tempgrp;

1

SQL>select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME --------------------------------------------------------- TEMPGRP　　　　　　　　　　　　 TEMP1 TEMPGRP　　　　　　　　　　　　 TEMP2 TEMPGRP　　　　　　　　　　　　 TEMP3

1

2

3

4

5

6

7

8

9

10

11

此時數(shù)據(jù)庫所有用戶的默認臨時表空間為tempgrp

SQL>select username,defualt_tablespace,temporary_tablespace from dba_user where username='SCOTT'; USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------------------------------- SCOTT USERS TEMPGRP

1

2

3

4

5

6

7

刪除臨時表空間組

1.必須先刪除成員

SQL>alter tablespace temp1 tablespace group '';（表示刪除temp1）

1

SQL>select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ---------------------------------------------------------- TEMPGRP　　　　　　　　　　　　 TEMP2 TEMPGRP　　　　　　　　　　　　 TEMP3

1

2

3

4

5

6

7

8

9

10

同理將temp2，temp3刪除

當表空間組是數(shù)據(jù)庫默認表空間時，最后一個成員刪除報錯：ORA-10919：Defualt temporary tablespace group must be have at least one tablespace

SQL>alter database default temporary tablespace temp;

1

此時再刪除最后一個成員，臨時表空間組自動消失

SQL>select * from dba_tablespace_groups; no rows selected

1

2

3

刪除temp1表空間及數(shù)據(jù)文件

SQL>drop temporary tablespace temp1 including contents and datafiles;

1

Oracle 數(shù)據(jù)庫

版權聲明：本文內容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內刪除侵權內容。

版權聲明：本文內容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內刪除侵權內容。