Re: deleting many rows from a table
Date: Thu, 14 Jan 2010 10:01:54 -0800 (PST)
On Jan 14, 2:43 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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
> HTH -- Mark D Powell --- Hide quoted text -
> - Show quoted text -
dbms_redefinition yes I can see in 9.2
I have checked table "T" and found it suitable for online redefintion.
Further, we have plenty of storage space but would prefer to have "no" outage.
Mark , why would you prefer alter table move.... if online redifinition is available. do you see we hitting any bug...or this method not safe?
Regards Received on Thu Jan 14 2010 - 12:01:54 CST