临时表空间爆满问题

程序碎碎念 2021-10-14

490

使临时表空间占用增加的地方：
（1） order by、distinct和 group by
（2）索引的创建和重创建rebuild
（3） union、insersect、minus、sort-merge joins
（4）Anaylze 操作
（5）其他异常引起的

--查看临时表空间

SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total,3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used)/ 1024 / 1024 used
          FROM GV$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024/ 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name;

--查看用户临时表空间

 select d.username, t.file_name, d.temporary_tablespace from
 DBA_TEMP_FILES t,dba_users d 
where t.tablespace_name = d.temporary_tablespace

--修改用户的临时表空间

 alter user xxx temporary tablespace data_temp;

--查询默认临时表空间

select property_name,property_value from 
database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

--查询原先的数据文件做个记录

 select *  from  dba_temp_files

--事务在表空间下面的段状态，必须等待临时表空间下的所有段状态变成offline才能drop

select segment_name,tablespace_name ,r.status
,(initial_extent/1024) initialExtent,(next_extent/1024) nextExtent,
max_extents,v.curext CurExtent from dba_rollback_segs r
,v$rollstat v where r.segment_id=v.USN(+) order by segment_name

--删除原来的临时表空间

drop tablespace pdm_temp including contents and datafiles;

--如果一直删不掉，查看是那些用户仍在使用原先的临时表空间

select to_char( b.SQL_FULLTEXT),
(select osuser from v$session v where v.saddr=session_addr ),
 (select sid from v$session v
 where v.saddr=session_addr )
, (select serial# from 
v$session v where v.saddr=session_addr ),
 tablespace,a.*  from 
 v$tempseg_usage a,v$sql b where a.SQL_ID=b.SQL_ID;
 alter system kill session 'sid,serial#'

--创建新的临时表空间

create temporary tablespace pdm_temp
tempfile '/data/dw/DATA/dw_temp01.dbf' size 30G ,
         '/data/dw/DATA/dw_temp02.dbf' size 30G,
         '/data/dw/DATA/dw_temp03.dbf' size 30G,
         '/data/dw/DATA/dw_temp04.dbf' size 30G,
         '/data/dw/DATA/dw_temp05.dbf' size 30G
         autoextend off;

--查看用户临时表空间

select d.username, t.file_name, 
d.temporary_tablespace from DBA_TEMP_FILES t
,dba_users d where t.tablespace_name = d.temporary_tablespace

--切换为原先的临时表空间

   alter user xxx temporary tablespace pdm_temp;

--删掉临时用来切换的表空间

  drop tablespace data_temp including contents and datafiles;

--临时表空间组

--增加临时表空间到临时表空间组上
ALTER TABLESPACE  DIGITALSPDEPT_TEMP TABLESPACE GROUP USER_TEMP_GRP;
--修改用户的临时表空间为临时表空间组
alter user pdm temporary tablespace USER_TEMP_GRP;

数据库

文章转载自程序碎碎念，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

临时表空间爆满问题

评论