June 19, 2003 at 8:25 am
Hello All,
Being only a part-time admin, I just started seeing this error -
Msg 7399, Level 16, State 1, Server xxxNT000, Procedure , Line 2
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'MSDAORA'
reported an error.
[OLE/DB provider returned message: ORA-01555: snapshot too old: rollback
segment number 7 with name "RBS6" too small]
I have a view to a linked server table from an Oracle database. This procedure usually returned about 800,000 rows. And of course, this error message is not in the BOL and searching Google doesn't help. Nothing has changed in the SQL script. Any ideas what this means? Is there a URL for reading the descriptions to SQL Error codes?
Tnx
June 19, 2003 at 9:19 am
You have a problem with the Oracle database.
Look at the rollback segments in the Target Oracle databases. You need to extend the rollback segments. In oracle every thing you update is first stored in RBS as a preimage to help you rollback in case you decided to rollback your changes.
consider doing the following
1) Increase the size of all the roll back segments or provide more rollback segments.
2) Increase the 'OPTIMAL' size of the roll back segments,Alter rollback segment "RBS03" storage (optimal 123);
3)If you are running a batch then make sure using commits more frequently.
June 19, 2003 at 9:34 am
The thing is that I'm not updating anything. I just have a view through a linked server to an Oracle table that selects rows with conditions. It's a straight Insert as Select into an SS7.0 table. I don't really have any admin rights into the Oracle db.
June 19, 2003 at 11:50 am
This link will be helpful on oracle issue.
June 20, 2003 at 7:57 am
The problem most likely is that your query is
taking too long to run. Oracle is trying to give you a "point in time" result set. If there are updates to the data you are requesting and that data is in the rollback segments; if oracle now needs that rollback segment space, it will give you this error.
Perform the query when there is less action against the oracle database.
June 20, 2003 at 9:05 am
I have decided to revisit the query and break it up into smaller return sets. That should take the pressure off the Oracle rollback segment. Unfortunately the throughput of the linked server and select-on-views probably doesn't help the situtation. On an off-peak time, I ran the query that is giving me grief in the Query Analyzer. It returned 875,000 rows in 120 minutes. That's about 121 returned rows per second. Does that sound about right to everyone? Does anyone have a suggestion to increasing throughput or working around the SQL-to-Oracle bottlenecks?
June 20, 2003 at 10:24 am
Depending on the hardware the machines are running on, version of Oracle, version of the ODBC driver that rate seems reasonable. You are bringing back 875,000 rows. That is a lot of data.
I would check to see if there is any network latency issues.
As well as investiage using Oralce Loader? (their version of BCP) to just dump the data out to a flat file zip it up, copy it over and have a DTS job load it up.
Henry
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply