Wednesday, June 13, 2012

ORA-08103: object no longer exists / ORA-12801 & ORA-08103


Error occured from one of Our Informatica ETL batch Job.Which basically Truncate/select/load lot of partition tables.


ORA-12801: error signaled in parallel query server P040, instance ABC_DUMMY03.mycorp.com:DUMMYDB3 (3)
ORA-08103: object no longer exists

Here is re-generated error.

00:06:53 SQL> CREATE TABLE EDW.TEST AS SELECT * FROM EDW.SUMMARY_INFO WHERE ROWNUM < 10000;

Table created.

Elapsed: 00:00:00.90

00:06:59 SQL> SELECT * FROM EDW.TEST;

From Other session table is getting truncated.

00:06:48 SQL> TRUNCATE TABLE EDW.TEST;

Table truncated.

Elapsed: 00:00:00.19

And Here we go.

ERROR:
ORA-08103: object no longer exists

5565 rows selected.

Elapsed: 00:00:21.40
00:07:49 SQL>

Another scenario in which i did not had which statement caused problem.

Find All SQL_ID's of failed Statment
-------------------------------------------------------------------------
SQL> SELECT SQL_ID,substr(SQL_TEXT, 1, 55) TEXTS FROM DBA_HIST_SQLTEXT
WHERE SQL_TEXT LIKE '%SELECT stg.rdc_rec_creation_dt%' ORDER BY 1;

SQL_ID        TEXTS
------------- ------------------------------------------------------------
1agvajtvcx6uz SELECT stg.rdc_rec_creation_dt,
                     stg.refinery_n
1v7rz5pmchtpy SELECT stg.rdc_rec_creation_dt,
                     stg.refinery_n
3bj9r85rcq6u1 SELECT stg.rdc_rec_creation_dt,
                     stg.refinery_n
3n78p3wvfd9u7 SELECT stg.rdc_rec_creation_dt,
                     stg.refinery_n
3nwcxh1p6wkaq SELECT stg.rdc_rec_creation_dt,
                     stg.refinery_n
3sb25qjaj33v9 SELECT stg.rdc_rec_creation_dt,
--Result set Trim. 

-------------------------------------------------------------------------
Find All Tables which Accessed in Above SQL_IDs.
-------------------------------------------------------------------------
SELECT INSTANCE_NUMBER,SNAP_ID,USER_ID,SQL_ID,SQL_PLAN_HASH_VALUE,SQL_PLAN_OPERATION,DO.OBJECT_NAME,DO.SUBOBJECT_NAME,SQL_EXEC_START 
FROM DBA_HIST_ACTIVE_SESS_HISTORY DHSH,DBA_OBJECTS DO
WHERE SQL_ID IN (SELECT DISTINCT SQL_ID FROM DBA_HIST_SQLTEXT WHERE SQL_TEXT LIKE '%SELECT stg.rdc_rec_creation_dt%')
AND SNAP_ID BETWEEN 2597 AND 2621 
AND DHSH.CURRENT_OBJ#=DO.OBJECT_ID 
order by 2;

INST# SNAP_ID USER_ID SQL_ID    SQLPLANHASHVALUE SQLPLAN_OPERATION OBJECT_NAME        SUBOBJECT_NAME SQLEXEC_START_TIME
-------------------------------------------------------------------------------------------------------------------------------------
2     2609   101     btsj9guja52jn 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R13            9/21/2012 12:46:57 PM 
                          
2     2609   101     btsj9guja52jn 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R8            9/21/2012 12:46:57 PM                           
2     2609   101     97823yd06wvm3 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R8            9/21/2012 12:46:57 PM                           

2     2609   101     d9r00q7thjq2d 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R9            9/21/2012 12:46:57 PM                           
1     2609   101     3n78p3wvfd9u7 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R10            9/21/2012 12:46:57 PM                           
1     2609   101     ffubvc0gj883w 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R10            9/21/2012 12:46:57 PM                           
2     2609   101     agsu55vrz6nh5 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R10            9/21/2012 12:46:57 PM                           
2     2609   101     3bj9r85rcq6u1 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R10            9/21/2012 12:46:57 PM                           
1     2609   101     57u0u9v14yfdc 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R11            9/21/2012 12:46:57 PM                           
1     2609   101     f138kjx2k5r01 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R12            9/21/2012 12:46:57 PM                           
1     2609   101     57u0u9v14yfdc 799412467                          TEST_DLY_USAGE_STG R5                                                                        
2     2610   101     92z9466bz47aw 799412467         HASH JOIN         HOUSE_DIM_MV                 9/21/2012 1:49:02 PM                                        
1     2610   101     910zpzwq56x4a 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                     9/21/2012 1:49:03 PM                                  
2     2610   101     gp6qtc223wn7n 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R6            9/21/2012 1:49:03 PM                            
1     2610   101     910zpzwq56x4a 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R9            9/21/2012 1:49:03 PM                            
1     2610   101     910zpzwq56x4a 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R1            9/21/2012 1:49:03 PM                            
1     2610   101     1v7rz5pmchtpy 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R1            9/21/2012 1:49:02 PM                            
1     2610   101     abwj1n9krrcuc 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R1            9/21/2012 1:49:02 PM                            
2     2610   101     gp6qtc223wn7n 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R14            9/21/2012 1:49:03 PM                            
1     2610   101     1v7rz5pmchtpy 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R8            9/21/2012 1:49:02 PM                            
2     2610   101     3nwcxh1p6wkaq 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R13            9/21/2012 1:49:02 PM                            
1     2610   101     abwj1n9krrcuc 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R9            9/21/2012 1:49:02 PM                            
1     2610   101     910zpzwq56x4a 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R12            9/21/2012 1:49:03 PM                            
1     2610   101     abwj1n9krrcuc 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R12            9/21/2012 1:49:02 PM                            
2     2610   101     92z9466bz47aw 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R12            9/21/2012 1:49:02 PM                            
2     2610   101     3nwcxh1p6wkaq 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R10            9/21/2012 1:49:02 PM                            
1     2610   101     1v7rz5pmchtpy 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R11            9/21/2012 1:49:02 PM                            
2     2610   101     gp6qtc223wn7n 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R10            9/21/2012 1:49:03 PM                            
2     2610   101     92z9466bz47aw 799412467         TABLE ACCESS     TEST_DLY_USAGE_STG R9            9/21/2012 1:49:02 PM                            
2     2610   101     gp6qtc223wn7n 799412467         HASH JOIN       HOUSE_DIM_MV                 9/21/2012 1:49:03 PM                                        
2     2610   101     gp6qtc223wn7n 799412467         HASH JOIN       HOUSE_DIM_MV                 9/21/2012 1:49:03 PM                                        
2     2610   101     92z9466bz47aw 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                  9/21/2012 1:49:02 PM                                  
2     2610   101     3nwcxh1p6wkaq 799412467         HASH JOIN       HOUSE_DIM_MV                 9/21/2012 1:49:02 PM                                        
2     2610   101     92z9466bz47aw 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                      9/21/2012 1:49:02 PM                                  
1     2610   101     abwj1n9krrcuc 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                      9/21/2012 1:49:02 PM                                  
1     2610   101     1v7rz5pmchtpy 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                      9/21/2012 1:49:02 PM                                  
1     2610   101     910zpzwq56x4a 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                      9/21/2012 1:49:03 PM                                  
1     2610   101     abwj1n9krrcuc 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                      9/21/2012 1:49:02 PM                                  
1     2610   101     1v7rz5pmchtpy 799412467         MAT_VIEW ACCESS   HOUSE_DIM_MV                      9/21/2012 1:49:02 PM                                  
1     2610   101     910zpzwq56x4a 799412467         SELECT STATEMENT  NODE_DIM_MV                        9/21/2012 1:49:03 PM                           


--Find All Commands executed on Tables which founded on Above SQL

SELECT INSTANCE_NUMBER,SNAP_ID,USER_ID,SQL_ID,SQL_PLAN_HASH_VALUE,SQL_PLAN_OPERATION,DO.OBJECT_NAME,DO.SUBOBJECT_NAME,SQL_EXEC_START 
FROM DBA_HIST_ACTIVE_SESS_HISTORY DHSH,DBA_OBJECTS DO
where SNAP_ID BETWEEN 2597 AND 2621 
AND DHSH.CURRENT_OBJ#=DO.OBJECT_ID 
AND DO.OBJECT_NAME IN ('TEST_DLY_USAGE_STG','TEST_process_control_lkp','HOUSE_DIM_MV','NODE_DIM_MV')
order by 2;
-------------------------------------------------------------------------------------------------------------------------------------
INST# SNAP_ID USER_ID SQL_ID       SQLPLAN_HASH_VALUE SQL_PLAN_OPERATION OBJECT_NAME         SUBOBJECT_NAME SQL_EXEC_START
1     2609   101    f138kjx2k5r01 799412467           TABLE ACCESS       TEST_DLY_USAGE_STG R12             12:46:57 PM
1     2609   101    57u0u9v14yfdc 799412467           TABLE ACCESS       TEST_DLY_USAGE_STG R11             12:46:57 PM
1     2609   101    3n78p3wvfd9u7 799412467           TABLE ACCESS       TEST_DLY_USAGE_STG R10             12:46:57 PM
1     2609   101    ffubvc0gj883w 799412467           TABLE ACCESS       TEST_DLY_USAGE_STG R10             12:46:57 PM
                                                                                                                
--Partition start begin Read                                 <-HERE WE GO.                                                                                                                       
2     2609   101    btsj9guja52jn 799412467           TABLE ACCESS       TEST_DLY_USAGE_STG R8             12:46:57 PM
2     2609   101    97823yd06wvm3 799412467           TABLE ACCESS       TEST_DLY_USAGE_STG R8             12:46:57 PM
                                                                                                                
--Partition start getting truncat.                                                                                                                                                                     
1     2609   101    2hkk1v26hy6by 3629144304         DDL STATEMENT       TEST_DLY_USAGE_STG R8             12:47:07 PM

---------
Find Which DDL STATEMENT is this. (get SQL_ID from above statement)

SQL> select SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='2hkk1v26hy6by';

SQL_TEXT
--------------------------------------------------------------------------------
ALTER TABLE TEST_USAGE.CHSI_DLY_USAGE_STG TRUNCATE PARTITION R8


Reference.
OERR: ORA-8103 "object no longer exists" / Troubleshooting, Diagnostic and Solution [ID 8103.1]

No comments:

Post a Comment