OraFAQ Forum: Backup & Recovery » Managing partition table please help

Home » RDBMS Server » Backup & Recovery » Managing partition table please help (10g Enterprise Edition Release 10.2.0.4.0.)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Managing partition table please help [message #503242]

Wed, 13 April 2011 13:14

kbkbharath
Messages: 11
Registered: August 2010

Junior Member

we have a table REVENUE. It is partition on "month wise" and it has data since 2009. Each partition has atleast
50000000 record. Since each partition size has become huge and more records will be added in future
we are plan to backup the entire table and have only last one year data in REVENUE table. Since
the table is huge we cannot use delete to purge last one year record after taking backup of REVENUE table
So we have proposed below way, if you have any better idea please let us know it will be helpful.

1) Create table REVENUE_BK with similar month wise partition and indexing.
2) Create a TEMP table similar strucure of revenue.
3) Use partition exchange load and transfer data from REVENUE table to temp table
4) Again transfer the data from temp table to REVENUE_BK table.
5) Rebuild index and cons on both REVENUE_BK and REVENUE table.
6) insert one year old data from REVENUE_BK to REVENUE table to respective partition.
7) Repeat from step 1 for all remaining 11 partition.

Report message to a moderator

Re: Managing partition table please help [message #503244 is a reply to message #503242]

Wed, 13 April 2011 13:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1) You don't need to rebuild indexes if they are local and if the TEMP table has the same indexes
2) For global indexes, only rebuild them at then end

Regards
Michel

Report message to a moderator

Re: Managing partition table please help [message #503245 is a reply to message #503242]

Wed, 13 April 2011 13:29

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Hi - are steps 2,3,4, and 5 needed? Will you get the same result from

alter table revenue rename to revenue_bk;

Or am I missing something?

Report message to a moderator

Re: Managing partition table please help [message #503257 is a reply to message #503245]

Wed, 13 April 2011 19:30

kbkbharath
Messages: 11
Registered: August 2010

Junior Member

Hi All,

Thanks for your reply... Is there anyother option since in my steps we need to select and load last one year data from revenue_bk it will take quite long time.. Any idea or other way of doing it will be great helpful. Thanks in advance..

John,
If we do rename revenue_bk then we have to create revenue again since all the jobs use revenue table and if we rename we need to recompile the jobs too. So anyother options?

Report message to a moderator

Re: Managing partition table please help [message #503264 is a reply to message #503257]

Wed, 13 April 2011 23:27

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In the end, I don't understand what you want.
Can you explain in details, what you currently have inline, you want to backup and what you want to keep inline.

Regards
Michel

Report message to a moderator

Re: Managing partition table please help [message #503323 is a reply to message #503264]

Thu, 14 April 2011 08:55

kbkbharath
Messages: 11
Registered: August 2010

Junior Member

Let me explain you with an example,

Currently Revenue had partition P01 to P12. let say we have records in P01 as Jan 2009 Jan 2010 and Jan 2011. Post our backup activity. We should have Jan 2010 and 2011 records in P01 of REVENUE table and all Jan 2009,2010 and 2011 records in REVENUE_BK P01 partition.

Before our Activity
---------------------------
Table Partition Records
REVENUE P01 Jan 2009,2010,2011
REVENUE P02 Feb 2009,2010,2011
REVENUE P03 Mar 2009,2010,2011
REVENUE P04 Apr 2009,2010,Yet to load
REVENUE P05 May 2009,2010,Yet to load
REVENUE P06 Jun 2009,2010,Yet to load
REVENUE P07 Jul 2009,2010,Yet to load
REVENUE P08 Aug 2009,2010,Yet to load
REVENUE P09 Sep 2009,2010,Yet to load
REVENUE P10 Oct 2009,2010,Yet to load
REVENUE P11 Nov 2009,2010,Yet to load
REVENUE P12 Dec 2009,2010,Yet to load

After our Activity
---------------------------
Table Partition Records
REVENUE P01 Jan 2010,2011
REVENUE P02 Feb 2010,2011
REVENUE P03 Mar 2010,2011
REVENUE P04 Apr 2010,Yet to load
REVENUE P05 May 2010,Yet to load
REVENUE P06 Jun 2010,Yet to load
REVENUE P07 Jul 2010,Yet to load
REVENUE P08 Aug 2010,Yet to load
REVENUE P09 Sep 2010,Yet to load
REVENUE P10 Oct 2010,Yet to load
REVENUE P11 Nov 2010,Yet to load
REVENUE P12 Dec 2010,Yet to load

Table Partition Records
REVENUE_BK P01 Jan 2009,2010,2011
REVENUE_BK P02 Feb 2009,2010,2011
REVENUE_BK P03 Mar 2009,2010,2011
REVENUE_BK P04 Apr 2009,2010
REVENUE_BK P05 May 2009,2010
REVENUE_BK P06 Jun 2009,2010
REVENUE_BK P07 Jul 2009,2010
REVENUE_BK P08 Aug 2009,2010
REVENUE_BK P09 Sep 2009,2010
REVENUE_BK P10 Oct 2009,2010
REVENUE_BK P11 Nov 2009,2010
REVENUE_BK P12 Dec 2009,2010

Report message to a moderator

Re: Managing partition table please help [message #503327 is a reply to message #503323]

Thu, 14 April 2011 09:14

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sorry but bad partitioning sheme.
If you make a difference between years then years should be in different partitions.

Regards
Michel

Report message to a moderator

Re: Managing partition table please help [message #503586 is a reply to message #503323]

Mon, 18 April 2011 03:21

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

It isn't a bad design to have "older" partitions containing more data than "newer" partitions. For example, the latest 12 months data are in monthly partitions, preceding 2 years data in quarterly partitions, preceding 3-8 years data in yearly partitions.

Such a design is part of a proper Information Lifecycle Management policy. Chapter 5 of the VLDB and Partitioning Guide in the Oracle Documentation covers this.

You can scroll down to the "Implementing an ILM System Manually" in Example 5-1 at http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/part_lifecycle.htm#CACEABBJ

What IS important is that you name the partitions more meaningfully. Your proposed partition names can be confusing when users/developers are looking at older data to compare with current data.

Hemant K Chitale

Report message to a moderator

Re: Managing partition table please help [message #503588 is a reply to message #503586]

Mon, 18 April 2011 03:25

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

It isn't a bad design to have "older" partitions containing more data than "newer" partitions. For example, the latest 12 months data are in monthly partitions, preceding 2 years data in quarterly partitions, preceding 3-8 years data in yearly partitions.

This is not the design you posted.

You posted that january of ALL years are in partition 1, february of ALL years are in partition 2 and so on.

Regards
Michel

Report message to a moderator

Re: Managing partition table please help [message #503605 is a reply to message #503588]

Mon, 18 April 2011 04:30

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

>You posted that january of ALL years are in partition 1, february of ALL years are in partition 2 and so on.

Yes. He has to redesign his partitioning scheme properly.

Hemant K Chitale

Report message to a moderator

Re: Managing partition table please help [message #503606 is a reply to message #503605]

Mon, 18 April 2011 04:44

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So you cannot use partition exchange and have to use INSERT SELECT.
Hoppefully this can be done in APPEND PARALLEL mode.

Regards
Michel

Report message to a moderator

Previous Topic:	Tranfer Database from Windows 2003 32 bit to Windows 2008 64 bit
Next Topic:	how to recover truncated table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 05:55:38 CDT 2024