When your oracle temporary table space get corrupted.

So, guys its after few weeks here again with new post. 👮 It’s my early days with Oracle Database. After playing with Mysql, Mssql and Orient DB(Graph Db) for nearly 4–5 years, now it’s time to start something new. It’s Oracle. 😅
Still new to Oracle/Oracle DB Administration , but hope this is going to be more interesting than i thought. So what i’m going to post today is something related to common Oracle error.
“ORA-01187: cannot read from file because it failed verification tests”
This is mainly due to Oracle Temporary tablespace Data file Corruption. So i got the correct way of resolving this error.

1. Let’s first check our v$tempfile.

This sql query will list down all the tempfiles and their status whether online or offline.

2. Then we should drop the corrupted tempfile from the DB. If not, this error continues. as an example let’s say my error comes from temp1.dbf file which is related to TEMP1 temporary tablespace. So we need to drop this corrupted tempfile(temp1.dbf) from DB.
** Not the table space , it’s only tempfile.

This will drop the corrupted tempfile from DB.

3. Check V$TEMPFILE again. Now you can see that temp1.dbf file was gone from the list. 😇😇

4. Now we should create new tempfile for the TEMP1 table space.
**Do not separately create new temp file and add it to TEMP1 tablespace. Because it is not going to work. Therefore, use below query.

This query will create new temp4.dbf temp file and add it to the TEMP1 tablespace which is the default tablespace for this Oracle DB.

Game Over! 💪💪💪💪
Now you are clean with your oracle temporary tablespace & tempfile.
Hope this will be helpful to someone. See you soon. 😊😊

Originally published at http://printfnimesh.wordpress.com on December 1, 2017.