Error while exporting data to flat file from database using sqlplus in UNIX

Question

Trying to export data from a table to .dat file in a unix script using the SPOOL command.

However on execution only ~17k data is exported to file out of 91K data with an error in end of the file : ORA-08103 Object do not exist, though the objects exists in the database.

When I tried to export the data in csv, it exported only ~11k data with same error in the file. Exporting almost ~35 columns from the table.

spool ${DataDirectory}/output.dat; 
select * from < Table_name >; 

What could be the reason for not exporting complete data set and throwing a error even if object exist?

Attached is the screenshot of the script.

enter image description here

enter image description here

Answer 1

ORA-08103 object no longer exists

means you were querying a segment that has since been (re)moved. The most common examples of this are:

• Someone truncated the table
• Someone did 'alter table move'
• Someone did 'exchange partition'

all of which move a segments contents to a new place on disk, hence the data you were scanning "no longer exists"

Answer 2

I though the error-number was

ORA-08103 object no longer exists

This is not a bash, shell or Unix problem: it is an Oracle issue. The reason for this error message can be various:

• another user/program/... removes an object from the table during the query
• parameters on temporary tables that have ON COMMIT DELETE ROWS
• even hard drive failures may cause this problem

So check the Oracle logs.

• sudhir: Hi Dullaart, When i checked into the database i still see the table existing in the DB with no change in record count.

Answer 3

When you are spooling from SQL*Plus for making script that time always execute following commands before executing spool

set heading off

set feedback off

set pagesize 0

spool file_name

Because these commands will build clean spool file for using any dynamic script. In your issue, there will be some inputs are coming from spool script which is resulting to throw error ORA-08103

• miracle173: these settings are not relared to the error
• sudhir: doc123, These options are already set in the script. Is there a way that i can break the script to read the data multiple times and then copy to a file?
• sudhir: Is there a way that i can loop my Select query in my script to spool every 15000 records into 1 file, so that I can concatenate them later.