-
Temp tablespace status in Oracle 9i
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.
Nwcomer
Student
-
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...
Amar
"There is a difference between knowing the path and walking the path."
-
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
Nwcomer
Student
-
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
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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)
-
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.
Code:
alter database tempfile 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF' drop;
alter tablespace temp add tempfile 'E:\ORACLE\ORA92\BUCHKU\TEMP01.DBF' size 10m;
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|