Re: deleting many rows from a table

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 14 Jan 2010 06:43:43 -0800 (PST)
Message-ID: <41a6185a-dc25-4428-b81d-fdb323fd41b1_at_j19g2000yqk.googlegroups.com>

On Jan 14, 8:06 am, UXDBA <unixdb..._at_googlemail.com> wrote:
> Thanks Gints .
>
> But Live table T will have ongoing transaction.
>
> So step#2 would also require downtime?
>
> On Jan 14, 12:53 pm, Gints Plivna <gints.pli..._at_gmail.com> wrote:
>
> > How about:
> > 1) CREATE table t1 with the same structure and necessary storage
> > definitions
> > 2) INSERT /*+ append */ INTO t1 select only necessary rows
> > 3) drop old table t
> > 4) RENAME t1 to t;
>
> > Of course it means, that all privileges should be regranted and
> > dependant procedural units recompiled.
>
> > Minimum downtime (only steps 3 and 4) - the only problem is need for
> > extra space and recompile units/regrant privileges.
>
> > Gints Plivnahttp://www.gplivna.eu
>
> Thanks

Yes, data loss is possible with Gints plan if you cannot stop DML activity to the table while the copy is being made.

The dbms_redefinition package is your only real option if no downtime can be taken.

I would rather get a window and if space is available use the ATLER TABLE MOVE and ALTER INDEX REBUILD commands to handle the reogranization. If free space is not available then you need to use export/truncate/import or drop and re-create in place of truncate since even redefinition requires adequate free space to duplicate the table and indexes exists plus you need space to track the DML activity.

HTH -- Mark D Powell -- Received on Thu Jan 14 2010 - 08:43:43 CST