Comments embedded.
HJR
--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================
"Steve Salvemini" <steve.salvemini_at_adelaide.edu.au> wrote in message
news:3BBD190B.D3B36442_at_adelaide.edu.au...
> Hi group, I'm running a cut down script on a copy of a live database to
> remove some 90%
> of the data.
>
> Currently I use delete statements to remove the data from 100's of
> tables.
> Another method I can use is to copy out 10% into a temp table, truncate
> the original and
> then copy the 10% back in, reducing the hit on the rollback segments.
>
>
> Regardless of which option I take, is there a faster option to delete
> and insert data without filling up the rollback segments.
>
Nope. It is utterly impossible to ever prevent rollback from being
generated by DMLs.
> I see 'alter table xxx nologging' doesn't seem to be affected by
> deletes.
>
Correct. Nologging (ie, switching off *REDO*, not rollback) is not possible
for regular DML. The nologging attribute is there for things like index
creationg, 'create table as select' commands, direct loads with SQL Loader,
and various other bits of DDL, mainly to do with partitions. Plain old DML
always generates redo.
> Ideally an 'alter session nologging' statement would be useful.
>
Well, I can see that it might be useful, but it isn't possible.
> Also, I would have dedicated use of this database, if that helps with
> any ideas.
>
Since this is a copy of a live database, and you could therefore (I presume)
tolerate complete database loss, you might consider sticking a
"_disable_logging" parameter (with an underscore at the front) in your
init.ora, and re-bouncing your Instance. That really *does* switch off all
redo generation. It's use is totally unsupported by Oracle, and if you have
an Instance crash when it's operational, forget about ever re-opening your
database. But if it stays up long enough to complete the operation you are
attempting, you can then shutdown the database *cleanly* (ie, at least an
'immediate'), remove the nasty parameter and start up again as normal.
The non-generation of redo should speed things up considerably.
You cannot, however, prevent the rollback from being generated.
Regards
HJR
>
> Cheers
>
> Steve
Received on Fri Oct 05 2001 - 04:42:51 CDT
