rename partition script error

Posted by Roshan on Feb 09, 2018; 8:34am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/rename-partition-script-error-tp5170.html

Oracle Database 12.1.0.2
Red Hat Linux 7.1

Hi Erman,

I have a script which used to rename partitions to format MOBILE_INOCS_CBP_REC_<JULIAN DATE>.

the plsql script is shown below.

Declare

  c_table_name  varchar2(30):='MOBILE_INOCS_CBP_REC'; --specify name of table

  c_table_owner varchar2(30):='ARCHICOM'; --you can specify owner

  v_highvalue   varchar2(8000);

  v_highdate    date;

  v_newname     varchar2(30);

Begin

  --DBMS_OUTPUT.ENABLE(1000000);

  for r1 in (

    select partition_name

    from all_tab_partitions

    where table_name=c_table_name and table_owner=c_table_owner

      and partition_name like 'SYS_P%'
     -- and partition_name like 'MOBILE_INOCS_CBP_REC_16%'

  ) LOOP

    select high_value into v_highvalue from all_tab_partitions

    where table_name=c_table_name and table_owner=c_table_owner

      and partition_name=r1.partition_name;

    execute immediate 'select '||v_highvalue||' from dual' into v_highdate;

    v_newname:='MOBILE_INOCS_CBP_REC_'||to_char(v_highdate-1,'DDD'); -- or another notation

    --DBMS_OUTPUT.PUT_LINE(v_newname);

    execute immediate 'Alter table '||c_table_owner||'.'||c_table_name

        ||' rename partition '||r1.partition_name||' to '||v_newname;

  end LOOP;

End;

/



Whenever I execute it, it gives me error message
SQL> @ren_part_recocs.sql
Declare
*
ERROR at line 1:
ORA-14082: new partition name must differ from that of any other partition of
the object
ORA-06512: at line 42

The partitions are created daily. Why does it display error message?

Regards,

Roshan