ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01

标签: database 数据库 sql file 扩展
16593人阅读 评论(0) 收藏 举报
分类:
收集数据库信息时候报ORA-01652错 如下


SQL> EXEC DBMS_STATS.gather_database_stats;

BEGIN DBMS_STATS.gather_database_stats; END;
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13700
ORA-06512: at "SYS.DBMS_STATS", line 13664
ORA-06512: at line 1


原因是我的temp01表空间过小,而且没有自动扩展,因此无法完成数据库信息收集

SQL>select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files

FILE_NAME                                                   MB  AUT    TABLESPACE_NAME
-------------------------------------------------------- ----- -----  ------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf         512  NO     TEMP01


需要对表空间进行重建,新建一个数据库的临时表空间temp02


SQL> create temporary tablespace TEMP02 
     TEMPFILE '/u01/app/oracle/product/10.2.0/db_1/dbs/temp02.dbf' SIZE 512M 
     REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; 

Tablespace created.


更改数据库的默认临时表空间为temp02

SQL> alter database default temporary tablespace temp02;

Database altered.

删除原来的默认临时表空间TEMP01

SQL> drop tablespace temp01 including contents and datafiles;

Tablespace dropped.

创建新的临时表空间TEMP01
SQL> create temporary tablespace TEMP01 
TEMPFILE '/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf' SIZE 512M 
REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;

Tablespace created.

更改数据库的默认临时表空间为TEMP01

SQL> alter database default temporary tablespace temp01;

Database altered.

删除临时表空间TEMP02

SQL> drop tablespace temp02 including contents and datafiles;

Tablespace dropped.

查询新建的临时表空间TEMP01信息,自动扩展已经为“YES”
SQL>select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files

FILE_NAME                                                   MB  AUT    TABLESPACE_NAME
-------------------------------------------------------- ----- -----  ------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf         512  YES     TEMP01

此时再收集数据库信息,收集完毕

SQL> EXEC DBMS_STATS.gather_database_stats;

PL/SQL procedure successfully completed.




临时表空间常用操作

更改临时表空间大小

SQL>alter database tempfile '/u01/app/oracle/product/10.2.0/db_1/dbs/temp01.dbf' RESIZE 1000m;

查看临时表空间大小
SQL>select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files

查看评论

ORA-1652: unable to extend temp segment

1.问题描述 检查alert日志发现近期大量ORA-1652报错 截止上午10:00近2天报错次数为19次 Wed Feb 17 01:32:03 2016 ORA-1652: una...
  • EVISWANG
  • EVISWANG
  • 2016-02-17 17:27:49
  • 1820

ORA-01652: unable to extend temp segment by 8192...

1、错误提示信息alter index err ORA-01652: unable to extend temp segment by 8192 in tablespaceGOEX_ARCHIVE_I...
  • robinson_0612
  • robinson_0612
  • 2014-04-14 11:11:52
  • 7475

ORA-1652: unable to extend temp segment诊断以及解决

“ORA-1652: unable to extend temp segment”       临时表空间被资源中的多个会话共享,并且quotas不能限制每个用户使用的临时表空间数量,当临时表空间被填...
  • aicon
  • aicon
  • 2009-09-24 09:22:00
  • 750

2016-1-8 ORA-1652: unable to extend temp segment by 128 in tablespace解决方案

一次ORA-1652的诊断过程,系统不能使用,重启后可以使用。 weblogic日志: #### > ...
  • guogang83
  • guogang83
  • 2016-01-08 18:13:29
  • 3762

ORA-1652错误 unable to extend temp segment by 128 in tablespace

周六下午,收到EM alert邮件 Sfc12db 的TEMP 表空间使用率达到99%  查看alert log 在alert log中有 ORA-1652错误 unable to extend ...
  • xiaofan23z
  • xiaofan23z
  • 2011-09-11 14:31:46
  • 4473

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

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP  --总的使用情况  selec...
  • rgb_rgb
  • rgb_rgb
  • 2014-06-17 10:58:19
  • 2713

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

解决办法 --创建中转临时表空间 2.create temporary tablespace TEMP02 TEMPFILE '/u01/app/oracle/oradata/...
  • Evils798
  • Evils798
  • 2012-09-18 14:06:35
  • 1434

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

一、原因     意思是指temp表空间无法自动扩展temp段。这种问题一般有两种原因:一是临时表空间空间太小,二是不能自动扩展。 二、分析     查看temp表空间的数据文件个数,当前大小,是否...
  • u013379717
  • u013379717
  • 2018-01-18 09:39:31
  • 143

unable to extend temp segment by 128 in tablespace name

SELECT T1.EVENTTIMESTAMP,       T1.OBJECTID,       T1.UIA_AREA_ID,       T1.UIA_LOCATION_ID,    ...
  • shachejiayou
  • shachejiayou
  • 2015-10-09 11:23:48
  • 388

java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

数据库更新操作报错: Error updating database.  Cause: java.sql.SQLException: ORA-01652: unable to extend temp...
  • sdizoea
  • sdizoea
  • 2017-03-23 17:49:10
  • 349
    个人资料
    持之以恒
    等级:
    访问量: 85万+
    积分: 9310
    排名: 2483
    简介
    此博客为笔者的DT工作记录,欢迎一起交流学习工作中的点滴,若博客文章观点存在原理的错误、纰漏,笔者非常欢迎各位不保留地指出
    最新评论