ORACLE 临时表空间管理

 临时表空间和临时段

临时表空间用于存放排序、临时表等数据，其信息不需要REDO，因此临时表的DML操作往往比普通表产生的REDO少很多。临时表数据变化不产生REDO，UNDO数据变化产生REDO。临时段不仅仅存在于临时表空间中，也可能存在普通表空间。比如通过CTAS创建一张表，新表的数据放在临时段中，这些临时段在CTAS完成的时候会被转换为PERMENT段。ORACLE7.3版本之前，临时段是在需要时分配，使用完毕后被删除.ORACLE7.3 推出的新算法的核心就是SEP（SORT EXTENT POOL），SEP 负责管理临时段中扩展的结构，存储在共享池内，任何需要使用排序空间的操作，都需要从SEP 中分配空闲的扩展，使用完毕后，不需要释放该空间，只需要在SEP 中将该扩展设置为空闲。
当数据库实例启动后，SMON 将会删除该实例未释放的临时段，并且对临时表空间进行碎片整理。在这个操作完成前，数据库打开的操作不能完成。因此每次数据库重启后，临时段中的垃圾都会被完全清理。当数据库打开后，第一个进行的硬盘排序操作会在相关的临时表空间内创建临时段，这个临时段也是整个实例唯一的临时段（在新的临时段算法下，同一个表空间内，每个实例只有一个临时段）。临时段中扩展的信息会被记录在SEP 中。硬盘排序操作会在SEP 中查找可用的扩展，在查找前，需要获得SORT EXTENT POOL 闩锁。如果能找到可用的扩展，那么SEP 中已被分配的扩展就会被标注为占用状态；如果找不到可用的扩展，那么系统就会试图从表空间中分配新的空间，而如果这个分配工作因为表空间的空闲空间不足而无法完成，那么就会产生一个ORA-1652 错误。当排序操作完成的时候，会再次获取SORT EXTENT POOL 闩锁，并且将使用的扩展标注为空闲，然后释放SORT EXTENT POOL 闩锁。新的临时表空间管理算法不需要频繁地分配和释放临时段，这大大提高了临时段管理的效率。由于这是一种只分配不释放的算法，因此DBA 经常会看到自己的临时表空间总是处于或者接近100％使用的状态。其实对于7.3 以后的版本而言，临时表空间使用率接近100％是十分正常的，DBA 可以通过V$SORT_USAGE 和V$SORT_SEGMENTS 这两个视图来检查临时段的使用情况。
在RAC/OPS环境下，临时段的管理算法也是类似的。在RAC 中，由于多个实例会共享一个临时表空间，因此这些实例也能够共享相同的扩展。在RAC环境下，每个实例都拥有独立的SEP，各个实例中的排序操作需要在自己的SEP中分配空间。如果SEP 中无法分配到足够的空间，那么首先会在表空间中分配；而如果表空间也已经分配完毕，这时若其他的实例还有空闲的扩展，那么这个扩展就可以分配给需要的实例使用。这些操作都是不可见的，虽然SERVER 进程不会收到ORA-1652 的错误信息，但是在ALTER LOG 中会有一个ORA-1652 的记录。

临时表空间的使用情况：
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,
SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
FROM GV_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TU ,
(SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

查询会话使用临时表空间
SELECT SE.USERNAME,
SE.SID,
SE.SERIAL#,
SE.SQL_ADDRESS,
SE.MACHINE,
SE.PROGRAM,
SU.TABLESPACE,
SU.SEGTYPE,
SU.CONTENTS FROM V$SESSION SE,
V$SORT_USAGE SU WHERE SE.SADDR = SU.SESSION_ADDR;
或者：

SELECT B.TABLESPACE,
B.SEGFILE#,
B.SEGBLK#,
B.BLOCKS,
B.BLOCKS * 32 / 1024 / 1024,
A.SID,
A.SERIAL#,
A.USERNAME,
A.OSUSER,
A.STATUS,
C.SQL_TEXT,
B.CONTENTS
 FROM V$SESSION A, V$SORT_USAGE B, V$SQL C
WHERE A.SADDR = B.SESSION_ADDR
AND A.SQL_ADDRESS = C.ADDRESS(+)
and status = 'ACTIVE'
ORDER BY B.BLOCKS DESC

查看临时表空间的空闲情况
SELECT TABLESPACE_NAME,FILE_ID,BYTES_USED/1024/1024,BYTES_FREE/1024/1024 FROM V$TEMP_SPACE_HEADER;

数据库中消耗资源比较大的SQL
select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
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
order by se.username, se.sid;

或者：
select su.username,su.Extents,tablespace,segtype,sql_text  from v$sort_usage su,v$sql s Where su.SQL_ID = s.SQL_ID;

常见占用临时表空间的几种情况
1：order by or group by (disc sort占主要部分)；
2：索引的创建和重创建；
3：distinct操作；
4：union & intersect & minus sort-merge joins；
5：analyze 操作；
6:有些异常也会引起TEMP的暴涨。

临时表空间使用率过高,日常维护
确定数据库schema的默认表空间
select username,temporary_tablespace from dba_users where account_status ='OPEN';

确定数据库的默认临时表空间

select * from database_properties where property_name ='DEFAULT_TEMP_TABLESPACE';

1.创建中转临时表空间
create temporary tablespace temp2 tempfile '/data/app/oracle/oradata/prod/temp02.dbf' size 512M autoextend on next 1M maxsize unlimited;

2.改变缺省临时表空间为中转临时表空间TEMP2
alter database default temporary tablespace TEMP2;
验证用户的临时表空间为TEMP2
select username,temporary_tablespace from dba_users where account_status ='OPEN';

3.删除原临时表空间
drop tablespace temp including contens and datafiles;

4.重建临时表空间
create temporary tablespace temp tempfile '/data/app/oracle/oradata/prod/temp01.dbf' size 512M autoextend on next 1M maxsize unlimited;

5.重置缺省临时表空间为新建的TEMP表空间
alter database default temporary tablespace TEMP1;

验证用户的临时表空间为TEMP
select username,temporary_tablespace from dba_users where account_status ='OPEN';