Re: delete data from table without rollback/logging?

Unfortunately, Oracle does not allow the bypassing of the rollbacks on dml statements. If this is a rather large table, you have already mentioned your best solution and that is to copy out your 10% into a temporary table, truncate the production table, then copy the data back in. But instead of doing the table creates before hand, do something like the following:

1. Export your current production table you are deleting with rows=N, just to get the structure.
2. run something like the following: create table temp_table tablespace new_tablespace as select * from production_table where <the rows I want are selected> --note on this CTAS statement, you could put an ordered hint on the select to order the rows into the temp table
3. Drop the production table.
4. Rename temp_table to production table
5. import in the export file to get constraints, indexes and grants or
6. do another create table as to the production tablename and tablespace
7. import the export file.

HTH,
Pete's

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.
>
> I see 'alter table xxx nologging' doesn't seem to be affected by
> deletes.
>
> Ideally an 'alter session nologging' statement would be useful.
>
> Also, I would have dedicated use of this database, if that helps with
> any ideas.
>
>
> Cheers
>
> Steve
Received on Fri Oct 05 2001 - 08:09:39 CDT