一、问题描述： 输入条件，点击查询后，页面一直在加载中，后台未出现error日志，经过多次操作发现有错误日志：ora-01652无法通过128（在temp表空间中）扩展temp段
二、原因分析： 1.分析此统计sql，百度ora-01652的原因，是sql统计时使用的临时表空间不够导致的，临时表空间经过查询是2G，修改到无限制 alter database tempfile ‘/home/oracle/app/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;仍然不能解决此问题。 可能是第一个sql把第二个sql的temp跑光了
select * from dba_tablespaces; --查看数据库表空间 select * from dba_data_files–查看表空间文件 select * from v$tempfile; --查看临时表空间文件 select * from dba_temp_files; --查看临时表空间 在第二条语句中可以查看到，其中的bytes就是表空间大小，这里单位是字节，所以，最好这么查： select tablespace_name,file_name,bytes/1024/1024 “表空间大小(M)”, autoextensible from dba_temp_files; 3.百度结果： 执行一个sql语句后，大约花了10分钟，但是报了一个ora-01652错误，查阅了oracle的错误代码说明：意思是指temp表空间无法自动扩展temp段。这种问题一般有两种原因：一是临时表空间空间太小，二是不能自动扩展。 分析过程： 既然是temp表空间有问题，那当然就要从temp表空间说起啦。首先要说明的是temp表空间的作用，temp表空间主要是用作需要排序的操作。 1.临时表空间 是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)每个用户都有一个临时表空间。 2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理。 3.分配用户单独临时表空间,一般是针对 大型产品数据库,OLTP数据库,数据库仓库对于小型产品不需要单独制定临时表空间,使用默认临时表空间。 正常情况下，一个sql执行之后，返回结果后系统会自动收回分配给这个用户的空间。以便可以把此部分空间再分配给其他用户。 好了，既然问题出在临时表空间，那就查看一下临时表空间信息： select * from dba_tablespaces; select * from dba_temp_files; select * from v$tempfile 通过观察发现，temp表空间只有一个数据文件，大小为4G，不自动扩展。大小24G，应该说是不能算小啦。这时想到，需要看一看执行的sql语句到底是什么样的，难道它能把这个空间用完（由于系统事务非常少，所以暂不考虑其它用户语句对temp的影响），于是就把那个语句拷贝下拉，研究一番： select count(1) as counnum from (select c.collecttime, c.objectname, c.onlinetotal, c.onlinecount, c.vnocode, c.provincename, c.username, c.regionname, case when c.avgonline = 0 then c.onlinetotal * 100 else c.onlinetotal * 100 / c.avgonline end as volatility from (select nvl(avg(a.onlinetotal), 0) avgonline, a.hotspotobjectid, b.collecttime, b.objectname, b.onlinetotal, b.onlinecount, b.vnocode, b.provincename, b.username, b.regionname from pmrpt_wlan_htstat_day a, (select p.collecttime, r.objectname, p.onlinetotal, p.onlinecount, r.vnocode, r.provincename, r.regionname, r.username, p.hotspotobjectid from pmrpt_wlan_htstat_day p, (select ld., wl.username from (select dtt. from rm_wlan_hotspot dtt where 1 = 1) ld, (select distinct a.hotspotobjectid, b.userid, b.username from rm_wlan_hotspot_user_rel a, gumuser b where a.userid = b.userid) wl where ld.objectid = wl.hotspotobjectid) r where p.hotspotobjectid = r.objectid and p.collecttime <= to_date(‘2015-01-06’,‘yyyy-MM-dd’) and p.collecttime >= to_date(‘2014-12-07’,‘yyyy-MM-dd’)) b where a.collecttime < b.collecttime and a.collecttime >= b.collecttime - 30 and a.hotspotobjectid = b.hotspotobjectid group by b.collecttime, a.hotspotobjectid, b.objectname, b.onlinetotal, b.onlinecount, b.vnocode, b.provincename, b.username, b.regionname) c) d where d.volatility > 1 ---------------------------------------------------------------------------修改优化sql，由原来的占用临时表空间修改为–>占用内存表空间 with temp_data as (select ld., wl.username from (select dtt. from rm_wlan_hotspot dtt where 1 = 1) ld, (select distinct a.hotspotobjectid, b.userid, b.username from rm_wlan_hotspot_user_rel a, gumuser b where a.userid = b.userid) wl where ld.objectid = wl.hotspotobjectid) select count(1) as counnum from (select c.collecttime, c.objectname, c.onlinetotal, c.onlinecount, c.vnocode, c.provincename, c.username, c.regionname, case when c.avgonline = 0 then c.onlinetotal * 100 else c.onlinetotal * 100 / c.avgonline end as volatility from (select nvl(avg(a.onlinetotal), 0) avgonline, a.hotspotobjectid, b.collecttime, b.objectname, b.onlinetotal, b.onlinecount, b.vnocode, b.provincename, b.username, b.regionname from pmrpt_wlan_htstat_day a, (select p.collecttime, r.objectname, p.onlinetotal, p.onlinecount, r.vnocode, r.provincename, r.regionname, r.username, p.hotspotobjectid from pmrpt_wlan_htstat_day p, temp_data r where p.hotspotobjectid = r.objectid and p.collecttime <= ? and p.collecttime >= ?) b where a.collecttime < b.collecttime and a.collecttime >= b.collecttime - 30 and a.hotspotobjectid = b.hotspotobjectid group by b.collecttime, a.hotspotobjectid, b.objectname, b.onlinetotal, b.onlinecount, b.vnocode, b.provincename, b.username, b.regionname) c) d where d.volatility > 1据我所知，表空间的扩展有两种方法： 1、增加数据文件 2、调整当前数据文件的大小或扩展的大小
1、增加数据文件 ALTER TABLESPACE TESTTBS ADD DATAFILE ‘+DG1/ora/datafile/testtbs01.ora’ SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 2000M;
增加了一个500M的数据文件，并且可以自动扩展到2G，每次扩展50M。
2、增加当前数据文件的大小： ALTER DATABASE DATAFILE ‘+DG1/ora/datafile/testtbs.ora’ RESIZE 50000M;
或者使用file_id ALTER DATABASE DATAFILE 10 RESIZE 50000M;
3、在使用过程中，如果想改变某个数据文件的最大大小，可以
ALTER DATABASE DATAFILE ‘+DG1/ora/datafile/test.ora’ AUTOEXTEND ON NEXT 50M MAXSIZE 10240M; 4，其它可能会用到的命令： SQL> set linesize 800 SQL> select file_name,autoextensible,increment_by from dba_data_files; FILE_NAMED:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024
SYSAUX 270 UNDOTBS1 25 USERS 5 SYSTEM 510 SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024
UNDOTBS1 5.625 SYSAUX 13.625 USERS 4.5 SYSTEM 4.8125
以下摘自http://blog.csdn.net/luojunjing/archive/2005/02/12/286327.aspx ORA-01652:unable to extend temp segment by num in tablespace name
产生原因：ORACLE临时段表空间不足，因为ORACLE总是尽量分配连续空间，一但没有足够的可分配空间或者分配不连续就会出现上述的现象。
解决方法：我们知道由于ORACLE将表空间作为逻辑结构-单元，而表空间的物理结构是数据文件，数据文件在磁盘上物理地创建，表空间的所有对象也存在于磁盘上，为了给表空间增加空间，就必须增加数据文件。先查看一下指定表空间的可用空间，使用视图SYS.DBA_FREE_SPACE，视图中每条记录代表可用空间的碎片大小：
SQL>Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name=’’;
返回的信息可初步确定可用空间的最大块，看一下它是否小于错误信息中提到的尺寸，再查看一下缺省的表空间参数：
SQL>SELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME=name;
通过下面的SQL命令修改临时段表空间的缺省存储值：
SQL>ALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY);
适当增大缺省值的大小有可能解决出现的错误问题，也可以通过修改用户的临时表空间大小来解决这个问题：
SQL>ALTER USER username TEMPORARY TABLESPACE new_tablespace_name;
使用ALTER TABLESPACE命令，一但完成，所增加的空间就可使用，无需退出数据库或使表空间脱机，但要注意，一旦添加了数据文件，就不能再删除它，若要删除，就要删除表空间。
一个报错例子如下：
ORA-1652:unable to extend temp segment by 207381 in tablespace TEMPSPACE
相应的英文如下：
Cause: Failed to allocate extent for temp segment in tablespace
Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace or create the object in another tablespace.
补充：
重建Temp表空间
startup --启动数据库 create temporary tablespace TEMP2 TEMPFILE ‘/home2/oracle/oradata/sysmon/temp02.dbf’ SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --创建中转临时表空间 alter database default temporary tablespace temp2; --改变缺省临时表空间 为刚刚创建的新临时表空间temp2 drop tablespace temp including contents and datafiles;–删除原来临时表空间 create temporary tablespace TEMP TEMPFILE ‘/home2/oracle/oradata/sysmon/temp01.dbf’ SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED; --重新创建临时表空间 alter database default temporary tablespace temp; --重置缺省临时表空间为新建的temp表空间 drop tablespace temp2 including contents and datafiles;–删除中转用临时表空间 alter user roll temporary tablespace temp; --重新指定用户表空间为重建的临时表空间
 1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。 |
标签： #oracle
