ORA-1652 unable to extend temp tablespace临时表空间无法自动扩展发现的奇葩设置
Jul142015
在监控UAT数据库告警日志的时候，发现出现临时表空间无法自动扩展的情况。
Wed Jul 08 13:15:28 2015 ORA-1652: unable to extend temp segment by 128 in tablespace TEMP Wed Jul 08 13:15:28 2015 ORA-1652: unable to extend temp segment by 128 in tablespace TEMP Wed Jul 08 13:15:28 2015 ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
接手这个数据库虽然不长时间，但是我还是知道这个数据库创建了一个由3个临时表空间组成的临时表空间组，虽然每个临时表空间只有一个数据文件，但临时表空间组一共也有96GB，案例说不应该出现这个问题才是，数据库版本11.2.0.4。
sys@UATDB(10.0.97.33)> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ ------------------------------ IVALUE_TEMP IVALUE_TEMP1 IVALUE_TEMP IVALUE_TEMP2 IVALUE_TEMP IVALUE_TEMP3
观察报错信息发现，报错的竟然不是临时表空间组，而是数据库自带的TEMP临时表空间，难道业务用户使用的不是临时表空间组？
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ -------------------- -------------------- MIGJOB TS_MIG TEMP MIGBI TS_MIG TEMP IVLMC TS_MC TEMP PRODTASK TS_TASK TEMP CHGSHSIF TS_FUL_IF TEMP IVL_DEBUG TS_FUL TEMP BI_PLAN TS_BSC TEMP CHGSHS TS_BSC TEMP BK_IVL TS_BSC TEMP VOUCHER TS_BSC TEMP IVL_READ USERS TEMP ZABBIX TS_TASK IVALUE_TEMP IVL_PROD USERS IVALUE_TEMP EXPBAK USERS IVALUE_TEMP MONITOR USERS IVALUE_TEMP IVL_UAT USERS IVALUE_TEMP MONITOR1 USERS IVALUE_TEMP ORACLE_OCM USERS IVALUE_TEMP XS$NULL USERS IVALUE_TEMP MDDATA USERS IVALUE_TEMP DIP USERS IVALUE_TEMP APEX_PUBLIC_USER USERS IVALUE_TEMP SPATIAL_CSW_ADMIN_USR USERS IVALUE_TEMP SPATIAL_WFS_ADMIN_USR USERS IVALUE_TEMP DBSNMP SYSAUX IVALUE_TEMP FLOWS_FILES SYSAUX IVALUE_TEMP MDSYS SYSAUX IVALUE_TEMP ORDSYS SYSAUX IVALUE_TEMP EXFSYS SYSAUX IVALUE_TEMP WMSYS SYSAUX IVALUE_TEMP APPQOSSYS SYSAUX IVALUE_TEMP APEX_030200 SYSAUX IVALUE_TEMP OWBSYS_AUDIT SYSAUX IVALUE_TEMP ORDDATA SYSAUX IVALUE_TEMP CTXSYS SYSAUX IVALUE_TEMP ANONYMOUS SYSAUX IVALUE_TEMP XDB SYSAUX IVALUE_TEMP ORDPLUGINS SYSAUX IVALUE_TEMP OWBSYS SYSAUX IVALUE_TEMP SI_INFORMTN_SCHEMA SYSAUX IVALUE_TEMP OLAPSYS SYSAUX IVALUE_TEMP SYS SYSTEM IVALUE_TEMP SYSTEM SYSTEM IVALUE_TEMP OUTLN SYSTEM IVALUE_TEMP
查询发现，就是这么另类，临时表空间组居然是系统用户使用的临时表空间，而业务用户使用的是TEMP临时表空间，这个表空间也只有一个数据文件，最大32GB，这么做完全是可以的，可是从我的思路及数据库维护的经验来看，这完全属于不按套路出牌那种，完全搞不懂韩国人这么做的目的。
我只能猜测，韩国人肯定是在创建完临时表空间组的时候，将这个临时表空间组设置成了数据库默认临时表空间，所以系统用户的临时表空间就全变成了这个临时表空间组，而业务用户在创建的时候，又指定了默认临时表空间为TEMP。经查询，IVALUE_TEMP临时表空间组真的被设置成了数据库默认的临时表空间。
PROPERTY_VALUE PROPERTY_NAME ------------------------------ -------------------------------------------------- IVALUE_TEMP DEFAULT_TEMP_TABLESPACE USERS DEFAULT_PERMANENT_TABLESPACE
那么是不是创建用户的时候，这个临时表空间组还没有创建，制定的DEFAULT TABLESPACE TEMP选项呢？查看下用户的创建时间和临时表空间的创建时间。
SQL> select a.file_name, 2 a.tablespace_name, 3 creation_time 4 from dba_temp_files a, v$datafile b 5 where a.file_id = b.file# 6 order by tablespace_name; FILE_NAME TABLESPACE_NAME CREATION_TIME ---------------------------------------- -------------------- ------------------- /data/u01/uatdb/ivalue_temp1.dbf IVALUE_TEMP1 2015-03-28 06:58:28 /data/u01/uatdb/ivalue_temp2.dbf IVALUE_TEMP2 2015-03-28 07:13:25 /data/u01/uatdb/ivalue_temp3.dbf IVALUE_TEMP3 2015-03-28 07:29:10 /data/u01/uatdb/temp01.dbf TEMP 2015-03-28 05:17:06
可见这个临时表空间组最早创建的时间是2015年3月28日早7点半，下面看下用户的创建时间。
SQL> SELECT USERNAME,CREATED,TEMPORARY_TABLESPACE FROM DBA_USERS ORDER BY CREATED; USERNAME CREATED TEMPORARY_TABLESPACE ------------------------------------------------------------ ------------------- ----------------------- SYS 2015-03-28 05:16:58 IVALUE_TEMP SYSTEM 2015-03-28 05:16:59 IVALUE_TEMP OUTLN 2015-03-28 05:17:00 IVALUE_TEMP DIP 2015-03-28 05:18:16 IVALUE_TEMP ORACLE_OCM 2015-03-28 05:19:14 IVALUE_TEMP DBSNMP 2015-03-28 05:24:33 IVALUE_TEMP APPQOSSYS 2015-03-28 05:24:35 IVALUE_TEMP WMSYS 2015-03-28 05:25:11 IVALUE_TEMP EXFSYS 2015-03-28 05:29:17 IVALUE_TEMP CTXSYS 2015-03-28 05:29:27 IVALUE_TEMP XDB 2015-03-28 05:29:52 IVALUE_TEMP ANONYMOUS 2015-03-28 05:29:52 IVALUE_TEMP XS$NULL 2015-03-28 05:31:24 IVALUE_TEMP ORDPLUGINS 2015-03-28 05:31:40 IVALUE_TEMP ORDDATA 2015-03-28 05:31:40 IVALUE_TEMP SI_INFORMTN_SCHEMA 2015-03-28 05:31:40 IVALUE_TEMP MDSYS 2015-03-28 05:31:40 IVALUE_TEMP ORDSYS 2015-03-28 05:31:40 IVALUE_TEMP OLAPSYS 2015-03-28 05:35:01 IVALUE_TEMP MDDATA 2015-03-28 05:35:47 IVALUE_TEMP SPATIAL_WFS_ADMIN_USR 2015-03-28 05:38:19 IVALUE_TEMP SPATIAL_CSW_ADMIN_USR 2015-03-28 05:38:24 IVALUE_TEMP FLOWS_FILES 2015-03-28 05:38:39 IVALUE_TEMP APEX_PUBLIC_USER 2015-03-28 05:38:40 IVALUE_TEMP APEX_030200 2015-03-28 05:38:40 IVALUE_TEMP OWBSYS_AUDIT 2015-03-28 05:47:56 IVALUE_TEMP OWBSYS 2015-03-28 05:47:56 IVALUE_TEMP MIGBI 2015-03-28 07:42:32 TEMP CHGSHS 2015-03-28 09:06:36 TEMP CHGSHSIF 2015-03-28 09:06:36 TEMP VOUCHER 2015-03-28 09:06:37 TEMP PRODTASK 2015-03-28 09:06:37 TEMP IVLMC 2015-03-28 09:06:37 TEMP MIGJOB 2015-03-28 09:57:38 TEMP IVL_READ 2015-03-28 14:03:43 TEMP BK_IVL 2015-03-28 17:43:46 TEMP BI_PLAN 2015-03-30 16:40:24 TEMP MONITOR 2015-03-30 17:53:11 IVALUE_TEMP MONITOR1 2015-03-31 23:18:03 IVALUE_TEMP IVL_DEBUG 2015-04-01 04:01:55 TEMP ZABBIX 2015-04-02 09:39:35 IVALUE_TEMP IVL_UAT 2015-06-16 17:26:47 IVALUE_TEMP IVL_PROD 2015-06-16 17:53:25 IVALUE_TEMP EXPBAK 2015-06-25 09:52:44 IVALUE_TEMP OGG 2015-07-09 10:25:18 IVALUE_TEMP DBA_TEST 2015-07-10 16:16:52 IVALUE_TEMP 46 rows selected.
可见在创建用户的时候，这个临时表空间组已经存在了，那么为什么这些关键业务用户使用的是TEMP临时表空间呢？考虑到韩国人的技术水平，排除了故意这么设计可能，那么就只有一种可能，那就是在创建用户的时候，指定了默认的临时表空间，可韩国人不会这么二吧，明明创建了临时表空间组，还指定TEMP。那就应该是这些用户是通过逻辑迁移到这个数据库的，而且源库只有TEMP临时表空间，没有IVALUE_TEMP这个临时表空间组。
后来和同事讨论，最终确定就是这么回事，因为这个UAT数据库是我使用生产数据库的RMAN备份恢复出来的，生产数据库的用户是从上线之前的UAT迁移过去的(那个UAT已经不存在，无法取证)，而后创建的那些用户由于在创建的时候没有指定默认表空间，则使用数据库默认的临时表空间IVALUE_TEMP。这就导致了上文查询出的怪异现象，核心业务用户的临时表空间都是TEMP，其他用户的临时表空间都是IVALUE_TEMP临时表空间组。
ORA-1652 unable to extend temp tablespace临时表空间无法自动扩展发现的奇葩设置：等您坐沙发呢！
发表评论
