ORA-01652 无法通过128 (在表空间 TEMP中)扩展temp段，临时表空间满了

淡淡的烟草味﹌ 2023-03-01 03:30 5阅读 0赞

今天遇到临时表空间满了，报错：

ORA-01652 无法通过128 (在表空间 TEMP中)扩展temp段。

蛋疼，找DBA加了临时表空间，重跑转换程序，好了。

**解决办法：加临时表空间（应急）；优化程序，减少对temp的使用（后续）。**

记录下查询临时表空间的sql，

select *
      from (Select a.tablespace_name,
                   to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
                   to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
                   to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
                           '99,999.999') use_bytes,
                   to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
              from (select tablespace_name, sum(bytes) bytes
                      from dba_data_files
                     group by tablespace_name) a,
                   (select tablespace_name, sum(bytes) bytes
                      from dba_free_space
                     group by tablespace_name) b
             where a.tablespace_name = b.tablespace_name
            union all
            select c.tablespace_name,
                   to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
                   to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
                   to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
                   to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
              from (select tablespace_name, sum(bytes) bytes
                      from dba_temp_files
                     group by tablespace_name) c,
                   (select tablespace_name, sum(bytes_cached) bytes_used
                      from v$temp_extent_pool
                     group by tablespace_name) d
             where c.tablespace_name = d.tablespace_name)
     order by tablespace_name ;