Oracle 11g 新特性 -- 临时表空间收缩

说明【https://blog.csdn.net/tianlesoftware/article/details/8225395】

            一. 临时表空间收缩
 


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

Oracle 
            Temp
             临时表空间

http:
            //blog.csdn.net/tianlesoftware/article/details/4697417

 

以下操作会占用大量的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 相反，ALTERDATABASE 命令不会在排序区分配后尝试取消分配。

 

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

 
=============================================================================================================================================
set pagesize 50
col tablespace_name for a20
col "Tempfile name" for a42
set linesize 300Select 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_idand p.file_id(+) =d.file_id;

 
==========================================================================================================

SELECT D.TABLESPACE_NAME,
SPACE || ‘M‘ "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || ‘M‘ "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) ||

‘%‘
"USED_RATE(%)",
FREE_SPACE || ‘M‘ "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2)

FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL

--如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE || ‘M‘ "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || ‘M‘ "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || ‘%‘

"USED_RATE(%)",
NVL (FREE_SPACE, 0) || ‘M‘ "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2)

USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2)

FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;

===================================================================================================================================

            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 TEMPORARYTABLE temp_table (cvarchar2(10))

ONCOMMITDELETE ROWS TABLESPACE temp;

 

二．示例
 

2.1 查看dba_temp_free_space
 

SQL>set lin 160       

SQL> col tablespace_name for a20

SQL> col tablespace_size for99999999999

SQL> col allocated_space for99999999999

SQL> col free_space for99999999999

SQL>select*from dba_temp_free_space;

 

TABLESPACE_NAME      TABLESPACE_SIZE ALLOCATED_SPACE   FREE_SPACE

-------------------- ------------------------------ ------------TEMP5242880007340032523239424

 

SQL>select524288000/1024/1024||‘M‘from dual;

 

5242----
500M

--这里的Temp 表空间是500M。2.2 执行temp 表空间的online shrink 操作：
SQL>alter tablespace temp shrink space keep 400M;

 

Tablespace altered.

 

SQL>select*from dba_temp_free_space;

 

TABLESPACE_NAME      TABLESPACE_SIZE ALLOCATED_SPACE   FREE_SPACE

-------------------- ------------------------------ ------------TEMP4204789761048576419430400

 

SQL>select420478976/1024/1024||‘M‘from dual;

 

4204----
401M

 

 

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

SQL> col file_namefor a50

SQL>selectfile_name fromdba_temp_files;

 

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

 

SQL>alter tablespace temp shrink tempfile ‘/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

-------------------- ------------------------------ ------------TEMP3156131841040384314572800

 

SQL>select315613184/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

-------------------- ------------------------------ ------------TEMP208896010403841048576

 

SQL>select2088960/1024/1024||‘M‘ fromdual;

 

2088960/10----------1.9921875M

 

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