Sameer Could you please share you case which will not work option 1. It will be very helpful for me cover that at my current place. To my understanding DBMS_REDEFINTION does create staging table then load data from source table and finally does partition swap with target table. To compare table data I have used DBMS_COMPARISON and MINUS. Karth > On Dec 12, 2014, at 2:54 AM, Sameer Choudhari <sameer.choudhari@xxxxxxxxx> > wrote: > > Hi Seth > > Yes, you are correct. Initially I thought of using Karth's OPTION-1 but that > also not feasible for my case. I am not confident enough to opt for OPTION-2 > as well. I will try to test my ONLINE REDEFINITION option in dev box and > implement in PROD. At this moment, I am not sure how long it will take to > complete the task. Also, need to increase another 600GB as DBMS_REDEFINITION > creates MLOG$ . (as per Doc ID 1481558.1). > > Cheers, > Sameer > >> On 12 December 2014 at 09:48, Seth Miller <sethmiller.sm@xxxxxxxxx> wrote: >> Sameer, >> >> You mentioned that you define the new table structure with DBMS_REDEFINITION >> but use imp-exp to load it. Is there a reason you don't use >> DBMS_REDEFINITION to sync the tables? >> >> Assuming the data in this table is changing rapidly and is important to the >> business, are you really going to rely on a matching row count to determine >> that the data is synchronized between the two tables? >> >> I'm trying not to sound mean but you were on the right track to achieve your >> task by using DBMS_REDEFINITION and then you jumped the track flipped three >> times and and hit a wall. >> >> Seth Miller >> >> >>> On Thursday, December 11, 2014, Sameer Choudhari >>> <sameer.choudhari@xxxxxxxxx> wrote: >>> Hi Karth >>> >>> Thanks for your tips. I will try to use Option-1 and update once >>> implemented. >>> >>> Cheers, >>> Sameer >>> >>>> On 10 December 2014 at 21:41, Karth Panchan <keyantech@xxxxxxxxx> wrote: >>>> Sameer >>>> >>>> I have handled with 10TB database in compressed at Banking. >>>> >>>> You can approach this 2 way. >>>> >>>> Option 1:(steps in sequences) >>>> >>>> Create table New with Partition definition goes here >>>> As select * from current table. >>>> >>>> Rebuild indexes with new name >>>> Create Ref constraints with disable with new name. >>>> Create triggers with new name >>>> (Above 3 steps you need to take definitions from Dictionary tables for >>>> your current table) >>>> >>>> Verify record count between New and current table >>>> >>>> When match then >>>> >>>> Rename current table as backup >>>> Rename new as current >>>> Disable backup table Ref constraints >>>> Drop/disable triggers on backup table >>>> >>>> End >>>> >>>> In this approach we need extra 600GB but safe method for rollback option >>>> or anything goes wrong you have backup. Instead rename you can Drop backup >>>> table to save storage with caution. You can compress old data partitions >>>> to save storage. >>>> >>>> Option 2: >>>> >>>> This approach assume all data in this table are History+Current. History >>>> data which never have DML activities. >>>> >>>> Create table New with Partition definition goes here >>>> As select * from current table >>>> Where date < your current dataset date. >>>> >>>> Create table Staging (non-partition but same structure as current) >>>> As select * from current table where 1=2 >>>> >>>> Here you will pre- stage New table with history data. Run job daily/weekly >>>> to load current data to Staging table and do partition swap. >>>> >>>> Finally data sync is done you can rename tables. >>>> >>>> I implemented both solutions. >>>> >>>> Hope this helpful to you. >>>> >>>> Karth >>>> >>>> >>>>> On Dec 10, 2014, at 4:10 AM, Sameer Choudhari >>>>> <sameer.choudhari@xxxxxxxxx> wrote: >>>>> >>>>> Hello Oracle-L >>>>> >>>>> Oracle DB : 10.2.0.4 >>>>> OS : IBM AIX >>>>> DB SIZE : 5TB >>>>> Table Size : 600GB >>>>> Application : Baking System - OLTP. Busy system. >>>>> >>>>> I have to RANGE partition on date field. Looking at the size of the >>>>> table, I am confused which method to follow. In the past I have performed >>>>> using DBMS_REDEFINITION and IMP-EXP feature for small tables i.e. <50GB. >>>>> This time, I am worried by looking at the size of the table. Could you >>>>> please suggest better or reliable way partitioning with minimum downtime >>>>> or no downtime.? >>>>> >>>>> Cheers, >>>>> Sameer