【案例分析】ORA-25153: Temporary Tablespace is Empty

问题描述：

执行

SQL> select dbms_metadata.get_ddl('TABLE',table_name,owner)||';'  from dba_tables where wner='THSIMIS' and table_name='AC01_T';
ERROR:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_METADATA", line 2729
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

查看

SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
DATA_K_8                       ONLINE
INDEX_COM                      ONLINE
INDEX_AC20                     ONLINE
INDEX_K                        ONLINE
USER_HIS                       ONLINE
DATA_ARC20_1                   ONLINE
DATA_ARC20_2                   ONLINE
DATA_ARC20_3                   ONLINE
DATA_ARC20_4                   ONLINE
DATA_ARC20_5                   ONLINE
DATA_ARC20_6                   ONLINE
DATA_ARC20_7                   ONLINE
DATA_ARC20_8                   ONLINE
U2                             ONLINE
DATA_K_1                       ONLINE
DATA_K_2                       ONLINE
DATA_K_3                       ONLINE
DATA_K_4                       ONLINE
DATA_K_5                       ONLINE
DATA_K_6                       ONLINE
DATA_K_7                       ONLINE

26 rows selected.

发现temp仍然为online。

SQL> select tablespace_name, file_name from dba_temp_files;

no rows selected

分析：

两种情况，

1、OS level的physical datafile丢失，

2、logical tempfile丢失

查看物理文件，存在。那属于第二种情况了。

SQL> alter tablespace temp add tempfile 'D:\work_tools\oracle\product\10.2.0\oradata\dxm\TEMP01.DBF';

SQL> select dbms_metadata.get_ddl('TABLE',table_name,owner)||';'  from dba_tables where wner='USER' and table_name='AC01_T';

DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER)||';'
--------------------------------------------------------------------------------

  CREATE TABLE "USER"."AC01_T"
   (    "OAE001" NUMBER(18,0) NOT NULL ENABLE,
OK问题解决。

若OS level的physical datafile丢失，

SQL> alter tablespace temp add tempfile 'D:\work_tools\oracle\product\10.2.0\oradata\dxm\TEMP01.DBF' size 5m;

Tablespace altered.

SQL>  select dbms_metadata.get_ddl('TABLE',table_name,owner)||';'  from dba_tables where wner='THSIMIS' and table_name='AC01_T';

DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER)||';'
--------------------------------------------------------------------------------

  CREATE TABLE "THSIMIS"."AC01_T"
   (    "OAE001" NUMBER(18,0) NOT NULL ENABLE,

 

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

删除temp01.dbf，然后启动数据库，在启动的过程中自动创建了temp01.dbf。

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1248552 bytes
Variable Size              92275416 bytes
Database Buffers          184549376 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
SQL>  select dbms_metadata.get_ddl('TABLE',table_name,owner)||';'  from dba_tables where wner='THSIMIS' and table_name='AC01_T';

DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER)||';'
--------------------------------------------------------------------------------

  CREATE TABLE "THSIMIS"."AC01_T"
   (    "OAE001" NUMBER(18,0) NOT NULL ENABLE,

SQL>

 

 

 

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

转载于:http://blog.itpub.net/13726712/viewspace-715826/