在什么样的情况下会使用临时表空间?

1，索引创建或重创建.

2，ORDER BY or GROUP BY

3，DISTINCT 操作.

4，UNION & INTERSECT & MINUS

5，Sort-Merge joins.

6，Analyze 操作

7，有些异常将会引起temp暴涨

如何从根本上降低临时表空间的膨胀呢？（一般情况下）

1 设置合理的pga或sort_area_size

2 优化引起disk sort的sql

相关视图

select * from dba_temp_files;
select * from dba_tablespaces;
select * from database_properties;
相关操作
--创建新的临时表空间
create temporary tablespace ts_tmp TEMPFILE '/home1/oracle/oradata/btoc/ts_tmp01.dbf' size 4G extent management local;
--将当前表空间设置成默认临时表空间
alter database default temporary tablespace ts_tmp;
-- 删除原来的临时表空间
drop tablespace TEMP including contents and datafiles;
--修改用户的临时表空间
select * from dba_users;
alter user xxx temporary tablespace ts_tmp;
--相关脚本
Oracle会在第一次执行磁盘排序时创建排序段，并且根据需要扩展，但是不会收缩。
SELECT A.tablespace_name tablespace,
D.mb_total,
SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts# = C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

--会话使用的排序空间
SELECT S.sid || ',' || S.serial# sid_serial,
S.username,
S.osuser,
P.spid,
S.module,
S.program,
SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid,
S.serial#,
S.username,
S.osuser,
P.spid,
S.module,
S.program,
TBS.block_size,
T.tablespace
ORDER BY sid_serial;
--语句使用的临时空间
SELECT S.sid || ',' || S.serial# sid_serial,
S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
T.sqladdr address,
Q.hash_value,
Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address(+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

ITPub博客

关于临时表空间

最新文章