ORA-1652: unable to extend temp segment

1.问题描述

检查alert日志发现近期大量ORA-1652报错

截止上午10:00近2天报错次数为19次

Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace                 TEMP1 
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace                 TEMP1 
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace                 TEMP1 
Wed Feb 17 01:32:03 2016
ORA-1652: unable to extend temp segment by 16 in tablespace                 TEMP1 

Tue Feb 16 23:32:04 2016
ORA-1652: unable to extend temp segment by 16 in tablespace                 TEMP1 
Tue Feb 16 23:32:04 2016
ORA-1652: unable to extend temp segment by 16 in tablespace                 TEMP1 
Tue Feb 16 23:32:14 2016
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2 
Tue Feb 16 23:32:14 2016
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2 
Tue Feb 16 23:45:20 2016

2.问题点分析

1.> 默认表空间问题：

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES  WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
 
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP
 
SQL> 

经查询默认临时表空间是TEMP

但是erp库中根本没有temp表空间

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
 
TABLESPACE_NAME
------------------------------
TEMP1
TEMP2
PROD2_IAS_TEMP
PROD1_IAS_TEMP
SQL> select file_name,file_id, tablespace_name from dba_temp_files;
 
FILE_NAME                                                                           FILE_ID TABLESPACE_NAME
-------------------------------------------------------------------------------- ---------- ------------------------------
+DATA_ERP/prod/tempfile/temp1.302.835913167                                               3 TEMP1
+DATA_ERP/prod/tempfile/temp2.303.835913167                                               4 TEMP2
+DATA_ERP/prod/tempfile/prod1_ias_temp.512.895320743                                      1 PROD1_IAS_TEMP
+DATA_ERP/prod/tempfile/prod2_ias_temp.501.868628877                                      2 PROD2_IAS_TEMP
 
SQL> 

2.> TEMP1和TEMP2报空间不足的问题

SQL> SELECT A.tablespace_name tablespace,
  2             D.mb_total,
  3         SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
  4         D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
  5  FROM v$sort_segment A,
  6             (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
  7                FROM v$tablespace B, v$tempfile C
  8           WHERE B.ts# = C.ts#
  9           GROUP BY B.name, C.block_size) D
 10  WHERE A.tablespace_name = D.name
 11  GROUP by A.tablespace_name, D.mb_total;
 
 
TABLESPACE                        MB_TOTAL    MB_USED    MB_FREE
------------------------------- ---------- ---------- ----------
TEMP1                                 4000      28.75    3971.25
TEMP2                                 4000        147       3853
 
SQL>

3.解决方案

1.> 针对默认临时表空间问题，建议添加默认临时表空间temp

但是出现了奇怪的问题，居然报错TEMP已存在

SQL> create temporary tablespace TEMP tempfile '+DATA_ERP/prod/tempfile/temp.dbf' size 4096M ;
 
create temporary tablespace TEMP tempfile '+DATA_ERP/prod/tempfile/temp.dbf' size 4096M
 
ORA-01543: tablespace 'TEMP' already exists
 
SQL> alter tablespace TEMP add tempfile '+DATA_ERP' size 2048M;
 
alter tablespace TEMP add tempfile '+DATA_ERP' size 2048M
 
ORA-10917: TABLESPACE GROUP cannot be specified

这是临时表空间组，好吧，没想到

SQL> select * from dba_tablespace_groups;
 
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP                           TEMP1
TEMP                           TEMP2
 
SQL>

2.> 针对短时间内报错问题

临时表空间4G太小了，改为自动扩展

SQL> alter database tempfile '+DATA_ERP/prod/tempfile/temp1.302.835913167' autoextend on next 100M;
 
Database altered
SQL> 
SQL> alter database tempfile '+DATA_ERP/prod/tempfile/temp2.303.835913167' autoextend on next 100M;
 
Database altered
 
SQL> select FILE_NAME, TABLESPACE_NAME,   BYTES,   AUTOEXTENSIBLE,  INCREMENT_BY from dba_temp_files;
 
FILE_NAME                                                                        TABLESPACE_NAME                     BYTES AUTOEXTENSIBLE INCREMENT_BY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------
+DATA_ERP/prod/tempfile/temp1.302.835913167                                      TEMP1                          4194304000 YES                   12800
+DATA_ERP/prod/tempfile/temp2.303.835913167                                      TEMP2                          4194304000 YES                   12800
+DATA_ERP/prod/tempfile/prod1_ias_temp.512.895320743                             PROD1_IAS_TEMP                  104857600 YES                    6400
+DATA_ERP/prod/tempfile/prod2_ias_temp.501.868628877                             PROD2_IAS_TEMP                  104857600 YES                    6400
 
SQL>