oracle清除temp表空间,Oracle 11G清理临时表空间

一. 临时表空间收缩

1.1 说明

关于Oracle 的临时表空间，之前有整理过一篇Blog：

以下操作会占用大量的temporary：

1、用户执行imp/exp 导入导出操作时，会使用大量的temporary段

2、用户在rebuild index时

3、执行create table ...... as 语句时

4、移动用户下的数据到别的表空间时

大量的排序操作可能会导致临时表空间大量增长。为了提高性能，对排序区进行物理分配后，将在内存中管理它们以避免以后的物理回收。结果，磁盘中包含一个巨大的临时文件，直到将其删除。一种可能的解决方法是：使用较小的文件创建新的临时表空间，并将这个新的表空间设置为用户的默认临时表空间，然后删除旧的表空间。但是，这有一个缺点，即过程要求删除旧的临时表空间时不能存在活动的排序操作。

从Oracle Database11g 版本1 开始，可使用ALTER TABLESPACESHRINK SPACE 命令收缩临时表空间，也可以使用ALTER TABLESPACE SHRINKTEMPFILE 命令收缩临时文件。对于这两个命令，可以指定可选的KEEP 子句，该子句定义了表空间/临时文件可收缩到的下限。

如果忽略KEEP子句，则只要满足其它存储属性，就会尽可能尝试收缩表空间/临时文件(所有当前使用的区的总空间)。此操作需联机执行。但是，如果所分配的当前使用的一些区超出了收缩估计值，系统将等待这些区被释放以完成收缩操作。

注：

ALTER DATABASETEMPFILE RESIZE 命令通常会因ORA-03297 而失败，因为临时文件包含的已用数据超过了所需的RESIZE 值。

与ALTER TABLESPACE SHRINK相反，ALTER DATABASE 命令不会在排序区分配后尝试取消分配。

在Oracle 11g 以前，Temp 表空间使用以后，虽然可以释放，但是表空间的使用量显示还是100%，可以使用如下脚本查看临时表空间每个数据文件实际使用量：

set pagesize 50

col tablespace_name for a20

col "Tempfile name" for a42

set linesize 300

Select f.tablespace_name,

d.file_name "Tempfile name",

round((f.bytes_free + f.bytes_used) / 1024 /1024, 2) "total MB",

round(((f.bytes_free + f.bytes_used) -nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,

round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)"Used MB",

round((round(nvl(p.bytes_used, 0)/ 1024 /1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) as"Used_Rate(%)"

from SYS.V_$TEMP_SPACE_HEADER f,DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p

where f.tablespace_name(+) = d.tablespace_name

and f.file_id(+) = d.file_id

and p.file_id(+) =d.file_id;

1.2 DBA_TEMP_FREE_SPACE视图

该字典视图是在Oracle 11g新增加的视图，用来查看表空间级别的临时空间使用率信息。此信息是从各种现有视图中导出的。

(1)  列出临时空间使用率信息

(2)  临时表空间使用率中心点

列名说明

TABLESPACE_NAME  表空间的名称

TABLESPACE_SIZE表空间的总大小(以字节为单位)

ALLOCATED_SPACE  已分配的总空间(以字节为单位)，包括当前已分配的且正在使用中的空间以及当前已分配的且可重用的空间

FREE_SPACE  可用的总空间(以字节为单位)，包括当前已分配的、可重用的以及当前未分配的空间

1.3 创建临时表的表空间选项

从Oracle Database11g 版本1 开始，可以在创建全局临时表时指定TABLESPACE子句。

如果没有指定表空间，将在默认的临时表空间中创建全局临时表。此外，还会在与临时表相同的临时表空间中创建在临时表中创建的索引。

注：

可以在DBA_TABLES 中查找用于存储全局临时表的表空间。

如：

CREATE TEMPORARY TABLESPACE temp

TEMPFILE 'tbs_temp.dbf' SIZE 600m REUSEAUTOEXTEND ON MAXSIZE

UNLIMITED

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m;

CREATE GLOBAL TEMPORARY TABLE temp_table (cvarchar2(10))

ON COMMIT DELETE ROWS TABLESPACE temp;

二．示例

2.1 查看dba_temp_free_space

SQL> set lin 160

SQL> col tablespace_name for a20

SQL> col tablespace_size for 99999999999

SQL> col allocated_space for 99999999999

SQL> col free_space for 99999999999

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME      TABLESPACE_SIZE ALLOCATED_SPACE   FREE_SPACE

-------------------- ------------------------------ ------------

TEMP                       524288000         7340032    523239424

SQL> select 524288000/1024/1024||'M'from dual;

5242

----

500M

--这里的Temp表空间是500M。

2.2 执行temp 表空间的online shrink 操作：

SQL> alter tablespace temp shrink spacekeep 400M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME      TABLESPACE_SIZE ALLOCATED_SPACE   FREE_SPACE

-------------------- ------------------------------ ------------

TEMP                       420478976         1048576    419430400

SQL> select 420478976/1024/1024||'M'from dual;

4204

----

401M

2.3 shrink 数据文件

--如果有多个temp数据文件，也可以直接指定某个特定的temp 数据文件来进行shrink：

SQL> col file_name for a50

SQL> select file_name fromdba_temp_files;

FILE_NAME

--------------------------------------------------

/u01/app/oracle/oradata/anqing/temp01.dbf

SQL> alter tablespace temp shrinktempfile '/u01/app/oracle/oradata/anqing/temp01.dbf' keep 300M;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME      TABLESPACE_SIZE ALLOCATED_SPACE   FREE_SPACE

-------------------- ------------------------------ ------------

TEMP                       315613184         1040384    314572800

SQL> select  315613184/1024/1024||'M' from dual;

315613184/10

------------

300.9921875M

SQL>

2.4 keep 选项说明

KEEP 选项用来指定压缩时表空间或者数据文件shrink的最小值，如果没有执行该命令，那么表空间或数据文件将被压缩到最小值。

SQL> alter tablespace temp shrink space;

Tablespace altered.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME      TABLESPACE_SIZE ALLOCATED_SPACE   FREE_SPACE

-------------------- ------------------------------ ------------

TEMP                         2088960         1040384      1048576

SQL> select 2088960/1024/1024||'M' fromdual;

2088960/10

----------

1.9921875M

--这里直接被压到2M了。Temp 表空间过小对性能是有影响的，所以在shrink时，还是建议使用keep 指定最小值。

转载于：http://blog.csdn.net/tianlesoftware/article/details/8225395