[小e笔记]之错误案例——ORA-25153错误

错误描述如下：

SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;

ERROR:

ORA-25153: Temporary Tablespace is Empty

ORA-06512: at "SYS.DBMS_LOB", line 664

ORA-06512: at "SYS.DBMS_METADATA", line 3825

ORA-06512: at "SYS.DBMS_METADATA", line 5678

ORA-06512: at line 1

no rows selected

解决方法：  --很简单解决的

(1)故障定位

SQL> select name from v$tempfile;    --发现临时文件丢失

no rows selected

也会从警告日志中可以看出确实临时文件

SMON: enabling tx recovery

*********************************************************************

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

*********************************************************************

(2)指定临时文件

SQL> alter tablespace temp add tempfile '/u01/oracle/oradata/elvis/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           920166 17-OCT-12          3          1 ONLINE  READ WRITE

  71303168       8704     71303168       8192

/u01/oracle/oradata/elvis/temp01.dbf

SQL> set long 9999;

SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST')

--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."TEST"

   (    "ID" NUMBER(*,0),

        "SCN" NUMBER(*,0)

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST"

OK没有问题了

总结：临时文件丢失不会影响运行中的数据库，但临时文件还是必不可少的，因为Oracle使用临时文件来存储大规模排序操作和散列操作的中间结果等等作用，如果临时文件丢失，完全可以在创建一个，而且临时文件丢失不会有数据丢失的情况。因为永久数据对象不会存储在临时文件中，总之Oracle既然创造了临时文件，就有它特有的意义。

 

elvis

2012.10.20

知识共享~共同进步

转载请注明：

 http://blog.csdn.net/elvis_dataguru/article/details/8092095