Re: bypass rollback for large insert statements

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.