解决临时表空间的报错

开发 开发工具
今天主要讲讲如何解决临时表空间的报错。

报错信息:[HY000](1652) [Oracle][ODBC][Ora]ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

原因分析:oracle临时表空间不足,事务执行一般将会报ora-01652无法扩展临时段的错误。因为oracle总是尽量分配连续空间,一旦没有足够的可分配空间或者分配不连续就会出现上述的现象。

回忆下临时表空间的作用:

(1)临时表空间主要用途是在数据库进行排序运算[如创建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、管理索引[如创建索引、IMP进行数据导入]、访问视图等操作时提供临时的运算空间,当运算完成之后系统会自动清理。

(2)当临时表空间不足时,表现为运算速度异常的慢,并且临时表空间迅速增长到***空间(扩展的极限),并且一般不会自动清理了。

解决办法:我们知道由于ORACLE将表空间作为逻辑结构-单元,而表空间的物理结构是数据文件,数据文件在磁盘上物理地创建,表空间的所有对象也存在于磁盘上,为了给表空间增加空间,就必须增加数据文件。先查看一下指定表空间的可用空间,使用视图SYS.DBA_FREE_SPACE,视图中每条记录代表可用空间的碎片大小。当然也可以扩展表空间。

1、增大临时表空间(或增加临时表空间文件)。

2、设置临时数据文件自动扩展

临时表空间

步骤:

1、 查询临时表空间状态:

  1. SQL> col file_name for a20; 
  2. SQL> select tablespace_name,file_name,bytes/1024/1024file_size,autoextensible from dba_temp_files;  

查询临时表空间状态

 

2、 扩展临时表空间

  1. SQL> alter database tempfile '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf'resize 8192m; 

或也可增加临时表空间文件

  1. alter tablespace temp add tempfile‘/u01/app/oracle/oradata/CP7PV1DB/temp02.dbf’ size 8192m; 

注:临时表空间文件如果已经32G,达到***文件大小,只能添加文件。

3、 设置自动扩展

  1. SQL> alter database tempfile'/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' autoextend on next 10m maxsizeunlimited; 

4、 扩展表空间时的报错

  1. ERROR atline 1: 
  2. ORA-00376:file 201 cannot be read at this time 
  3. ORA-01110:data file 201: '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' 

原因是临时表空间不知道什么原因offline了,修改为online后修改成功。

  1. SQL>  alter database tempfile ‘/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf’online; 
  2. Database altered. 

5、 删除临时表空间(补充)

  1. SQL>drop tablespace temp01  including contents and datafiles; 
  2. SQL> ALTER DATABASE TEMPFILE  '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' DROPINCLUDING DATAFILES; 
  3. Database altered. 

注意:删除临时表空间的临时数据文件时,不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件,否则需要手工删除物理文件。也不能直接删除当前用户的默认表空间,否则会报ORA-12906错误。如果需要删除某一个默认的临时表空间,则必须先创建一个临时表空间,然后指定新创建的表空间为默认表空间,然后删除原来的临时表空间。

6、 更改系统默认的临时表空间

  1. --查询默认临时表空间 
  2. SQL> select *from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'
  3. PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION 
  4. -------------------------------------------------- -------------------------------------------------- 
  5. DEFAULT_TEMP_TABLESPACE        TEMP       Name of default temporary tablespace 
  6. --修改默认临时表空间 
  7. SQL> alterdatabase default temporary tablespace temp02; 
  8. Databasealtered. 

我们可以查询是否切换为TEMP02:

  1. SQL> select *from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'
  2. PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION 
  3. -------------------------------------------------- ---------------------------------------- 
  4. DEFAULT_TEMP_TABLESPACE        TEMP02     Name of default temporary tablespace 

7、 查看临时表空间的使用率

  1. SQL>SELECT temp_used.tablespace_name, 
  2.            total - used as "Free", 
  3.            total as "Total", 
  4.            round(nvl(total - used, 0) * 100 /total, 3) "Free percent" 
  5.       FROM (SELECT tablespace_name,SUM(bytes_used) / 1024 / 1024 used 
  6.               FROM GV_$TEMP_SPACE_HEADER 
  7.              GROUP BY tablespace_name) temp_used, 
  8.            (SELECT tablespace_name, SUM(bytes) /1024 / 1024 total 
  9.               FROM dba_temp_files 
  10.            GROUP BY tablespace_name) temp_total 
  11.     WHEREtemp_used.tablespace_name = temp_total.tablespace_name; 
  12.   
  13. TABLESPACE_NAME                      Free      Total Free percent 
  14. ---------------------------------------- ---------- ------------ 
  15. TEMP                                 6876       8192       83.936 

8、 查找消耗资源比较多的sql语句

  1. Select se.username, 
  2.        se.sid, 
  3.        su.extents, 
  4.        su.blocks * to_number(rtrim(p.value)) asSpace, 
  5.        tablespace, 
  6.        segtype, 
  7.        sql_text 
  8.   from v$sort_usage su, v$parameter p, v$session se, v$sql s 
  9.  where p.name = 'db_block_size' 
  10.    and su.session_addr = se.saddr 
  11.    and s.hash_value = su.sqlhash 
  12.    and s.address = su.sqladdr 
  13.  order by se.username, se.sid; 

【本文为51CTO专栏作者“孙杰”原创稿件,转载请联系原作者】

责任编辑:赵宁宁 来源: 51CTO博客
相关推荐

2011-05-20 15:50:06

oracle

2010-11-29 14:47:47

Sybase临时表

2010-04-02 18:01:39

Oracle清理

2010-03-29 17:03:48

Oracle创建

2022-10-19 21:24:24

临时表空间Oracle

2011-08-11 18:38:05

Oracle回滚段

2011-07-04 10:33:45

临时表空间排序

2022-01-26 07:18:57

oracle临时表空间数据库

2009-07-02 00:26:00

临时表空间Oracle数据库性能

2011-04-13 13:56:52

Oracle临时表

2010-11-22 15:19:28

Mysql临时表

2010-07-08 14:24:21

SQL Server

2010-11-03 09:34:39

DB2临时表

2011-09-02 14:45:43

Oracle临时表SQL Server临

2011-03-16 09:42:27

Oracle临时表

2010-10-19 14:45:01

SQL SERVER临

2010-04-26 13:38:34

Oracle dele

2010-09-16 15:10:48

SQL Server表

2011-03-29 13:22:07

SQL Server临时表表变量

2011-08-24 14:21:44

Oracle 10gUNDO表空间
点赞
收藏

51CTO技术栈公众号