Re: Commit during insert

Robert Fazio <dbabob_at_yahoo.nospam.com> wrote in message news:<Xns9103F7F4AD54dbabobyahoocom_at_24.12.106.199>...
> 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.
> >
> > If the original post was reading and inserting to the same table then
>
> 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.

In Reference to:
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 ...

--
What I said was that "If the original post was reading and inserting
to the same table then 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...."

I did not mean to imply that reading and updating the same table is
the only cause of 'snapshot' errors.  It was just the lead in to my
comment which got cut out of your reply.

Also the task's commiting would no effect on the retention of any
target data that is in the remote database's rollback segments.  It
will be the commit or rollback issued by the remote task that actually
changed the data that determines when the remote Oracle can overlay
the transaction header.  In the case where a snapshot is returned from
the remote instance then one possible way the poster may be able to
get around the error is to place an order by on a non-indexed column
in the select.  This will require Oracle to read and sort all the rows
before any of them can be fetched.  In this case either all of the
rows can be read or they can't before any inserts are attempted.

-- Mark D Powell --