0

I have this challenge with creating a partition by a range in 0racle 11g. While I wrote a script top partition my table and scheduled a job to run every month. Unfortunately just discovered that partitions for the month of February weren't created. below is an image enter image description here

please i need help on how to add this partition vo_mo_202001 to vo_mo_20229.

below is my script

set echo off
set serveroutput on
set trimspool on
set colsep ';'
set numformat 9999999999999999.99
column tm new_value file_time noprint
select to_char(sysdate, 'YYYYMMDD') tm from dual ;
spool /home/oracle/scripts/partitionlogs/RAW_HUAWEI_MSC_VO_MO.&file_time..log
DECLARE
    partition_name VARCHAR2(30)  :=to_char(SYSDATE
        , 'yyyy_mm');
    partition_date VARCHAR2(30)    :=to_char(SYSDATE, 'yyyy-mm');
        count1 INTEGER;
        count2 INTEGER;
        count3 INTEGER;
BEGIN
    select count(*) into count1  from user_TAB_PARTITIONS where TABLE_NAME = UPPER('RAW_HUAWEI_MSC_VO_MO');

    dbms_output.put_line('PARTITIONS IN RAW_HUAWEI_MSC_VO_MO BEFORE RUNNING SCRIPT:');
    dbms_output.put_line(count1);
    
EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'1"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-1 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'2  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-3 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'03"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-4 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'04"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-5 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'05"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-6 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'06"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-7 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'07"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-8 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'08"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-9 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'09"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-10 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'10"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-11 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'11"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-12 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'12"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-13 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'13"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-14 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'14"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-15 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'15"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-16 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'16"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-17 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'17"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-18 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'18"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-19 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'19"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-20 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'20"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-20 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'21"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-21 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'22"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-22 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'23"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-23 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'24"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-24 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'25"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-25  23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'26"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-26 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'27"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-27 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
 EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'28"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-28  23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS';
   EXECUTE IMMEDIATE  'alter table RAW_HUAWEI_MSC_VO_MO add PARTITION "VO_MO_'||partition_name||'29"  VALUES LESS THAN (TIMESTAMP'' '||partition_date||'-29 23:59:59'') 
  PCTFREE 5 PCTUSED 40 INITRANS 4 MAXTRANS 255  NOLOGGING 
  STORAGE(INITIAL 32768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MSC_RAN_ETL_DATA_TBS" COMPRESS FOR ALL OPERATIONS'; 
  
  select count(*) into count2  from user_TAB_PARTITIONS where TABLE_NAME = UPPER('RAW_HUAWEI_MSC_VO_MO');

    dbms_output.put_line('PARTITIONS IN RAW_HUAWEI_MSC_VO_MO AFTER RUNNING SCRIPT:');
    dbms_output.put_line(count2);
    
    select (count2 - count1) into count3 from dual;
    
    dbms_output.put_line('PARTITIONS CREATED IN RAW_HUAWEI_MSC_VO_MO AFTER RUNNING SCRIPT:');
    dbms_output.put_line(count3);
    
    EXCEPTION 
    WHEN OTHERS THEN
    select count(*) into count2  from user_TAB_PARTITIONS where TABLE_NAME = UPPER('RAW_HUAWEI_MSC_VO_MO');

    dbms_output.put_line('PARTITIONS IN RAW_HUAWEI_MSC_VO_MO AFTER RUNNING SCRIPT:');
    dbms_output.put_line(count2);
    
    select (count2 - count1) into count3 from dual;
    
    dbms_output.put_line('PARTITIONS CREATED IN RAW_HUAWEI_MSC_VO_MO AFTER RUNNING SCRIPT:');
    dbms_output.put_line(count3);
    
    
END;
/
spool off
Improve this question
  • HI Justin, the 0301 does not contain any of the February data. I tried to split the partitions but am getting an error "ORA-14080: partition cannot be split along the specified high bound". when i check for the bound values, am getting for this month SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = 'RAW_HUAWEI_MSC_VO_MO' ORDER BY partition_name. so i would i go about this splitting between january and march? – samurai Nov 8 '20 at 14:18
  • no partition has data for february, i scheduled a job to ran every month, unfortunately it only created partitions for month January, march to date. when i started loading data for the particular months, unfortunately only discovered that the february partitions are not existing – samurai Nov 9 '20 at 8:41
  • You are doing range partitions with values less than <<timestamp>>. If there is a partition that stores values less than March 1 and another partition that stores values less than January 31 then the March 1 partition would have all of the February data. It isn't possible for there to be no partition for February data unless it exceeds the max value. So I'm back to expecting that the 0301 partition would include all the February values. What was the command you used to split the 0301 partition into separate partitions for February. – Justin Cave Nov 9 '20 at 10:22
  • You may consider Interval Partitioning, then Oracle will create new partitions automatically and you don't have to care about it. Why do you put all these options in the command string? Most of them are default, you can simply skip them – Wernfried Domscheit Nov 9 '20 at 21:58
  • The February partition was accidentally deleted. hence the need to recreate all the February partitions. – samurai Nov 10 '20 at 13:26

Your Answer

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

Browse other questions tagged or ask your own question.