Re: Oracle Partitioning - 600GB Table
- From: Karth Panchan <keyantech@xxxxxxxxx>
- To: "sameer.choudhari@xxxxxxxxx" <sameer.choudhari@xxxxxxxxx>
- Date: Wed, 10 Dec 2014 06:41:58 -0500
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
Other related posts:
