Home » SQL & PL/SQL » SQL & PL/SQL » Error message: ORA-08103: object no longer exists.
Error message: ORA-08103: object no longer exists. [message #677709] Tue, 08 October 2019 09:01 Go to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
I have a scenario with two ETL batch jobs(Lets call them Job A and Job B) belonging to different subject areas with no dependency among each other, but operating on same table.



job A has the following statement

ALTER TABLE <TABLENAME> TRUNCATE PARTITION (PNAME) UPDATE INDEXES



job B does a simple select * from the same table above



Now the issue is this. On rare occasions they kind of overlap or run simultaneously causing the below error in second batch job (which has the select statement)



Error message: ORA-08103: object no longer exists.



We are aware that the root cause is because it is trying to select from the table when the partition is being truncated. We even were able to reproduce it in the TEST environment where we ran the batch job 2 and on a separate toad session we ran the Truncate PArtition query



As I said this happens on only once or twice a month when these jobs run simultaneously. This is completely not in our control as we cant set dependencies between these 2. As i said they belong to different domains and we cant just make 1 job wait for another



In this case, is there something which can be done in the Job B SELECT query such that this error does not happen ?


Version Info:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Re: Error message: ORA-08103: object no longer exists. [message #677713 is a reply to message #677709] Tue, 08 October 2019 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Quote:
In this case, is there something which can be done in the Job B SELECT query such that this error does not happen ?

Just make the B job traps the error and then re-execute the statement in case it is reached.

Re: Error message: ORA-08103: object no longer exists. [message #677719 is a reply to message #677713] Wed, 09 October 2019 02:45 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
Michel Cadot wrote on Tue, 08 October 2019 10:49

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Quote:
In this case, is there something which can be done in the Job B SELECT query such that this error does not happen ?
Just make the B job traps the error and then re-execute the statement in case it is reached.

How to do that? Can I do it in the query itself?
Re: Error message: ORA-08103: object no longer exists. [message #677721 is a reply to message #677719] Wed, 09 October 2019 03:28 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
No.
You would need an exception handler.
Re: Error message: ORA-08103: object no longer exists. [message #677723 is a reply to message #677721] Wed, 09 October 2019 03:57 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
Will the exception handler be coded such that it waits for the Truncate query to finish and then issue the Select
Can you elaborate with a pseudo code?
Re: Error message: ORA-08103: object no longer exists. [message #677726 is a reply to message #677723] Wed, 09 October 2019 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Can you elaborate with a pseudo code?
select ...
when exception ORA-08103 then 
  wait a little bit
  goto select
end when
You can also add a counter in the loop and re-raise the exception of the exceptions exceed a number of time to avoid an infinite loop.

You can also use a "select for update" it will try to lock the table and so was blocked by the truncate but the counterpart is that it will then block other treatments it should not with "for update".

[Updated on: Wed, 09 October 2019 05:31]

Report message to a moderator

Re: Error message: ORA-08103: object no longer exists. [message #677728 is a reply to message #677726] Wed, 09 October 2019 06:39 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
If I use Select for Update and if the truncate is in progress, will Select wait or will the query abort?
I want it not to abort
Re: Error message: ORA-08103: object no longer exists. [message #677729 is a reply to message #677728] Wed, 09 October 2019 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
abort.

You need to call the query in a loop with an exception handler.
Re: Error message: ORA-08103: object no longer exists. [message #677730 is a reply to message #677729] Wed, 09 October 2019 07:22 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
Could you please specify how I handle this in exception as it is not part of pre-defined exception
Re: Error message: ORA-08103: object no longer exists. [message #677731 is a reply to message #677730] Wed, 09 October 2019 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's an example:
SQL> declare
  2    a number;
  3    myexcpt exception;
  4    pragma exception_init (myexcpt, -1476);
  5  begin
  6    a := 1/0;
  7  exception
  8    when myexcpt then dbms_output.put_line('exception division by 0');
  9  end;
 10  /
exception division by 0

PL/SQL procedure successfully completed.
Re: Error message: ORA-08103: object no longer exists. [message #677732 is a reply to message #677731] Wed, 09 October 2019 08:44 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
I tried
begin  
    
  sql_stmt := 'SELECT 
*
FROM TAB';  
exception ORA-08103
WHen excpetion
then dbms_lock(sleep10)
  execute immediate sql_stmt; 
end; 

This is aborting
Re: Error message: ORA-08103: object no longer exists. [message #677733 is a reply to message #677732] Wed, 09 October 2019 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try a valid syntax.

Re: Error message: ORA-08103: object no longer exists. [message #677753 is a reply to message #677733] Fri, 11 October 2019 03:36 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
Could you please let me know how to? The PL-SQL is fetching just 1 row while the table has millions
Re: Error message: ORA-08103: object no longer exists. [message #677755 is a reply to message #677753] Fri, 11 October 2019 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Could you please post what you do.

Re: Error message: ORA-08103: object no longer exists. [message #677758 is a reply to message #677755] Fri, 11 October 2019 05:18 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
begin  
    
  sql_stmt := 'SELECT 
*
FROM TAB';  



  execute immediate sql_stmt; 
end; 

I started with this statement initially.
Before getting into exception handling I just wanted to confirm if it is working for a simple select. But it is not
Re: Error message: ORA-08103: object no longer exists. [message #677759 is a reply to message #677758] Fri, 11 October 2019 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why are you bothering with dynamic SQL?

[Updated on: Fri, 11 October 2019 05:24]

Report message to a moderator

Re: Error message: ORA-08103: object no longer exists. [message #677760 is a reply to message #677758] Fri, 11 October 2019 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> begin
  2
  3    sql_stmt := 'SELECT
  4  *
  5  FROM TAB';
  6
  7
  8
  9    execute immediate sql_stmt;
 10  end;
 11  /
  sql_stmt := 'SELECT
  *
ERROR at line 3:
ORA-06550: line 3, column 3:
PLS-00201: identifier 'SQL_STMT' must be declared
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
ORA-06550: line 9, column 21:
PLS-00201: identifier 'SQL_STMT' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
Each variable needs to be declared;

I gave you an example of code,
Re: Error message: ORA-08103: object no longer exists. [message #677761 is a reply to message #677759] Fri, 11 October 2019 05:33 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
Because I will need the PL/SQL construct for exception handling.
Re: Error message: ORA-08103: object no longer exists. [message #677762 is a reply to message #677761] Fri, 11 October 2019 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
I get that you think that explains why you need dynamic SQL but it really doesn't.

You don't need dynamic SQL to call a SQL in a loop in PL/SQL with exception handling.

You need dynamic SQL if the SQL statement needs to be dynamic.
Re: Error message: ORA-08103: object no longer exists. [message #677763 is a reply to message #677761] Fri, 11 October 2019 06:13 Go to previous messageGo to next message
Bill B
Messages: 1919
Registered: December 2004
Senior Member
Michel gave you the code to handle the error, all you had to do was expand on what he showed.

DECLARE
    Cnt         NUMBER;
    No_object   EXCEPTION;
    PRAGMA EXCEPTION_INIT (No_object, -8103);
    My_col      VARCHAR2 (100);
BEGIN
    Cnt := 1;

    WHILE Cnt > 0
    LOOP
        BEGIN
            SELECT Col1 INTO My_col FROM My_table;

            Cnt := 0;
        EXCEPTION
            WHEN No_object
            THEN
                IF Cnt = 10
                THEN
                    RAISE No_object;
                END IF;

                -- sleep for 5 seconds. The user running the dbms_lock 
                -- procedure must be given permission by the dba to
                -- access the package.
                DBMS_LOCK.Sleep (5);
                Cnt := Cnt + 1;
        END;
    END LOOP;
END;
/
Re: Error message: ORA-08103: object no longer exists. [message #677766 is a reply to message #677763] Fri, 11 October 2019 06:58 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
If i use this
SELECT Col1 INTO My_col FROM My_table;

do i need to declare a variable of each of the 20 columns of the table?
Re: Error message: ORA-08103: object no longer exists. [message #677767 is a reply to message #677766] Fri, 11 October 2019 07:01 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could use a record type.

Do you not already have variables for the existing query?
Re: Error message: ORA-08103: object no longer exists. [message #677768 is a reply to message #677767] Fri, 11 October 2019 07:04 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
The existing query is not a PL/SQL block. It is a simple SQL Select * from TABLE
Re: Error message: ORA-08103: object no longer exists. [message #677769 is a reply to message #677768] Fri, 11 October 2019 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
What, exactly, is running the existing query?
Re: Error message: ORA-08103: object no longer exists. [message #677770 is a reply to message #677769] Fri, 11 October 2019 07:05 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
And really this would go a lot faster if you explained your existing code in detail.
Re: Error message: ORA-08103: object no longer exists. [message #677772 is a reply to message #677770] Fri, 11 October 2019 07:09 Go to previous messageGo to next message
abhilashrn
Messages: 12
Registered: October 2019
Junior Member
I have a scenario with two ETL batch jobs(Lets call them Job A and Job B) belonging to different subject areas with no dependency among each other, but operating on same table.



job A has the following statement

ALTER TABLE <TABLENAME> TRUNCATE PARTITION (PNAME) UPDATE INDEXES



job B has this
SELECT * FROM <TABLENAME>



Now the issue is this. On rare occasions they kind of overlap or run simultaneously causing the below error in JOB B (which has the select statement)



Error message: ORA-08103: object no longer exists.

I need to chnage the job B such that it does not abort and instead waits for job A
Re: Error message: ORA-08103: object no longer exists. [message #677774 is a reply to message #677772] Fri, 11 October 2019 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So do it as it has been said.

Re: Error message: ORA-08103: object no longer exists. [message #677777 is a reply to message #677774] Fri, 11 October 2019 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's just repeating your initial post without giving any meaningful additional details.

What is the tool/program/language that is running these jobs?
Is it PL/SQL in a stored procedure
Is it a custom program written in java or c++ or something?
Is it some third party tool?

Without knowing what is running that select statement we can't give you any advise about to implement to way of handling the error beyond what we've already posted.
Re: Error message: ORA-08103: object no longer exists. [message #677809 is a reply to message #677768] Sat, 12 October 2019 09:14 Go to previous message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
abhilashrn wrote on Fri, 11 October 2019 07:04
The existing query is not a PL/SQL block. It is a simple SQL Select * from TABLE
Putting 'select * ' in production code is a bug waiting to happen. If you use 'select *', your code will break the first time someone modifies the table definition. 'Select *' is fine for one-off, ad hoc code. But production code should KNOW EXACTLY what columns are to be dealt with, and code accordingly.
Previous Topic: How To Measure Time To Build a Materialized View
Next Topic: ORA-02320/ 00904 nested table error
Goto Forum:
  


Current Time: Wed Dec 11 19:42:11 CST 2019