-1

So i have this partition which makes an error because i search on google it said mostly like "the value is bigger than the partition"

PARTITION BY LIST (BUSINESS_DATE)   
    (PARTITION D20200326 VALUES (TO_DATE('26/03/2020','DD/MM/YYYY'))    
        PCTFREE 10 INITRANS 1 MAXTRANS 255
        STORAGE ( BUFFER_POOL DEFAULT)
        TABLESPACE DWH LOGGING NOCOMPRESS)
GO

This is the partition with the date '26/03/2020', but i wanted to make the partition is dynamic, so i have this ETL jobs, if the ETL jobs run on '27/03/2020' my ETL job error and doesn't want to insert, so i have to change the partition into 27/03/2020.

But how do i do this dynamically? 

PARTITION BY LIST (INSERT_DATE) 
    (PARTITION D20200326 VALUES (TO_DATE(SYSDATE))  
        PCTFREE 10 INITRANS 1 MAXTRANS 255
        STORAGE ( BUFFER_POOL DEFAULT)
        TABLESPACE DWH LOGGING NOCOMPRESS)
GO

i've tried with changing SYSDATE but only gave me an error

ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL Script line 23, statement line 23, column 47

PiPio
  • 89
  • 1
  • 3
  • 11

1 Answers1

1

If you need day-wise partition to be automatically created then you can use the INTERVAL partition which creates the partition as and when inserted data do not fall in any of the partition as follows:

CREATE TABLE YOUR_TABLE (
  ......
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
   PARTITION YT_PART_01 values LESS THAN (TO_DATE('02-JUN-2020','DD-MON-YYYY'))
);

It will automatically create the new partition if you insert any data beyond the 02-jun.

Refer to this documents regarding this feature.

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • so whenever i run my ETL jobs it would always create a new partition? if that so, isn't going to take so much 'space' ? Sorry if my questions is 'too basic' since i'm a newbie. – PiPio Jun 02 '20 at 09:52
  • No, Whenever your ETL job tries to insert the data which can not fit in any partition then it will create the new partition. and No, It will not take much space, It will just store the same kind of data in separate partitions. – Popeye Jun 02 '20 at 09:56