Re: bypass rollback for large insert statements

ddf_dba_at_my-deja.com wrote:
>
> In article <39535DE8.AFFE842C_at_byteme.com>,
> jim shorts <jim.short_at_byteme.com> wrote:
> > I have an application that is updating a very large Oracle 7.3.4
> > database. at the end of the application are some insert statements
 that
> > affect about 100K rows of data. i've tested the statements and I'm
 sure
> > they complete successfully. I'm trying to find out if their is a
 clause
> > I can add to the insert statements that will bypass the rollback
> > segments and just update the database. For example:
> > insert into tablename (col1, col2, col3) values (data, data+1, data)
> > norollback;
> >
> > any input would be greatly appreciated. thanks.
> >
> >
>
> As far as I am aware there is no INSERT command parameter to shut off
> creation of rollback during a transaction. There IS a method of
> avoiding creation of redo log entries and that is to create the table as
> UNRECOVERABLE (NOLOGGING in Oracle 8). The table creation is not logged
> nor are any transactions to the table. This makes the table impossible
> to recover after a database crash if the table has not been exported
> prior to the incident since no record of the transactions exist in the
> archived redo logs.
>
> A possible, but in my mind dangerous, solution would be to set
> 'autocommit' on for the transactions in question; this would possible
> reduce the amount of rollback generated since each insert would be
> committed upon completion. This also eliminates any possibility of
> transaction recovery (rollback) and could inadvertently invalidate data
> if an incorrect value was inserted into a table by mistake.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Have a look at direct mode insert (8.0+)

insert /*+ APPEND */ into table
select ...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse