delete data from table without rollback/logging?
Steve Salvemini
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
Howard J. Rogers
HJR
--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================
"Steve Salvemini" <steve.s...@adelaide.edu.au> wrote in message
news:3BBD190B...@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
Pete's
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
4. do another create table as to the production tablename and
tablespace
5. import the export file.
HTH,
Pete's
Steve Salvemini <steve.s...@adelaide.edu.au> wrote in message news:<3BBD190B...@adelaide.edu.au>...
Ronald
> 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.
copy the 10% to a table using 'insert /*+append*/ into x as select *
from y', 'truncate table y'
and 'insert /*+append*/ into y select * from x'
is the fastest way to do this. Don't forget it creates 'holes' in the
redolog files. Normally it is better to take a backup after this
process is ready since a rollforward from the previous backup will and
up with empty tables ...
(assuming >= oracle 8i)
Ronald.
-----------------------
http://ronr.nl/unix-dba
Sybrand Bakker
There is not.
Regards,
Sybrand Bakker, Senior Oracle DBA
Yong Huang
discrete transactions do not generate undo.
Yong Huang
yon...@yahoo.com
"Howard J. Rogers" <howa...@www.com> wrote in message news:<3bbd...@news.iprimus.com.au>...
Howard J. Rogers
used to be hidden, but it is in 8i and 9i, -and we all know what hidden
parameters mean: "unsupported, and on your own head be it").
But in any case, "regular" old DMLs can't make use of it, even so.
Regards
HJR
--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================
"Yong Huang" <yon...@yahoo.com> wrote in message
news:b3cb12d6.01100...@posting.google.com...
Yong Huang
procedure. It's in Chapter 16 of the Concept manual (8.1.7 or 8.1.6
documentation).
Yong Huang
yon...@yahoo.com
"Howard J. Rogers" <howa...@www.com> wrote in message news:<3bbe...@news.iprimus.com.au>...
Howard J. Rogers
Clearly, in 8i and above, they've made it less of a cloak-and-dagger affair
that it used to be by knocking up a package that does what the parameter
used to do. Big deal: you'll appreciate that discrete transactions have
specific uses, and are unsuited for "regular" DML activity however they are
enabled. In particular, they are designed to be used for short transactions
that modify only a few blocks, and which never change an Oracle block more
than once. That is not "regular DML" in my book, and doesn't sound like the
kind of thing our original poster was wanting to do in any case.
Dino Hsu
<howa...@www.com> wrote:
>Yes, and the hidden parameter is called _enable_discrete_transactions.
>Clearly, in 8i and above, they've made it less of a cloak-and-dagger affair
>that it used to be by knocking up a package that does what the parameter
>used to do. Big deal: you'll appreciate that discrete transactions have
>specific uses, and are unsuited for "regular" DML activity however they are
>enabled. In particular, they are designed to be used for short transactions
>that modify only a few blocks, and which never change an Oracle block more
>than once. That is not "regular DML" in my book, and doesn't sound like the
>kind of thing our original poster was wanting to do in any case.
>
>HJR
In addition to autonomous transaction, this discrete transaction thing
is kinda interesting. From the doc, it says
dbms_transaction.begin_discrete_transaction() is used to start a
discrete transaction (how to end?), but no details. I need to know if
this is suitable for deleting 1 million records from a 50 million
records table without undo. Thanks in advance.
Dino
Howard J. Rogers
"Dino Hsu" <dino1....@ms1.hinet.net> wrote in message
news:omsvrtkbdq29von1d...@4ax.com...
> On Sun, 7 Oct 2001 08:22:20 +1000, "Howard J. Rogers"
> <howa...@www.com> wrote:
>
> >Yes, and the hidden parameter is called _enable_discrete_transactions.
> >Clearly, in 8i and above, they've made it less of a cloak-and-dagger
affair
> >that it used to be by knocking up a package that does what the parameter
> >used to do. Big deal: you'll appreciate that discrete transactions have
> >specific uses, and are unsuited for "regular" DML activity however they
are
> >enabled. In particular, they are designed to be used for short
transactions
> >that modify only a few blocks, and which never change an Oracle block
more
> >than once. That is not "regular DML" in my book, and doesn't sound like
the
> >kind of thing our original poster was wanting to do in any case.
> >
> >HJR
>
> In addition to autonomous transaction, this discrete transaction thing
> is kinda interesting. From the doc, it says
> dbms_transaction.begin_discrete_transaction() is used to start a
> discrete transaction (how to end?),
As all transactions are ended... by a commit or a rollback.
>but no details. I need to know if
> this is suitable for deleting 1 million records from a 50 million
> records table without undo. Thanks in advance.
I doubt it. Read the doco. again. It states it's for SMALL transactions,
and ones which don't touch the same block twice.
But YMMV -try it and see. The worst that can happen is that an error will
be thrown the minute it decides that what you are trying *needs* rollback.
Regards
HJR
>
> Dino
>
Steve Salvemini
--
-----------------------------------------------------------
Steven Salvemini
Peoplesoft Technical Specialist, Information Technology Services
ADELAIDE UNIVERSITY SA 5005
AUSTRALIA
Tel: +61 8 8303 6358 Fax: +61 8 8303 4400
Email: steve.s...@adelaide.edu.au
-----------------------------------------------------------
This email message is intended only for the addressee(s)
and contains information which may be confidential and/or
copyright. If you are not the intended recipient please
do not read, save, forward, disclose, or copy the contents
of this email. If this email has been sent to you in error,
please delete this email and any copies or links to this
email completely and immediately from your system. No
representation is made that this email is free of viruses.
Virus scanning is recommended and is the responsibility of
the recipient.