Temp tablespace status in Oracle 9i

[newcomer]

Hi,

In my oracle V9R2, I temp file got deleted from O/S level. But I startup my database successfully.

SQL> startup
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

And when I am querying v$tempfile it is showing the details, but when I am querying dba_temp_files , I am getting the expected following error

ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF'


But I wonder how Oracle allows me to startup my database successfully. Can anybody give me some details on this? I will appreciate.

[adewri]

If you know the purpose or what role a temporary tablespace plays in a database then you have the answer.

A temporary tablespace is used only to create temporary segments. It doesnot play any role in database startup or shutdown...

[newcomer]

Thaks for your clarification. I knew that, but I was confused bcoz I was getting error in Oracle 8i while starting up my database.

I feel the difference is HOW I HAVE created those tablespaces. In 8i I created a Temp tablespace with a data file. I did not create a temp file. While in 9i I created with Tempfile clause.

Will you agree with me? Or rectify me.

Thanks

[SANJAY_G]

If tempfile does not exist when the database is brought up, DBWR writes to a trace file indicating the tempfile is not found, but the database opens normally.

You can offline drop those tempfiles, drop the tablespace and create a new one.

HTH

[pando]

considering it´s a temp tablespace and tempfiles I dnt think need to recreate the tablespace when you lose a tempfile, I believe you simply add a tempfile to the temp tablespace (9i)

[SANJAY_G]

Originally posted by pando
considering it´s a temp tablespace and tempfiles I dnt think need to recreate the tablespace when you lose a tempfile, I believe you simply add a tempfile to the temp tablespace (9i)

You are right pando, no need to drop tablespace.
Just do this.

alter database tempfile 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF' drop;
alter tablespace temp add tempfile 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF' size 10m;