Re: SNAPSHOT TOO OLD (1555) - SOLUTION ?

On Thu, 26 Feb 1998 08:03:41 -0600, djose_at_att.com wrote:

>Hello everyone,

I have a Pro*C program which churns through 4 tables and
>creates a flat

 file. The program encounters oracle error code ORA-1555
>(SNAPSHOT TOO

 OLD ) causing it to exit.( I have an exit routine for non-zero
>return codes )

The tables are as follows

order
order_details
name
address

Options
1. As another poster has already said, the easiest solution is to process a range of orders at a time, closing and re-opening the cursor.

2. Improve the performance of the query to reduce the execution time. 4.5 hours seems a very long time.

3. Use temporary tables to hold the output of the job e.g

Create table order_output( order_id, order_col1, order_col2, name_id name_col1, address1_col1, address1_col2, address2_col1, address2_col2, details_1... ) storage ...( pctfree 80% )

insert into order_output (order_id, order_col1, order_col2, name_id..) select order_id, order_col1, order_col2, name_id from order;
-- the above ensures we have a snapshot of orders commit;

update order_output set name_col1 =
  ( select name_col1 from name where name.name_id = order_output.name_id );
commit;

repeat for each of the feed tables then fetch from order_output and dump to file.

This also allows the job to be restartable, by storing the current step in a database table. It does require that none of the dependent rows are deleted between steps.

4. Probably the quickest way is to dump the tables to fixed record length files sorted by key, using an index hint to avoid an Oracle sort operation. Then write a simple pro*C program to iterate through the orders file, using a binary search of the other files to find the corresponding entries.   Received on Sun Mar 01 1998 - 00:00:00 CST