Re: deleting many rows from a table
From: UXDBA <unixdba73_at_googlemail.com>
Date: Thu, 14 Jan 2010 10:01:54 -0800 (PST)
Message-ID: <6c25b17a-9caf-4be8-baa3-8fe1a1e18f4c_at_e37g2000yqn.googlegroups.com>
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
> activity.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 14 Jan 2010 10:01:54 -0800 (PST)
Message-ID: <6c25b17a-9caf-4be8-baa3-8fe1a1e18f4c_at_e37g2000yqn.googlegroups.com>
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
> activity.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -
Thanks Mark.
dbms_redefinition yes I can see in 9.2
http://www.dbspecialists.com/files/presentations/online_redef.html
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