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
Note: Only a member of this blog may post a comment.