不要在生产上随意drop零时表空间数据文件

背景：
adg备库报temp表空间无法扩展，但用sql查看temp表空间使用率很低，同事尝试删除主库零时表空间数据库文件，再重新添加。

alter database tempfile '+DATA/newuums/tempfile/temp.392.918939815' drop including datafiles;
删除的时候报错，未删除成功，后台alert报ORA-1652: unable to extend temp segment by 128 in tablespace
给temp表空间新增数据文件后，后台报错停止

alert日志报错如下：
Mon Jan 09 11:17:32 2017
Archived Log entry 38929 added for thread 1 sequence 38202 ID 0x5035a9e8 dest 1:
Mon Jan 09 11:18:29 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:19:29 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:19:44 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:19:59 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:20:00 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:20:01 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:20:14 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:20:29 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:20:44 2017
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP
Mon Jan 09 11:20:46 2017
ALTER TABLESPACE temp ADD TEMPFILE '+DATA' size 16g autoextend off
Completed: ALTER TABLESPACE temp ADD TEMPFILE '+DATA' size 16g autoextend off



但是应用层报错如下（之前drop的零时表空间数据文件找不到）：
ORA-00376: file 203 cannot be read at this time
ORA-01110: data file 203: '+DATA/newuums/tempfile/temp.392.918939815'
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 31
ORA-06512: at "UUMSUSER.PRO_CHAIN_EMPCHG", line 155




查看发现该零时表空间数据文件是offline状态
SQL> select file_name,FILE_ID,STATUS from dba_temp_files;


FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID STATUS
---------- -------
+DATA/newuums/tempfile/temp.392.918939815
3 OFFLINE


+DATA/newuums/tempfile/patrol_temp.393.918939815
4 ONLINE


+DATA/newuums/tempfile/temp.424.932815247
1 ONLINE




SQL> alter database tempfile '+DATA/newuums/tempfile/temp.392.918939815' online;


Database altered.


SQL> select file_name,FILE_ID,STATUS from dba_temp_files;


FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID STATUS
---------- -------
+DATA/newuums/tempfile/temp.392.918939815
3 ONLINE


+DATA/newuums/tempfile/patrol_temp.393.918939815
4 ONLINE


+DATA/newuums/tempfile/temp.424.932815247
1 ONLINE
 
 
set line 300
col file_name for a60
col tablespace for a30  
select file_name, FILE_ID, TABLESPACE_NAME, STATUS
  from dba_temp_files
 where status <> 'ONLINE'
union all
select file_name, FILE_ID, TABLESPACE_NAME, STATUS
  from dba_temp_files
 where status <> 'ONLINE';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29953799/viewspace-2132439/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29953799/viewspace-2132439/