天萃荷净

开发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 分析永久表空间出现临时段不能扩展原因