【案例分析】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/，如需轉載，請註明出處，否則將追究法律責任。