Skip to Main Content
  • Questions
  • How to avoid external table's data source file be replaced when another query is using the old data source file

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 25, 2016 - 2:36 pm UTC

Last updated: October 26, 2016 - 12:57 am UTC

Version: 11G

Viewed 1000+ times

You Asked

The external table's data source file is updated with some interval, if another query is running, replace the old data source file will cause 1)ORA-29913 and ORA-30653 if not setting reject limit UNLIMITED 2) data inconsistent, part data is old source file, another part is new source file if setting reject limit UNLIMITED 3) only part of the old source file.

Test procedure

1. Create one SQL file to spool several times select on external table to one txt file for further check whether data inconsistent
2. Replace the data souce file time and time when the SQL file is running.

Test result will be above description. So how to avoid the replacement if another query is running, have try to using lsof command to check whether the source file is open by another program when the query is running, but seems not work. many thanks

and Chris said...

I'm not aware of a way to stop this.

Could you change your processing, so the thing that overwrites the file calls the code that reads from it?

Then all you need to do is ensure only one copy of your process is running.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Simpler solution

Raj J, October 25, 2016 - 5:48 pm UTC

As soon as a file is available, (your) processes can rename it (by appending timestamp to it) and possibly move it to a different directory. And only then you process it from its new location using the new name. This should work in most cases.
Connor McDonald
October 26, 2016 - 12:57 am UTC

Nice input.

A technique I've seen used in the past is

File reading process
====================
- writes empty file, eg, 'filename.wip' ("work in progress")
- then reads filename.dat
- erases filename.wip

File writing process
====================
while true
if fileexists filename.wip then sleep 5
end while
write filename.dat

Its not perfect, but does a reasonable job and ensuring no collisions

A reader, October 26, 2016 - 4:29 am UTC

Very good solution of indicator file to seperate read and write

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library