LOB Corruption prevented export of a Table

Few days back, I noticed the following error while DataPump was exporting a table containing BLOB column:
ORA-31693: Table data object "<<tablename>>" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old

I tried to export that table with the old export utility, but that too was not successful.

Searched the Oracle Support Documents and got the below script:

set serverout on
exec dbms_output.enable(100000);
declare pag number;
len number;
c varchar2(10);
charpp number := 8132/2;
begin for r in (select rowid rid, dbms_lob.getlength () len from ) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (, 1, 1+ (page * charpp)) into c from where rowid = r.rid;
exception
when others then
dbms_output.put_line ('Error on rowid ' R.rid' page 'page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/

The execution of the above script showed two records have corrupted blocks. I deleted those records and attempted export (using DataPump). This time it was successfull.

I am looking for ways to recover such corrupted blocks.

ORA-00600: internal error code, arguments: [kcbnew_3], [6], [1], [51907], [], [], [], []

Ours is a Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production database and is being accessed by our Forms10g product "eFreight Suite", a freight forwarding package.

Yesterday, I noticed "ORA-00600: internal error code, arguments: [kcbnew_3], [6], [1], [51907], [], [], [], []" error in the alert log and some of our users complained that, the Forms Application also throws such error.

Got the problem solved by clearing the buffer cache by executing
alter system flush buffer_cache;

Forms 11g bug in dynamic sql execution (EXEC_SQL) while generating CSV output

In our Oracle Forms application, we provide an option to the user to generate CSV output from the data queried on the screen. For this what we do is capture the SQL Query from the Data Block of the form along with the user defined predicates and use dynamic sql execution (EXEC_SQL) to fetch the data from the database and write the data in to a text file with extension .CSV and use web.showdocument() built in to show the generate CSV file on the client machine. This was working fine in Forms 10g.



When we attemted to migrate our application, we landed in trouble. That is due to a bug in Forms 11g, EXEC_SQL.DESCRIBE_COLUMN did not raise invalid_column_number exception, which is very essential while working with dynamic SQL because at the time of programming the programmer does not know the exact number columns.



Actually we spent several hours in identifying the issue. After identifying this issue, communicated with Oracle Support. As usual, after taking a long time Oracle accepted it as a defect and keep on working on that.



In the mean while, I developed a PJC (Pluggable Java Component) to generate the CSV file. For this PJC to work, I passed the user name, password along with the query. It works fine and a little faster than the EXEC_SQL.



The only difference here is it is running on a new database session whereas the older method was working on the same same session.