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

Quote:

---

Originally Posted by cyclegeek

. . . 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?
:p

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.