2016-10-21 from–https://www.cnblogs.com/cyl048/p/5984854.html

  今天在做一个查询的时候,报了一个“ORA-01652无法通过128(在表空间temp中)扩展temp段”

ORA-01652: 无法通过128(在表空间TOSTEMP中)扩展 temp 段
ORA-06512: 在”Funcking”, line 60
ORA-06512: 在line 1

错误解决网上也有一些相关的资料。我的实验解决方法是这样的:

1.查看表空间使用率(包括临时表空间)
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
2.查看文件是否自动扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d
如果想查看临时表空间文件是否自动扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;
3.对临时文件进行扩展。

1)TOSTEMP表空间使用率接近100%,对它进行扩展。

SQL> alter database tempfile  ‘C:xxxxxx\TOSTEMP01.DBF’resize 500M;
2)若是发现 表空间使用率接近100%,且不可扩展修改文件自动可扩展性
alter database datafile ‘E:xxxxxxESCALADE.ORA’ autoextend on;

3)修改可扩展上限为无限制

SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;

 

正常的解决方案,请参考:http://www.atmarkit.co.jp/fdb/rensai/ora_admin/03/ora_admin02.html

很遗憾把tempfile 改到3G也不行。

根据网上看到有些朋友又碰到这样的问题,说是只要进行analyze就可以。我也进行analyze分析一下。

EXEC DBMS_UTILITY.ANALYZE_SCHEMA(‘USER’,’ESTIMATE’);

附DBMS_UTILITY.ANALYZE_SCHEMA作用:

このプロシージャは、スキーマ内にあるすべての表、クラスタおよび索引でANALYZEコマンドを実行します。このプロシージャを使用して、非オプティマイザ統計情報を収集します。

 

然后再执行存储过程,等待结果……(19:53开始)

20:40,结果出来了,还是不行。值得进一步探讨。

 

经过这次的失败后,我在想问题到底在哪里?

我把这个出错的SQL文从存储过程摘出来,进行分析。

1)从SQL结构中没发现任何问题。

2)在sqlplus里面跟了一下执行计划。

在经过30分钟左右的等待后,这一执行计划显示了出来。看得真是吓了一跳。

buffer消耗超过5000G,都不敢相信自己的眼睛,byte位数算了几遍,确实是特别大。

看到执行计划我也就知道原因是什么了,临时空间怎么加也不会够呀。

 

以上是实验告一段落,对于这种问题的解决方法,如下:

1)首先对存储过程的执行计划进行分析。

看占用多大的临时空间,有没有优化的余地,怎么优化。

(这就是网上说碰巧用analyze命令,结果通过的原因,应该是analyze命令,让执行计划发生了改变)

2)优化后的临时空间大于现有的临时空间的话,扩张临时空间。

以上算是我对ORA-01652错误的诊断分析。