1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'General' started by sree0213, Jan 10, 2010.

  1. sree0213

    sree0213 Guest

    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.

  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    Google is your friend:


    In particular start with this example: