Home » SQL & PL/SQL » SQL & PL/SQL » drop and recreate new partition
drop and recreate new partition [message #332196] Mon, 07 July 2008 16:50 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

i need to create and automate the job for creating and dropping partitions for some of the table. The range partition need to be create on the some tables on the daily basis and drop the oldest paritition. Please help for building some approach for this script.
Re: drop and recreate new partition [message #332197 is a reply to message #332196] Mon, 07 July 2008 16:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Do you have a solution in search of a problem?
Please provide evidence that daily partitions will provide measurable benefits.

[Updated on: Mon, 07 July 2008 19:59] by Moderator

Report message to a moderator

Re: drop and recreate new partition [message #332252 is a reply to message #332196] Tue, 08 July 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The partition is automatically created if you use an interval partitioning.

Regards
Michel
Re: drop and recreate new partition [message #332407 is a reply to message #332252] Tue, 08 July 2008 08:14 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

Micheal

Can we use interval partition prior to 11g or in Release 9.2.0.1.0.

Thanks for the help..
Re: drop and recreate new partition [message #332418 is a reply to message #332407] Tue, 08 July 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No but as you didn't post your version, I choosed to answer to the one I want.

Regards
Michel
Re: drop and recreate new partition [message #332436 is a reply to message #332418] Tue, 08 July 2008 09:18 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

I have this table with the range partition init. I am not looking for detail answer, any clue or hint to automate the process of creating and droping the partition on this table. Meaning before inserting the data of that date, it should automatically create new partition of that day and drop the oldest partition. That's how it can keep only one week data init.


create table AT_table
(
  CT_TIMESTAMP          DATE,
  CT_user            VARCHAR2(50),
  CT_acct          VARCHAR2(2),
  ac_nbr                NUMBER(10),
  land_id           NUMBER(10),
  business_ID            NUMBER(10),
  DATE_ORDERED          DATE,
  DATE_PERFORMED        DATE,
  DATE_RECEIVED         DATE

partition by range (CT_TIMESTAMP)
(
partition P20080522 values less than (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
partition P20080522 values less than (TO_DATE(' 2008-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
partition P20080522 values less than (TO_DATE(' 2008-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
partition P20080522 values less than (TO_DATE(' 2008-07-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
partition P20080522 values less than (TO_DATE(' 2008-07-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
partition P20080522 values less than (TO_DATE(' 2008-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
partition P20080522 values less than (TO_DATE(' 2008-07-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
Re: drop and recreate new partition [message #332447 is a reply to message #332436] Tue, 08 July 2008 09:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm#i2133032

In the above link search for add and drop partition. You will find examples how to do it.

Quote:

...
partition P20080522 values less than (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
partition P20080522 values less than (TO_DATE(' 2008-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));


I don't think this is a valid statement. Anyways it looks like you are creating a partition in this format pYYYYMMDD. So it will be something like this.

Step 1 : Get the date for which you want to create the partition and create the partition using add partition
Step 2 : subtract current date - 8 days and format it using to_char to get the right format
Step 3 : build your paritition name string from that
Step 4 : check in user_tab_partitions if you could find the partition for that table
Step 5 : If found drop the partition

Hope that helps.

regards

Raj
Re: drop and recreate new partition [message #332460 is a reply to message #332436] Tue, 08 July 2008 10:21 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or use a rolling partitioning.
Read the following thread:
http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=24757

Regards
Michel
Previous Topic: Finding distinct elements from Array
Next Topic: problem while executing Ref cusror
Goto Forum:
  


Current Time: Thu Dec 08 21:53:33 CST 2016

Total time taken to generate the page: 0.15195 seconds