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.

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.

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