今天,执行下面语句时,报错:
SQL> set autotrace on explain;
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-25153: Temporary Tablespace is Empty
SP2-0612: Error generating AUTOTRACE EXPLAIN report
上网查资料,看到这样一句话:"我数据的版本是10.2.0.1,之前做过控制文件恢复,导致临时表空间为空,|这是10g的一个bug,在11g里已经没有这个问题"
查看临时文件:
SQL> select tablespace_name,file_name from dba_temp_files;
未选定行
查看临时表空间:
SQL> select tablespace_name,status
2 from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE
解决办法:
给临时表空间添加数据文件:
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' reuse;
再执行上面的语句,不再报错了.