以前的整理

哪些情况下的操作会使用到临时表空间

首先我们要明确下哪些情况下的操作会使用到临时表空间：

• 排序操作 比如select或dml(ddl)语句中包含order by之类;
• create index
• create pk constraint (其实这个跟create index类似，因为创建主键约束时默认会同时创建index)
• enable constraint操作
• create table语句

temp表空间的作用

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

• 1.临时表空间 是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)每个用户都有一个临时表空间。

• 2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理。

• 3.分配用户单独临时表空间,一般是针对 大型产品数据库,OLTP数据库,数据库仓库对于小型产品不需要单独制定临时表空间,使用默认临时表空间。

  正常情况下，一个sql执行之后，返回结果后系统会自动收回分配给这个用户的空间，以便可以把此部分空间再分配给其他用户。

临时表空间信息

（查询用户需要具备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 说明

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作
时提供临时的运算空间，当运算完成之后系统会自动清理。

当 oracle 里需要用到sort 的时候， PGA 中 sort_area_size 大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间。

正常来说，在完成 Select 语句、 create index 等一些使用 TEMP 表空间的排序操作后， Oracle 是会自动释放掉临时段的。

注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。 所以 Temp 表空间可能会越来越大。

排序是很耗资源的， Temp 表空间满了,关键是优化你的语句，尽量使排序减少才是上策.

Temp 表空间的操作

创建临时表空间

create temporary tablespace TEMP
tempfile '/oradata/cc/temp01.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  --默认的是local ，可以不加，另外一种是dictionary(数据字典管理)
  
 

  
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 的状态。

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
 

方法二、将临时数据文件设为自动扩展：

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

  
1
 

方法三、向临时表空间中添加数据文件：

 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 表空间指向其他的
名称，那么这些用户的信息就会失效。

所以，我们替换时，
要么创建一个临时的Temp 表空间中转一下，这样切换之后，我们的 temp 空间名称不变，

要么改变名称，同时更新相关用户的 default temp 表空间。

这里用中转的方法来测试.

创建中转临时表空间

Temp 表空间必须是 uniform 的， undo 必须是 autoallocate 的。默认情况 下 uniform 是 1M。

创建 SQL

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

  
1
  
2
  
3
 

修改 Temp2 为默认临时表空间

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
 

重置缺省临时表空间为新建的 temp 表空间

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 的操作，不影响其他的查询.

使用临时表空间的SHRINK方法缩小临时表空间的大小

–将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
 

更改系统的默认临时表空间

查询默认临时表空间

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
 

删除临时表空间

删除临时表空间的一个数据文件：

   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 数据文件重命名的步骤：

（ 1）将 tempfile offline
（ 2）在操作系统上重命名 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
 

此时数据库所有用户的默认临时表空间为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删除

当表空间组是数据库默认表空间时，最后一个成员删除报错：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表空间及数据文件

SQL>drop temporary tablespace temp1 including contents and datafiles;
  
 

  
1
 

文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。

原文链接：artisan.blog.csdn.net/article/details/50417564