CODEWITHSUNDEEP
sqloracle-databasepartitioningpartitiondatabase-partitioning
mohka
mohka

Reputation: 11

Added Daily Partition before the insert

i have the following table

    CREATE TABLE "ICTPART_DAILY_SUMMARY" 
   (    
   
   "EVENT_START_DATE" DATE
   "ACCOUNTING_METHOD" CHAR(1), 
    ......etc 
    )
PARTITION BY RANGE ("EVENT_START_DATE") 
 (PARTITION "ICTPART_DAY_SUM_P20220218"  VALUES LESS THAN (TO_DATE(' 2022-02-219 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
 NOCOMPRESS LOGGING 
  TABLESPACE "TBS_ICTQUO_D" , 
PARTITION "ICTPART_DAY_SUM_P20220219"  VALUES LESS THAN (TO_DATE(' 2022-02-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 NOCOMPRESS LOGGING
  TABLESPACE "TBS_ICTQUO_D" , 
PARTITION "ICTPART_DAY_SUM_PMAX"  VALUES LESS THAN (MAXVALUE) 
 NOCOMPRESS LOGGING 
  TABLESPACE "TBS_ICTQUO_D" ) ;

my job run everyday morning and insert new lines to this table i want to add partition everyday before insert the new line when my job starts i mean before the insert please.

any ideas about that please ? and i need to keep the name of the partition like that one "ICTPART_DAY_SUM_P20220219" with changes of the day of course P20220220 .. etc

Upvotes: 0

Views: 259

Answers (1)

gsalem
gsalem

Reputation: 2028

You can use the alter table split partition, like this:

alter table ICTPART_DAILY_SUMMARY split partition ICTPART_DAY_SUM_PMAX at (to_date('20220220','yyyymmdd'))) into (partition ICTPART_DAY_SUM_P20220219, partition ICTPART_DAY_SUM_PMAX)

Look at the doc. You can also use interval partitioning :

PARTITION BY RANGE ("EVENT_START_DATE") interval(numtodsinterval(1,'day'))

this will create the partition for you automatically, although you cannot control the partition name. And you do not need to know the name of the partition to manage it, you just need to know a value in the 'range', for example:

alter table ICTPART_DAILY_SUMMARY truncate partition for (to_date('01012022','ddmmyyyy'))

Upvotes: 3

Related Questions