在一测试环境做测试的时候发生以下错误:
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TT',method_opt=>'for columns size 1 id',cascade=>true);
BEGIN dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TT',method_opt=>'for columns size 1 id',cascade=>true); END;
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1
SQL>
查看临时表空间:temp表空间是处于online状态
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TEST ONLINE
7 rows selected.
SQL>
但是没有发现临时文件:
SQL> select name from v$tempfile; --发现临时文件丢失
no rows selected
查看物理文件:文理文件仍然存在
$ ll
total 2678324
-rw-r----- 1 ora11 oinstall 10076160 Oct 25 14:39 control01.ctl
-rw-r----- 1 ora11 oinstall 10076160 Oct 25 14:39 control02.ctl
-rw-r----- 1 ora11 oinstall 363077632 Oct 25 14:33 example01.dbf
-rw-r----- 1 ora11 oinstall 52429312 Oct 25 14:33 redo01.log
-rw-r----- 1 ora11 oinstall 52429312 Oct 25 14:33 redo02.log
-rw-r----- 1 ora11 oinstall 52429312 Oct 25 14:38 redo03.log
-rw-r----- 1 ora11 oinstall 587210752 Oct 25 14:38 sysaux01.dbf
-rw-r----- 1 ora11 oinstall 922755072 Oct 25 14:38 system01.dbf
-rw-r----- 1 ora11 oinstall 62922752 Aug 14 06:02 temp01.dbf
-rw-r----- 1 ora11 oinstall 52436992 Oct 25 14:33 test01.dbf
从物理文件的时间戳可以看出temp文件最后一次访问时间
根据时间查看alert日志,发现在对应的时间做个重建控制文件操作,并且在重建控制完成后启动数据库后在alert日志中发现以下内容:
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
说明是重建控制文件后,丢失了临时文件。(rman恢复数据库时也会发生类似现象)
只要把临时文件重新添加即可。
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/db11/temp01.dbf';
Tablespace altered.
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ----------------------------------------
1 1727770 25-OCT-16 3 1 ONLINE READ WRITE 62914560 7680 62914560 8192 /u01/app/oracle/oradata/db11/temp01.dbf
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------------------------------------------------------------------
TEMP /u01/app/oracle/oradata/db11/temp01.dbf
SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TT',method_opt=>'for columns size 1 id',cascade=>true);
PL/SQL procedure successfully completed.
SQL>