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.

Session 1:-

SQL> select count(1) from anand.test_dec;

The session was running and i opened a new session and truncated the table.

Session 2:-

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.

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?”


About these ads

3 thoughts on “Silly and Interesting II

  1. Hi,
    When you truncate table, dataobject id of that table was changed. The reaon why you get “ORA-08103″ couases from changind data object id.

    1. Hi,

      Thanks for visiting the blog.

      As far as i understand from your comments is that the OBJECT_ID must change when we truncate the table causing the ORA error.But it is not true,

      03:01:32 SQL> create table test (a number);

      Table created.
      03:02:16 SQL> select object_name,object_id from dba_objects where object_name=’TEST’ and owner=’ANAND';

      OBJECT_NAME OBJECT_ID
      ———————————— ——————–
      TEST 340739

      03:02:51 SQL> insert into test values (1);

      1 row created.

      03:02:54 SQL> insert into test values (2);

      1 row created.

      03:02:57 SQL> insert into test values (3);

      1 row created.

      03:02:59 SQL>
      03:02:59 SQL> commit;

      Commit complete.

      03:03:01 SQL> select object_name,object_id from dba_objects where object_name=’TEST’ and owner=’ANAND';

      OBJECT_NAME OBJECT_ID
      ————————————— ——————
      TEST 340739

      03:03:06 SQL> select * from test;

      A
      ———-
      1
      2
      3

      03:03:21 SQL> truncate table test;

      Table truncated.

      03:03:30 SQL> select * from test;

      no rows selected

      03:03:33 SQL> select object_name,object_id from dba_objects where object_name=’TEST’ and owner=’ANAND;

      OBJECT_NAME OBJECT_ID
      ————————————— ——————
      TEST 340739

      The OBJECT_ID that is the object id remains the same.

      Regards,
      Anand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s