Oracle11g 临时表空间收缩

川长思鸟来 2022-06-08 04:18 337阅读 0赞

一. 临时表空间收缩

1.1 说明

以下操作会占用大量的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 指定最小值。

Oracle11g 临时表空间收缩

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle 11g 修改表的所属表空间

相关 oracle 11g 表空间大小问题

相关【Oracle】增加临时表空间

相关 ORACLE 收缩表空间的数据文件

相关 oracle临时表空间作用

相关 Oracle 11g 清理SYSAUX空间

相关 Oracle11g 临时表空间收缩

相关 Oracle 创建临时表空间组

相关 oracle临时表空间操作

相关 oracle 临时表空间使用情况