gs79
asked on
Job running on Oracle database fails on first run
Db: Oracle 11g
We have an etl job run via a third party scheduler that connects to oracle database via db link and fetches the data into a file.
The query is as follows:
select
<bunch of colums>
from
user.view_object@remotedb
This fails on the first run with the following error:
Error code: 8103, Error message: ORA-08103: object no longer exists.
On the re-run it runs just fine.
The object always exists on the remote database. Cannot understand why it fails on the first run?
Any thoughts? Please let me know if you need more information
We have an etl job run via a third party scheduler that connects to oracle database via db link and fetches the data into a file.
The query is as follows:
select
<bunch of colums>
from
user.view_object@remotedb
This fails on the first run with the following error:
Error code: 8103, Error message: ORA-08103: object no longer exists.
On the re-run it runs just fine.
The object always exists on the remote database. Cannot understand why it fails on the first run?
Any thoughts? Please let me know if you need more information
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would work directly with Oracle Support on this one. As hinted above, if you Google around you can see there are several things that can cause this.
In checking with Oracle Support there are several bugs that can also cause this. By chance is the remote table you are accessing Partitioned?
There are Hardware related bugs as well. Basically, there are way too many things for us to try and troubleshoot here.
You can set up client and server side tracing (in the sqlnet.ora files) and see if something is obvious but Oracle trace files are hard to interpret.
In checking with Oracle Support there are several bugs that can also cause this. By chance is the remote table you are accessing Partitioned?
There are Hardware related bugs as well. Basically, there are way too many things for us to try and troubleshoot here.
You can set up client and server side tracing (in the sqlnet.ora files) and see if something is obvious but Oracle trace files are hard to interpret.
or do this...
in your job, select in a try catch, ie, select 2 times :)
first will not do anything, the second is your job...
maybe this way you will forget about the error
in your job, select in a try catch, ie, select 2 times :)
first will not do anything, the second is your job...
maybe this way you will forget about the error
ASKER
This is not a partition table in the source. It's hard to reproduce this as it happens randomly. The issue occurred two days straight but has been running fine for past few days.
@hainkurt
That seems to be a nice workaround. But we would like to know what causes this
Thanks
@hainkurt
That seems to be a nice workaround. But we would like to know what causes this
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
re build the indexes on related table(s)
if possible, rename table(s), create a brand new table(s), add indexes and do a "select from old table(s) into new table(s)"
looks like something is corrupt somewhere...