目录
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 1G
AUTOEXTEND OFF;
增加临时表空间
ALTER TABLESPACE TMP_TEST
ADD 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) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;
TABLESPACE_NAME FREE(G) TOTAL(G) USED(%) FREE(%)
-------------------- ---------- ---------- ---------- ----------
xx_TEMP 10 54 81.481 18.519
TEMP 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;