Re: delete data from table without rollback/logging?
Date: 5 Oct 2001 06:09:39 -0700
Message-ID: <6724a51f.0110050509.2e27c16a_at_posting.google.com>
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:
- Export your current production table you are deleting with rows=N, just to get the structure.
- 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
- Drop the production table.
- Rename temp_table to production table
- import in the export file to get constraints, indexes and grants or
- do another create table as to the production tablename and tablespace
- 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 - 15:09:39 CEST