Learning is not a spectator sport

In the previous post we saw that even though we had done an exchange partition mid-flights through a query execution, the query kept on running to successful completion. The database managed to find the data from the exchanged partition and keep on executing.

The logical next question is – is that always the case? What if we went ahead and did “something” to that partition that is now a standalone table whilst the query is executing.

As per the previous post, I’ve created our table from scratch with four partitions and four million rows in each partition. My query is unchanged – we’re going to get the maximum value of Y which means scanning all four partitions.

SQL> create table t ( x int, y char(100))
  2  partition by list ( x )
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3),
  6    partition p4 values (4)
  7  );
Table created.
SQL> insert /*+ APPEND */ into t
  2  select c1, lpad(rownum,10)
  3  from
  4  ( select rownum c1 from dual connect by level <= 4 ),
  5  ( select 1 from dual connect by level <= 2000 ),
  6  ( select 1 from dual connect by level <= 2000 );
16000000 rows created.
SQL> commit;
Commit complete.
SQL>

Previousl, I just exchanged the partition out, whereas this time I’m going to exchange that partition out with T_EX but then I am going to truncate it so those rows that the query was seeking will now be gone.

Let’s see what happens with my query running, and then exchange/truncate in a separate session.

--
-- Session 1
--
SQL> select max(y) from t;
[working]
--
-- Session 2 
--
SQL> alter table t exchange partition p4 with table t_ex without validation;
Table altered.
SQL> truncate table T_EX;
Table truncated.
--
-- Back in session 1
--
SQL> select max(y) from t;
select max(y) from t
       *
ERROR at line 1:
ORA-08103: object no longer exists

Ker splat 🙂

This time when the database was trying to find the partition that had been exchanged out, it went looking for a segment with a particular DATA_OBJECT_ID which is no longer in the data dictionary. We hence get the “infamous” error: ORA-08103 object no longer exists

So if you’re seeing our ORA-08103 in your production systems, the most common cause is going to be partition or table maintenance operations going on whilst applications or users are actively querying those segments.

It also means that generally you cannot safely rely on a query completing to execution like in the previous post. If it does, then great! But you probably just got lucky.