Ora-01187

[reydp]

oracle 817 solaris 8

hello again,
I have this problem with one of my tablespace(USRTMP), though this is a temporary tablespace(LOCALly managed), I can't seem to bring it down or up. I already reassigned the temporary tablespace of users used to be assigned in the USRTMP to ther TEMP TBS, so nothing to worry when it comes to availability of the DB.

What I did as far as I can remember, one of our mount drive /c1t05 was full. And the only possible tbs that I can alter to free-up some space is the that is being used by either RBS or TEMP TBS. I have made changes to both, RBS was alright but USRTMP temp tbs was not.
I made my changes through OEM, but the equivalent SQL is:
ALTER DATABASE TEMPFILE '/c1t05/....../USRTMP.dbf' offline;

It didn't prompt me of error, but today when I query the tablespace in OEM, it prompt me this error(ORA-01187/ORA01110).

here's what oracle docs says regarding this error:
---------------------------------
ORA-01187 cannot read from file string because it failed verification tests

Cause: The datafile did not pass the checks to insure it is part of the database. Reads are not allowed until it is verified.

Action: Make the correct file available to the database. Then, either open the database, or execute ALTER SYSTEM CHECK DATAFILES.
--------------------------
ORA-01110 data file string: 'string'

Cause: This message reports the file name involved with other messages.

Action: See the associated messages for a description of the problem.
---------------------------------------------------------

I already do the ALTER SYSTEM CHECK DATAFILES command, but still the same error occured.
Where did I miss guys?

Thanks in Advance,
rey

[SANJAY_G]

If no trnasactions are using that temp tbs, you cna drop and recreate it.

[OracleDoc]

sanjay just beat me to it

Since it's temporary why don't you just try droping it and recreate it?

[reydp]

Originally posted by SANJAY_G
If no trnasactions are using that temp tbs, you cna drop and recreate it.

thanks SANJAY,
I just did drop the tempfile and do the alter system check datafiles
and it worked.