Temp tbs with no temp file

From: Sinardy <all_about_oracle_at_hotpop.com>
Date: Fri, 1 Jul 2005 18:15:19 +0800
Message-ID: <001e01c57e25$d9c9add0$0400a8c0@ncs.corp.intads>

Hi all,

Why Oracle allow to have temporary tablespace without the temp file?

The error is:
ORA-25153: Temporary Tablespace is Empty

---

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

---

SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE 6 rows selected.

---

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME


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

USERS                          C:\MYORACLE\ORADATA\ORA10G\USERS01.DBF
SYSAUX                         C:\MYORACLE\ORADATA\ORA10G\SYSAUX01.DBF
UNDOTBS1                       C:\MYORACLE\ORADATA\ORA10G\UNDOTBS01.DBF
SYSTEM                         C:\MYORACLE\ORADATA\ORA10G\SYSTEM01.DBF
EXAMPLE                        C:\MYORACLE\ORADATA\ORA10G\EXAMPLE01.DBF


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

SQL> select tablespace_name, file_name from dba_temp_files;

no rows selected

---

SQL> host dir c:\myoracle\oradata\ora10g\*.dbf  Volume in drive C has no label.
 Volume Serial Number is 3C1E-E269

 Directory of c:\myoracle\oradata\ora10g

07/01/2005  03:26 PM       157,294,592 EXAMPLE01.DBF
07/01/2005  03:26 PM       325,066,752 SYSAUX01.DBF
07/01/2005  03:26 PM       545,267,712 SYSTEM01.DBF
05/24/2005  05:01 PM     5,033,172,992 TEMP01.DBF
07/01/2005  03:26 PM       120,594,432 UNDOTBS01.DBF
07/01/2005  03:26 PM        44,572,672 USERS01.DBF
               6 File(s)  6,225,969,152 bytes
               0 Dir(s)   9,138,454,528 bytes free

SQL>

---

If you notice I have the temp01.dbf, the last time online is May 24th

Why Oracle allow to have temporary tablespace without the temp file?

What should I do?

regards,
Sinardy

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 01 2005 - 06:22:07 CDT