partition table on daily basis and delete old partitions on weekly basis

[sree0213]

Please let me know how we can partition on day basis. And delete partition on daily basis.

1) We have one current old data base which has 59 un-partitioned Tables with 300 days of data.
Now we need write a script to make a new Database oracle 10g with same table definition but Portioned on txn_date(one partition a day) and load the data from current DB.
We have txn_date field as date field in all tables.
We are starting from txn_date from 01 Aug 2009 to create partitions on each day and name the partition as partition_MMDDYY to till today.
So that we will have almost 180 partitions by the end of January 2010.
2) After that we need to create 4 weeks of empty partitions i.e. 28 partitions from February 1st 2010 onwards.
Same name standard: partition_MMDDYY.

3) After one week we need to delete and drop all the partitions that are older than 180 days. (partion_MMDDYY) and REUSE the table space
And we need to create one week of daily partitions.



Thanks

[zargon]

Please let me know how we can partition on day basis. And delete partition on daily basis.

1) We have one current old data base which has 59 un-partitioned Tables with 300 days of data.
Now we need write a script to make a new Database oracle 10g with same table definition but Portioned on txn_date(one partition a day) and load the data from current DB.
We have txn_date field as date field in all tables.
We are starting from txn_date from 01 Aug 2009 to create partitions on each day and name the partition as partition_MMDDYY to till today.
So that we will have almost 180 partitions by the end of January 2010.
2) After that we need to create 4 weeks of empty partitions i.e. 28 partitions from February 1st 2010 onwards.
Same name standard: partition_MMDDYY.

3) After one week we need to delete and drop all the partitions that are older than 180 days. (partion_MMDDYY) and REUSE the table space
And we need to create one week of daily partitions.



Thanks

Google is your friend:

http://www.google.com/search?hl=en&s...tition+&aqi=g7


In particular start with this example:

http://www.oracle-base.com/articles/...AndIndexes.php