Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Temp tbs with no temp file

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US