Re: Partition exchange

I just realized I only need to verify step 2 which happens on empty tables anyway, so no damage done there.
I don't even have to dry run /test step 1 - if it fails then the whole process will abort.

So we can close this SR.

Thanks, Ranko

On 4/18/06, tboss_at_bossconsulting.com <tboss_at_bossconsulting.com > wrote:
>
> If in step 1, you did something like this:
>
> create table temp_holding as select * from partitioned_table partition
> (123);
>
> then you'd have your temp holding table to manipulate in step 2, and if
> your updates/changes during step 2 fail, you've never modified the data
> in the original partition. Even if the exchange partition back fails,
> you still have the orig. table unmodified.
>
> For what its worth, doing the CTAS operation and creating local indexes
> cannot possibly take that long; we do something similar to this process
> on a regular basis with partition row counts up to 300k, and doing the
> CTAS takes mere seconds. Re-indexing can be expensive ... but it takes
> only a few seconds per index to create local indexes on the table to be
> exchanged back. (we've got 130 indexes, and it can take about 20 mins
> to create them all ... so call it 25-30 seconds per index).
>
> this is the way i'd go.
>
> my 2 cents, tboss
>
>
> >
> > Hi List,
> > I have 2 step exchange partition process (
> > step 1 ) from source partitioned table to non-partitioned temp;
> > step 2) from temp to target partitioned table ).
> > I am trying to find a way to revert changes to initial state if proces
> fail=
> > s
> > in step 2.
> > I know I can reexecute exchange partition from step 1 but that would
> imply
> > rebuilding indexes first.
> > Creating dummy partition is also not an option for the same reason (
> there
> > is max partition in this table; which implies partition splitting, which
> > will
> > again make indexes unusable ).
> > I could create dummy table as exact copy of the source table on the fly,
> an=
> > d
> > dry run with it. This looks too much trouble though.
> >
> > Any ideas ?
> >
> >
> > --
> > Regards,
> > Ranko Mosic
> > Contract Senior Oracle DBA
> > B. Eng, Oracle 10g, 9i Certified Database Professional
> > Phone: 416-450-2785
> > email: mosicr_at_rogers.com
> >
> http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosic=
> > Main.html
> >
> > ------=_Part_27022_28701510.1145391271571
> > Content-Type: text/html; charset=ISO-8859-1
> > Content-Transfer-Encoding: quoted-printable
> > Content-Disposition: inline
> >
> > <div>Hi List, </div>
> > <div>I have 2 step exchange partition process ( </div>
> > <div>step 1 ) from source &nbsp;partitioned table to non-partitioned
> temp; =
> > </div>
> > <div>step 2) from temp to target partitioned&nbsp;table ). </div>
> > <div>I am trying to find a way to revert changes to initial state if
> proces=
> > fails in step 2. </div>
> > <div>I know I can reexecute exchange partition from&nbsp;step 1 but that
> wo=
> > uld imply rebuilding indexes first.&nbsp;</div>
> > <div>Creating dummy partition is also not an option for the same reason
> ( t=
> > here is max partition in this table; which implies&nbsp;partition
> splitting=
> > , which will&nbsp;</div>
> > <div>again make indexes unusable ).</div>
> > <div>I could create dummy table as exact copy of the source
> table&nbsp;on t=
> > he fly,&nbsp;and dry run with it. This looks too much trouble
> though.</div>
> > <div>&nbsp;</div>
> > <div>Any ideas ?</div>
> > <div>&nbsp;&nbsp;<br clear=3D"all"><br>-- <br>Regards, <br>Ranko
> Mosic<br>C=
> > ontract Senior Oracle DBA<br>B. Eng, Oracle 10g, 9i Certified Database
> Prof=
> > essional<br>Phone: 416-450-2785<br>email: <a href=3D"mailto:mosicr_at_rogers.c
> =
> > om">mosicr_at_rogers.com
> > </a><br><a href=3D"http://ca.geocities.com/mosicr@rogers.com/ContractSenior=
>
> > OracleDBARankoMosicMain.html">
> http://ca.geocities.com/mosicr@rogers.com/Con=
> > tractSeniorOracleDBARankoMosicMain.html</a> <br>&nbsp;</div>
> >
> > ------=_Part_27022_28701510.1145391271571--
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>
>

--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr_at_rogers.com
http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html

--
http://www.freelists.org/webpage/oracle-l