临时表空间日常维护处理

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序

监控临时表空间:

COL TEMP_FILE FOR A60;

SELECT ROUND((F.BYTES_FREE + F.BYTES_USED)/1024/1024/1024, 2) AS "TOTAL(GB)",

ROUND(((F.BYTES_FREE + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)",

D.FILE_NAME AS "TEMP_FILE",

ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)" ,

ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2) AS "TOTAL(GB)",

ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" ,

ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)"

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;

查看临时表空间对应的临时文件的使用情况:

SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,

BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED ,

BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE

FROM V$TEMP_SPACE_HEADER

ORDER BY 1 DESC;

查找消耗临时表空间资源比较多的SQL语句

set line 300

col username for a10

col tablespace for a10

col sql_id for a13

col prev_sql_id for a13

select s.sid,

s.serial#,

s.username,

s.sql_id,

s.prev_sql_id,

u.tablespace,

u.contents,

u.segtype,

u.extents,

u.blocks,

round(((u.blocks * P.VALUE) / 1024 / 1024), 2) MB

from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P

where s.saddr = u.session_addr

AND UPPER(P.NAME) = 'DB_BLOCK_SIZE'

order by MB DESC;

---执行以下命令释放temp表空间（详见文档：How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (文档 ID 802897.1) ）

declare

clb clob;

ch varchar2(32767);

k number;

begin

dbms_lob.createtemporary(clb,true,dbms_lob.call);

for i in 1..1500 loop

ch:=lpad('o',32767,'Y');

dbms_lob.writeappend(clb,length(ch),ch);

end loop;

k:=dbms_lob.getlength(clb);

dbms_lob.freetemporary(clb);

dbms_output.put_line('the clob length: '||k);

end;

/

切换临时表空间

1：查看旧临时表空间信息

SELECT * FROM V$TEMPFILE

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

2：创建中转的临时表空间

3：添加相应的数据文件

4：切换临时表空间。

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

5：删除旧的临时表空间数据文件

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

6：如果有必要，重新指定用户临时表空间为新建的临时表空间

ALTER USER ODS TEMPORARY TABLESPACE TMP;

ALTER USER EDS TEMPORARY TABLESPACE TMP;

ALTER USER ETL TEMPORARY TABLESPACE TMP;

ALTER USER DM TEMPORARY TABLESPACE TMP;

收缩临时表空间

排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。

SQL> ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G;

SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'