CODEWITHSUNDEEP
sqloracle-databaseoracle11gdblink
FIROZ K A
FIROZ K A

Reputation: 111

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 rmottab@remotedb;

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

Upvotes: 1

Views: 2181

Answers (1)

Bjarte Brandt
Bjarte Brandt

Reputation: 4461

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.

Upvotes: 2

Related Questions