Where things can get interesting is that a query that has already commenced against an exchanged partition *may* still succeed (as long as nothing messed with the original data).
For example:
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>
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>
SQL> commit;
Commit complete.
SQL>
SQL> create table t_ex ( x int, y char(100));
Table created.
SQL>
SQL> select max(y) from t;
(still running...)
-- in another session I do this:
SQL> alter table t exchange partition p4 with table t_ex without validation;
Table altered.
-- now back to session 1
MAX(Y)
----------------------------
16000000
You can see that even though an exchange took place, we still located the object and completed the query. Now let's do that again, but what if that object is no longer available
SQL> select max(y) from t;
(still running...)
-- in another session I do this:
SQL> alter table t exchange partition p3 with table t_ex without validation;
Table altered.
SQL> truncate table t_ex;
Table truncated.
-- now back to session 1
select max(y) from t
*
ERROR at line 1:
ORA-08103: object no longer exists
That's why people often describe this error as intermittent.