Re: Partition exchange

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
>
>

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