Oracle snapshot too old and DBLINK

I am getting

ORA-01555: snapshot too old: rollback segment number 234 with name "_SYSSMU234_1378897836$"

too smal while selecting some data using dblink in oracle database.

select a,b from localtab a union select a,b from [email protected];

Is there any way to override this error?
I have a situation to take this data in a periodical manner.

--------------Solutions-------------

You need to close the cursor when finished retrieving the data. Your problem is that you are holding the cursor open for too long. There are tons of reasons for that: - row-by-row processing on the client side. Will take forever to finish. (the database is waiting to deliver data to the client) Hint. arraysize (fetchsize) is set too low. An arraysize of 100-200 can speed things up. - there is a nice view hidden behind "rmotab" which takes > 30 minutes to return which is burning cpu (logical reads)

Basically, you have these options:

  1. Speed up your process. (end your cursor (query) faster). Bulk load.
  2. Schedule the process to happen when no DML is going on.
  3. Materialized View (pre-load the data (read-only))
  4. Increase UNDO.
Category:sql Time:2018-06-23 Views:0

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 4.678 (s). 14 q(s)