-4

I have a table with monthly partitions on it. I was asked to change those monthly partitions to daily partitions. For that, can I do even if the data exists ? What prerequisites do I need to take care. I have never worked on partitions. Please help me. The existing partitions on the table are as

PT_20190831_M,PT_20190930_M, PT_20191031_M etc.

which has definition TO_DATE(' 2019-08-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN').

And these are Range partitions.

2

Sure, it is called splitting a partition.

Splitting a Partition of a Range-Partitioned Table

The below example (taken from the above URL) creates a table with quarterly partitions, then splits a quarter into monthly partitions:

CREATE TABLE orders
 (prod_id       NUMBER(6),
  cust_id       NUMBER,
  time_id       DATE,
  channel_id    CHAR(1),
  promo_id      NUMBER(6),
  quantity_sold NUMBER(3),
  amount_sold   NUMBER(10,2)
  ) 
  PARTITION BY RANGE (time_id)
  (PARTITION sales_q1_2016 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy')),
   PARTITION sales_q2_2016 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy')),
   PARTITION sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')),
   PARTITION sales_q4_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')) 
   );

ALTER TABLE orders 
  SPLIT PARTITION sales_q4_2016 INTO
  (PARTITION sales_oct_2016 VALUES LESS THAN (TO_DATE('01-NOV-2016','dd-MON-yyyy')),
   PARTITION sales_nov_2016 VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy')),
   PARTITION sales_dec_2016
   )
  ONLINE;

You can use the same concept for splitting monthly partitions to daily partitions.

  • Thanks for the detailed script. Will do in the same format. Thanks once again – Swetha Oct 2 '19 at 19:43
  • Am getting the below Error when am trying to convert into daily partition - ORA-14805: last resulting partition cannot contain bounds 14805. 00000 - "last resulting partition cannot contain bounds" *Cause: An ALTER TABLE SPLIT PARTITION contained a description for the last partition which included the bounds. *Action: Do not specify bounds for the last resulting partition. – Swetha Oct 3 '19 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.