rename partition script error

[Roshan]

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

[ErmanArslansOracleBlog]

As the error suggest, your code tries to name a partition with a new name , but  that new name is already belongs to another partition.

use dbms_output to display the execute immediate statements that are produced by your code during runtine, and see the problematic statement.
After that, revise your code accordingly.

[Roshan]

Thanks. Can you please guide me at which position I should insert the dbms_output?

[ErmanArslansOracleBlog]

Before renaming, after renaming basically...