Change Partiton Scheme
I'm using Oracle 18.104.22.168.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:
The sql for the new partiton scheme is:
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'))
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?
partition range ("l_date") interval (numtodsinterval(7, 'DAY'))
(partition "t_01" values less than (timestamp ' 2001-01-07 00:00:00'))
This: '2001-01-07 00:00:00' is not a full timestamp format but rather a date format, why change?
Originally Posted by cyclegeek
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
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
Click Here to Expand Forum to Full Width