Re: Oracle Partitioning - 600GB Table
- From: Seth Miller <sethmiller.sm@xxxxxxxxx>
- To: "sameer.choudhari@xxxxxxxxx" <sameer.choudhari@xxxxxxxxx>
- Date: Thu, 11 Dec 2014 17:48:13 -0600
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 <http://www.choudhari.net>
>
> On 10 December 2014 at 21:41, Karth Panchan <keyantech@xxxxxxxxx
> <javascript:_e(%7B%7D,'cvml','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
>> <javascript:_e(%7B%7D,'cvml','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 <http://www.choudhari.net>
>>
>>
>
Other related posts:
