ORA-08103: object no longer exists

"Object no longer exists" 

Which means that the object was there but not now :) This simple phrase means so much, it could be interpreted like any one of the below

1. The object is vanished in the middle of my activity, i would anticipate "ora-01555 - snapshot too old"  but how can oracle afford to loose an object when it is being used
2. Does this mean block corruption
3. Or is it by mistake i get "object no longer exists" instead of "ORA-00942: table or view does not exist" - kidding ahh :)  

okie lets do some blogging,

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Keeping aside block corruption scenario, Lets create the table in session S1 and simulate the error

SQL>REM CREATE TABLE
SQL> create table tbl_t1 as select * from dba_objects;

Table created.

SQL>REM FUNNY QUERY
SQL> select count(*) from
(
select t1.object_id,t2.data_object_id from (select * from tbl_t1)t1,(select * from tbl_t1)t2 where t1.LAST_DDL_TIME >=t2.LAST_DDL_TIME and
t2.STATUS in (select status from tbl_t1)
);  2    3    4    5
select t1.object_id,t2.data_object_id from (select * from tbl_t1)t1,(select * from tbl_t1)t2 where t1.LAST_DDL_TIME >=t2.LAST_DDL_TIME and
                                                          *
ERROR at line 3:
ORA-08103: object no longer exists

Ops what happen to my table, lets check its definition

SQL>REM CHECK FOR TABLE DEFINITION
SQL> desc tbl_t1
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL>

Well what happened during my query execution was, an truncate statement was issued at the same time in session S2 which has caused the issue at session S1

SQL> truncate table tbl_t1;

Table truncated.

SQL>

Truncate is causing the issue? Lets validate what truncate is doing at the background

SQL> select object_id,data_object_id from dba_objects where object_name ='TBL_T1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     74712          74714

Elapsed: 00:00:00.03
SQL>
SQL> select count(1) from tbl_t1;

  COUNT(1)
----------
         0

Elapsed: 00:00:00.00
SQL> insert into tbl_t1 select * from dba_objects;

72514 rows created.

Elapsed: 00:00:00.61
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Issue truncate now

SQL>
SQL> truncate table tbl_t1;

Table truncated.

Elapsed: 00:00:01.13
SQL> select object_id,data_object_id from dba_objects where object_name ='TBL_T1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     74712          74715

Elapsed: 00:00:00.00
SQL>

Note : DATA_OBJECT_ID is incremented, Now it is very clear that Truncate recreates the tables with a new DATA_OBJECT_ID. Now does this happen when i issue truncate on a table at all times ?

SQL> REM WITHOUT DATA
SQL>
SQL> select count(1) from tbl_t1;

  COUNT(1)
----------
         0

Elapsed: 00:00:00.00
SQL>
SQL> truncate table tbl_t1;

Table truncated.

Elapsed: 00:00:00.01
SQL>
SQL> select object_id,data_object_id from dba_objects where object_name ='TBL_T1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     74712          74715

Elapsed: 00:00:00.00
SQL>

Thus DATA_OBJECT_ID is incremented only when truncate is issued on a table which holds data. This change in DATA_OBJECT_ID has led to "ORA-08103: object no longer exists" error while executing the query which has referenced the same table being truncated simultaneously.

Haven't we got another point to answer the interview question "what is the difference between truncate and delete" :)

Real Time SQL Monitoring in Oracle 11g

Do you want to know, 

Can i see the plan while the SQL is running ?

Where is my SQL is in its Execution Plan ?

Need statistics at each step of the plan and what are the resources consumed for the just completed step ?

What is the Actual Cardinality at each step ?

       There are many ways to retrieve the above data and they are costly in terms of impacting the system performance. Oracle 11g introduces Real Time SQL Monitoring which would answer the above question with  almost no impact on the system.

Lets try these,  Below is the query written with the only intention to run for longer period of time :)

SQL> create table tbl_parallel as select rownum id,'DOD'||rownum id_val,sysdate
id_dt from dual connect by level < 10000;
SQL>
SQL>
SQL> with tp as (
select * from tbl_parallel),
tl as (
select length(id_val) ld from tbl_parallel where length(id_val)> (select min(length(id_val)) from tbl_parallel))
select * from tp t1,tp t2,tl where t1.id > t2.id and length(t1.id_val)>tl.ld;
SQL>

Lets take a look at the Execution Plan and check what is the step it is currently executing 

 The report is so cool, the SQL is now performing SORT JOIN. Take a look at the statistics - Elapsed time, Buffer gets and writes and I/O wait time being provided. We could also see the actual cardinality for each step. It also provides the SQL Bind info, Session id etc

will see how SQL Monitoring can help in analyzing SQL's in the next part