sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

案例:Oracle报错ORA-01652 分析永久表空间出现临时段不能扩展原因

时间:2016-06-22 18:37   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 开发DBA反映,数据库alert日志报错ORA-01652,分析数据库的表空间为永久表空间,出现临时段不能自动扩展

1.查询Oracle数据库版本

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建5M测试表空间

SQL> CREATE TABLESPACE T_1652 DATAFILE '/tmp/t_1652_01.dbf' size 5M 
  2  AUTOEXTEND OFF LOGGING PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
  3  SEGMENT SPACE MANAGEMENT AUTO blocksize 8192;

Tablespace created.

测试CTAS

SQL> create table CHF.T_oracleplus TABLESPACE T_1652 as
  2  select LPAD('oracleplus',1024,'F') "C_XFF" from  dual connect by level <=3500;
create table CHF.T_oracleplus TABLESPACE T_1652 as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652


SQL> create table CHF.T_oracleplus TABLESPACE T_1652 as
  2  select LPAD('oracleplus',1024,'F') "C_XFF" from  dual connect by level <=3000;

Table created.

测试CREATE INDEX

SQL> create index chf.i_oracleplus on chf.t_oracleplus(c_xff)
  2  tablespace t_1652;
create index chf.i_oracleplus on chf.t_oracleplus(c_xff)
                                   *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace T_1652


SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;

TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES  
----------- ---------- ------------------------- ---------- 
    5242880          0 /tmp/t_1652_01.dbf           5242880

SQL> drop table chf.t_oracleplus purge;

Table dropped.

SQL> create table CHF.T_oracleplus TABLESPACE T_1652 as
  2  select LPAD('oracleplus',1024,'F') "C_XFF" from  dual connect by level <=2000;

Table created.

SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;


TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES   
----------- ---------- ------------------------- ---------- 
    5242880    1048576 /tmp/t_1652_01.dbf           4194304  

SQL> create index chf.i_oracleplus on chf.t_oracleplus(c_xff)
  2  tablespace t_1652;
create index chf.i_oracleplus on chf.t_oracleplus(c_xff)
                                   *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652

SQL> ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 10M;

Database altered.

SQL> create index chf.i_oracleplus on chf.t_oracleplus(c_xff)
  2  tablespace t_1652;

Index created.

测试MOVE

SQL> drop table chf.t_oracleplus purge;

Table dropped.

SQL> create table CHF.T_oracleplus TABLESPACE T_1652 as
  2  select LPAD('oracleplus',1024,'F') "C_XFF" from  dual connect by level <=3500;

Table created.

SQL> alter table chf.t_oracleplus move;
alter table chf.t_oracleplus move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T_1652

SQL> Select MAX(d.bytes) total_bytes,
  2         nvl(SUM(f.Bytes), 0) free_bytes,
  3         d.file_name,
  4         MAX(d.bytes) - nvl(SUM(f.bytes), 0) used_bytes,
  5  from   DBA_FREE_SPACE f , DBA_DATA_FILES d
  6  where  f.tablespace_name(+) = d.tablespace_name
  7  and    f.file_id(+) = d.file_id
  8  and    d.tablespace_name = 'T_1652'
  9  group by d.file_name;

TOTAL_BYTES FREE_BYTES FILE_NAME                 USED_BYTES 
----------- ---------- ------------------------- ----------
   10485760    4194304 /tmp/t_1652_01.dbf           6291456  

SQL>  ALTER DATABASE DATAFILE '/tmp/t_1652_01.dbf' RESIZE 15M;

Database altered.

SQL> alter table chf.t_oracleplus move;

Table altered.

2.Oracle报错ORA-01652分析结论

这里可以发现CTAS,CREATE INDEX,MOVE操作都有个共同点:需要一次性创建一个较大SEGMENT,但是这个SEGMENT的创建过程是在数据库中逐渐实现(非初始化指定大小)。
也就是说,ORACLE对这些对象的处理方法是:对于这样的segment先当作临时段处理,当处理完成后,再把这些在永久表空间中的临时段转换为永久段;所以当这些永久表空间中的临时段在扩展的时候,遇到该永久表空间不足,而该段目前还是临时段(在永久表空间中的临时段),就出现了ORA-01652提示一个永久表空间unable to extend temp segment


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle报错ORA-01652 分析永久表空间出现临时段不能扩展原因

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/483.html

Oracle研究中心

关键词:

Oracle报错ORA-01652

分析Oracle数据库永久表空间出现临时段不能扩展原因