| drop and recreate new partition [message #332196] |
Mon, 07 July 2008 16:50  |
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 #332436 is a reply to message #332418] |
Tue, 08 July 2008 09:18  |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
