Change Partiton Scheme
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Change Partiton Scheme

Hybrid View

  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Change Partiton Scheme

    I'm using Oracle 11.2.0.3.0 and I need to change the partioning scheme on a table from a date field to a timestamp field.

    The sql for our old partition scheme is:
    Code:
    partition by range ("e_date") interval (numtodsinterval(7, 'DAY'))
    (partition "t_01" values less than (to_date(' 2001-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    The sql for the new partiton scheme is:

    Code:
    partition range ("l_date") interval (numtodsinterval(7, 'DAY'))
    (partition "t_01" values less than (timestamp ' 2001-01-07 00:00:00'))
    Is this OK? In testing it seems to work but after reading the docs I'm still a bit confused on partitioning. Does anyone have any suggestions?

    Thanks,
    CG

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Cool

    Quote Originally Posted by cyclegeek View Post
    . . . E t c . . .
    Code:
    partition range ("l_date") interval (numtodsinterval(7, 'DAY'))
    (partition "t_01" values less than (timestamp ' 2001-01-07 00:00:00'))
    Is this OK? In testing it seems to work but after reading the docs I'm still a bit confused on partitioning. Does anyone have any suggestions?
    This: '2001-01-07 00:00:00' is not a full timestamp format but rather a date format, why change?
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    As LkBrown mentioned, the part that does the partitioning is "interval (numtodsinterval(7, 'DAY'))" and that is creating a new partition every 7 days. You can also create a new partition every day, which isn't as bad as it sounds, given that you will have on average 3,653 partitions created over 10 years. If you have a large table and you routinely query for the last day, it can help.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width