临时表空间满或者丢失的处理.pdf

山

142

6页

16次

2024-01-23

免费下载

临时表空间满或者丢失的处理

临时表空间主要使用在以下几种情况：

1、order by or group by (disc sort 占主要部分)；

2、索引的创建和重创建；

3、distinct 操作；

4、union & intersect & minus sort-merge joins；

5、Analyze 操作；

6、有些异常也会引起 TEMP 的暴涨。

Oracle 临时表空间暴涨的现象经过分析可能是以下几个方面的原因造成的：

1. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能

还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大

同样会影响性能，至于需要设置为多大需要仔细的测试。

2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的字

段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间

的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。

3.对查询的某些字段没有建立索引。Oracle 中，如果表没有索引，那么会将所有的数据都

复制到临时表空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。

针对以上的分析，对查询的语句和索引进行了优化，情况得到缓解，但是需要进一步测试。

需要注意的是，oracle 使用的是贪心算法，如果上次磁盘空间消耗达到 1GB，那么临时表

空间就是 1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

如果临时表空间是 temporary 的，空间不会释放，只是在 sort 结束后被标记为 free 的，

如果是 permanent 的，由 SMON 负责在 sort 结束后释放，都不用去手工释放的

当临时表空间撑满后，就会影响到系统性能，这时我们就需要人为干涉来适当释放表空间大

小。

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

1、查询 sql

1、查询临时表空间占用情况（最大使用，不是真实）select * from (select

c.tablespace_name,sum(decode(c.maxbytes, 0, c.bytes,

maxbytes))/1024/1024 max_mb,sum(c.bytes) /1024 /1024 temp_mb from

dba_temp_files c group by tablespace_name) b;

2、查看临时表空间文件 select tablespace_name,file_name,bytes/1024/1024

file_size,autoextensible from dba_temp_files;

3、查询用户默认临时表空间 select username,temporary_tablespace from

dba_users;

4、查看数据库默认的临时表空间 select * from database_properties where

property_name='DEFAULT_TEMP_TABLESPACE';

、查看哪些用户在使用临时段

select

se.username,se.sid,se.serial#,se.sql_address,se.machine,se.progra

m,su.tablespace,su.segtype,su.contents from v$session se,

v$sort_usage su where se.saddr = su.session_addr;

SELECT

s.sid,s.serial#,s.username,p.spid,s.module,p.program,SUM(su.block

s)*tbsp.block_size/1024/1024 mb_used ,su.tablespace FROM

v$sort_usage su,v$session s,dba_tablespaces tbsp,v$process p WHERE

su.session_addr=s.saddr AND su.tablespace=tbsp.tablespace_name AND

s.paddr=p.addr GROUP BY

s.sid,s.serial#,s.username,s.osuser,p.spid,s.module,

p.program,tbsp.block_size,su.tablespace ORDER BY s.sid;

6、临时表空间数据文件使用率（真实占用）select round((f.BYTES_FREE +

f.BYTES_USED) / 1024 / 1024, 2) "total MB",round(((f.BYTES_FREE +

f.BYTES_USED) - nvl(p.BYTES_USED, 0)) / 1024 / 1024,2) "Free

MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) "Used

MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) /round((f.BYTES_FREE