Re: Commit during insert

mark.powell_at_eds.com (Mark D Powell) wrote in news:178d2795.0108190707.5b43c07b_at_posting.google.com:

>> >> I'm going to pull data from a remote table, insert it into a local
>> >> table, and I want to perform commits 
>> 
>> Be aware that if you commit, and fetch from an open cursor you stand
>> the chance to hit ORA-01555.  If you really want to commit along the
>> way.  Open and close the select cursor. 

It doesn't have to be the same table, same database. When a commit is issued the transaction that started the query local or remote is now over. The potential for the RBS where the query header info is stored is now free to be overwritten. If that header is overwritten you will receive ...

> the 'snapshot too old' ORA-01555 error would be a real possibility;
> however, since the original post was about reading a remote table into
> a local table this is not a high probability, but if the error were to
> occur I think the first step would be to try to increase the row count
> between commits rather than close and reopen the cursor since they
> have no efficient way of identifing which rows have already been
> processed.

Track your file/extents/blocks of the rows read. I have done it many times. Commit at the end of an file/extent/block combination. Re-open the query with
where rowid between begin_rowid and end_rowid;

Take advantage of dbms_rowid

>

Just pointing out the possible problem for those that will read this thread and apply it generically. Also I would most likely just use the copy statement in SQLPLUS. It is very efficient and already has the commit capability.

Not seeing the origional thread this may also not apply, but it's just how I would have approached the problem.

-- 
Robert Fazio
Senior Technical Advisor
dbabob_at_yahoo.com