Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Had a once-a-day long running data warehousing procedure that I had written that was failing about 50% of the time. Logs were showing ORA-08103 'object no longer exists'.

ORA-12012: error on auto execute of job "KIPP_NWK"."TS_GRADES_TIME_REFRESH"
ORA-08103: object no longer exists

I created the ServerError trigger suggested on by Leigh Riffel here but that didn't capture any extra level of detail beyond the errors thrown by the log. Was a touch puzzled because most of what I could find about the error pointed to block corruption, which seemed like a dead end the more I investigated.

Some context about the job in question - we're a school district and our student info system runs on top of Oracle. We have robust SELECT privileges, but can't create anything on the production machine. We connect to the production db over a database link, but have noticed enormous performance differences for queries written directly to the production machine vs. executed over the dblink. (As an aside, as research for this question I saw some new resources mentioning using the driving_site hint to improve queries over dblink, which might be another route to resolving the problem more simply).

The process that was throwing the 08103 errors above was an ETL process that

1) copied a bunch of tables from the production database into local global temporary tables (ON COMMIT PRESERVE ROWS)

2) calculated and averaged student grades, using some temporary staging tables to store intermediate results

3) truncated the result table

4) inserted (with append hint) into the final result table.

Some unrelated research into materialized view 'snapshot too old' errors lead me to the UNDO_RETENTION parameter. I couldn't find any documentation that connected ORA-08103 to UNDO tablespace or the size of the UNDO_RETENTION parameter, but upping the size of that parameter seems to have resolved the issue for us.

So, question - given the ambiguity of the 'object no longer exists' error, is it plausible that the size of the UNDO_RETENTION parameter was at fault? Changing UNDO_RETENTION for the entire db feels rather over-broad - are there other potential avenues/less invasive fixes worth investigating? I can provide more detail about the procedure in question, but the answer I'm looking for would talk more about the relationship between UNDO_RETENTION, global temporary tables, and anything special about database links if there's anything there that wouldn't meet the eye. (I'm aware that there's an implicit commit in TRUNCATE, and I don't think that has anything to do with the issue I was seeing, given that sometimes the procedure would execute successfully.)

Thanks!

share|improve this question
    
When at point "4", are you using, either explicitly or implicitly, a cursor that depends on point "1"? The "1" remote query might have allocated a little bit of dummy undo on your db (surprise!), and after "3" commit this undo would likely get overwritten as a result of small UNDO_RETENTION. Just an idea of a possible connection of dblink with UNDO_RETENTION. (Temporary tables are unrelated to this behavior; whether you use them or not, no impact here.) –  kubanczyk Nov 9 '12 at 14:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.