Oracle临时表空间综述

目录

1 临时表空间综述
2 查看临时表空间相关信息
3 临时表空间的日常操作
4 临时表空间的监控

1 临时表空间综述

在日常的使用中，偶见SQL报错：ORA-01652：无法通过 128 （在表空间TEMP中）扩展TEMP 段。该报错与临时表空间相关，以下对临时表空间的相关信息进行一个简介。
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。
像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。
当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。

临时表空间存储大规模排序操作(小规模排序操作会直接选择PGA的内存sort area、hash area和bitmap area,在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果。
它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。
临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。

2 查看临时表空间相关信息

查看实例对应的临时表空间

SQL> SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS where USERNAME like 'xxx%';

查看临时表空间对应的文件和大小等信息：

SELECT TABLESPACE_NAME AS TABLESPACE_NAME,       FILE_NAME AS FILE_NAME,       BLOCKS AS BLOCKS,       STATUS AS STATUS,       AUTOEXTENSIBLE AS AUTOEXTENSIBLE,       BYTES / 1024 / 1024 / 1024 AS "FILE_SIZE(G)",       DECODE(MAXBYTES,              0,              BYTES / 1024 / 1024 / 1024,              MAXBYTES / 1024 / 1024 / 1024) AS "MAX_SIZE(G)",       INCREMENT_BY AS "INCREMENT_BY",       round(USER_BYTES / 1024 / 1024 / 1024, 2) AS "USEFUL_SIZE"  FROM DBA_TEMP_FILES order by 1,2;

TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUTOEX FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE

---

xxx_TEMP +DATA/db/datafile/temp_xx_temp_01.dbf 524288 ONLINE NO 4 4 0 4

3 临时表空间的日常操作

创建临时表空间

CREATE TEMPORARY TABLESPACE TMP_TEST TEMPFILE '+DATA/db/tempfile/TMP_TEST01.dbf'SIZE 1GAUTOEXTEND OFF;

增加临时表空间

ALTER  TABLESPACE TMP_TESTADD TEMPFILE '+DATA/db/tempfile/TMP_TEST02.dbf'SIZE 2G

调整临时表空间文件大小

ALTER DATABASE TEMPFILE'+DATA/db/tempfile/TMP_TEST02.dbf' RESIZE 1G;

TABLESPACE_NAME FILE_NAME BLOCKS STATUS AUT FILE_SIZE(G) MAX_SIZE(G) INCREMENT_BY USEFUL_SIZE

---

TMP_TEST +DATA/db/tempfile/tmp_test01.dbf 131072 ONLINE NO 1 1 0 1
TMP_TEST +DATA/db/tempfile/tmp_test02.dbf 131072 ONLINE NO 1 1 0 1

查询ASM磁盘空间使用情况

select TOTAL_MB,FREE_MB   from v$asm_diskgroup;  TOTAL_MB    FREE_MB---------- ----------   5198564     177467

删除临时表空间文件（会将对应的物理文件删除）

ALTER TABLESPACE TMP_TEST DROP TEMPFILE '+DATA/db/tempfile/tmp_test02.dbf';

删除临时表空间

drop tablespace TMP_TEST including contents and datafiles;

查询ASM磁盘空间使select TOTAL_MB,FREE_MB from v$asm_diskgroup;

TOTAL_MB FREE_MB

---

5198564 179515

4 临时表空间的监控

查看临时表空间使用情况，必须在sys用户下才能查询，

GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小： PS：据观察GV_$TEMP_SPACE_HEADER记录类似于高水位线，使用的大小展示的是当前使用过最大的大小。

SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",       TT.TOTAL - TU.USED                                    AS "FREE(G)",       TT.TOTAL                                              AS "TOTAL(G)",       ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",       ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"FROM (SELECT TABLESPACE_NAME,               round(SUM(BYTES_USED) / 1024 / 1024 / 1024,2) USED       FROM GV_$TEMP_SPACE_HEADER       GROUP BY TABLESPACE_NAME) TU ,     (SELECT TABLESPACE_NAME,              round(SUM(BYTES) / 1024 / 1024 / 1024,2) AS TOTAL       FROM DBA_TEMP_FILES       GROUP BY TABLESPACE_NAME) TTWHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;TABLESPACE_NAME         FREE(G)   TOTAL(G)    USED(%)    FREE(%)-------------------- ---------- ---------- ---------- ----------xx_TEMP                    10         54     81.481     18.519TEMP                      10.78         12     10.167     89.833 查找当前消耗临时表空间资源的SQL语句SELECT se.username,       se.sid,       se.serial#,       se.status,       su.extents,       su.blocks * TO_NUMBER(RTRIM(p.VALUE)) / 1024 / 1024 AS Space,       tablespace,       segtype,       s.sql_id,       sql_text  FROM v$sort_usage su,       v$parameter p,       v$session se,       v$sql s WHERE p.name = 'db_block_size'   AND su.session_addr = se.saddr   AND s.hash_value = su.sqlhash   AND s.address = su.sqladdr   AND su.username = 'XXXX'   AND se.status = 'ACTIVE' ORDER BY se.username, se.sid;