Silly and Interesting II
“What happens when we do select on the table and someone from another session truncates the table? “
I enabled a session for parallel dml using alter session enable parallel dml and then fired the insert statement using the append hint.Mistake was that neither the table was having any parallel attribute nor parallel hint was given.So,parallel execution of the dml wasn’t taking place.I killed the session after few minutes and just for fun fired select count(1) from table .Now as the table had some data the select was taking time.Immediately, i opened a new session and fired the truncate table command as the select command was still in progress, and was shocked to see the popped up output in the select command session.
SQL> select count(1) from anand.test_dec;
The session was running and i opened a new session and truncated the table.
SQL> truncate table anand.test_dec; Table truncated.
The session 1 was still active when i fired the truncate table command.The moment the table got truncate (actually within a flash of seconds) check the output at session 1.
SQL> select count(1) from anand.test_dec; select count(1) from anand.test_dec; * ERROR at line 1: ORA-08103: object no longer exists
Wasn’t that enough just to make your heart fail :)
I truncated the table , i didn’t drop it , how can i get “ORA-08103: object no longer exists”……., though the table exists. :)
Also refer to Uwe’s blog http://uhesse.wordpress.com/2009/10/27/dropping-a-table-during-select/ where he has shown “What happens if we do select on a table and someone else drops it during it?”