Temp tbs with no temp file

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Temp tbs with no temp file

Temp tbs with no temp file

2005-07-01 - By Sinardy

Reply: 1 2 3 4 5 6 7 8 9 10 >>

Hi all,

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

The error is:
ORA-25153 (See ORA-25153.ora-code.com): 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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1479" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi all,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Why Oracle allow to have temporary tablespace
without the temp file?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The error is:</FONT></DIV>
<DIV><FONT face=Arial size=2>ORA-25153 (See ORA-25153.ora-code.com): Temporary Tablespace is
Empty<BR></DIV></FONT>
<DIV><FONT face=Arial
size=2>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- -----</FONT></DIV>
<DIV><FONT face=Arial size=2>SQL> select tablespace_name from
dba_tablespaces;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial
size=2>TABLESPACE_NAME<BR>-- ---- ---- ---- ---- ---- --<BR>SYSTEM<BR>UNDOTBS1
<BR>SYSAUX<BR>TEMP<BR>USERS<BR>EXAMPLE</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>6 rows selected.</FONT></DIV>
<DIV><FONT face=Arial
size=2>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- -----</FONT></DIV>
<DIV><FONT face=Arial size=2>SQL> select tablespace_name, file_name from
dba_data_files;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial
size=2>TABLESPACE_NAME         
      
FILE_NAME<BR>-- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- --<BR>USERS     
             
       
C:\MYORACLE\ORADATA\ORA10G\USERS01.DBF<BR>SYSAUX     
             
      
C:\MYORACLE\ORADATA\ORA10G\SYSAUX01.DBF<BR>UNDOTBS1    
             
     
C:\MYORACLE\ORADATA\ORA10G\UNDOTBS01.DBF<BR>SYSTEM    &nbsp
;             
      
C:\MYORACLE\ORADATA\ORA10G\SYSTEM01.DBF<BR>EXAMPLE    &nbsp
;             
     
C:\MYORACLE\ORADATA\ORA10G\EXAMPLE01.DBF</FONT></DIV>
<DIV><FONT face=Arial
size=2>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- -----</FONT></DIV>
<DIV><FONT face=Arial size=2>SQL> select tablespace_name, file_name from
dba_temp_files;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>no rows selected</FONT></DIV>
<DIV><FONT face=Arial
size=2>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- -----</FONT></DIV>
<DIV><FONT face=Arial size=2>SQL> host dir
c:\myoracle\oradata\ora10g\*.dbf<BR> Volume in drive C has no
label.<BR> Volume Serial Number is 3C1E-E269</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2> Directory of
c:\myoracle\oradata\ora10g</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>07/01/2005  03:26
PM       157,294,592
EXAMPLE01.DBF<BR>07/01/2005  03:26 PM      
325,066,752 SYSAUX01.DBF<BR>07/01/2005  03:26
PM       545,267,712 SYSTEM01.DBF<BR><STRONG>
<FONT
color=#ff0000>05/24/2005  05:01 PM     5,033,172,992
TEMP01.DBF</FONT><BR></STRONG>07/01/2005  03:26
PM       120,594,432
UNDOTBS01.DBF<BR>07/01/2005  03:26
PM        44,572,672
USERS01.DBF<BR>          
    
6 File(s)  6,225,969,152
bytes<BR>           
   
0 Dir(s)   9,138,454,528 bytes free</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SQL></FONT></DIV>
<DIV><FONT face=Arial
size=2>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
-- ---- ---- ---- ---- ---- ---- ---- -----</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>If you notice I have the temp01.dbf, the last time
online is May 24th </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial size=2>Why Oracle allow to have temporary tablespace
without the temp file?</FONT></DIV>
<DIV> </DIV>
<DIV>What should I do?</DIV>
<DIV> </DIV></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>regards,</FONT></DIV>
<DIV><FONT face=Arial size=2>Sinardy</FONT></DIV></BODY></HTML>