Ora-25153:temporary Tablespace is Empty

The following error occurred while doing the test in a test environment:

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 Emptyora-06512:at "SYS. Dbms_stats ", line 24281ora-06512:at" SYS. Dbms_stats ", line 24332ora-06512:at line 1sql>

View Temporary tablespace: Temp table space is in online state

Sql> select Tablespace_name,status from Dba_tablespaces; Tablespace_name                STATUS---------------------------------------SYSTEM                         onlinesysaux                         ONLINEUNDOTBS1                       onlinetemp                           Onlineusers                          onlineexample                        onlinetest                           ONLINE7 rows selected. Sql>

However, no temporary files were found:

Sql> select name from V$tempfile;    --Find temporary files missing no rows selected

View Physical files: Arts and Sciences files still exist

$ lltotal 2678324-rw-r-----1 ora11 oinstall  10076160 Oct 14:39 control01.ctl-rw-r-----1 ora11 oinstall  100761 Oct 14:39 control02.ctl-rw-r-----1 ora11 oinstall 363077632 Oct 14:33 example01.dbf-rw-r-----1 ora11 oinstall
   52429312 Oct 14:33 redo01.log-rw-r-----1 ora11 oinstall 52429312  Oct 14:33 redo02.log-rw-r-----1 ora11 oins  Tall  52429312 Oct 14:38 redo03.log-rw-r-----1 ora11 oinstall 587210752 Oct 14:38 sysaux01.dbf-rw-r-----1 ora11 Oinstall 922755072 Oct 14:38 system01.dbf-rw-r-----1 ora11 oinstall  62922752 in 06:02 temp01.dbf-rw-r-----1 Ora11 oinstall  52436992 Oct 14:33 test01.dbf

The last access time of the temp file can be seen from the time stamp of the physical file

Depending on the time the alert log is viewed, a Rebuild control file operation is found at the corresponding time, and the following is found in the alert log after the rebuild control is completed:

Warning:the following temporary tablespaces contain no files.         This condition can occur when a backup controlfile have         been restored.  It is necessary to add files to these         tablespaces.  That can is done using the SQL statement:          ALTER tablespace <tablespace_name> ADD tempfile          Alternatively, if These temporary tablespaces is no longer         needed, then they can dropped.           Empty Temporary tablespace:temp*********************************************************************

Description is the temporary file that was lost after the control file was rebuilt. (Similar behavior can occur when Rman recovers a database)

Just add the temporary files again.

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 NA ME------------------------------------------------------------------------------------------------------------- ---------------------------------------------1 1727770 25-oct-16 3 1 ONLINE READ WRI TE 62914560 7680 62914560 8192/u01/app/oracle/oradata/db11/temp01.dbfsql> Select Tablespace_name,fil E_name from Dba_temp_files; Tablespace_name file_name--------------------------------------------------------------------------------- ----------------------------------------temp/u01/app/oracle/oradata/db11/temp01.dbfsql> exe C Dbms_stats.gather_table_stats (ownname=> ' SYS ',tabname=> ' TT ',method_opt=> ' for columns size 1 ID ', cascade=>true);P L/sql procedure successfully completed.  Sql>

　　

Ora-25153:temporary Tablespace is Empty