I am trying to rename the system-generated partition names (generated during interval partitioning). I try to do this while my table is live(data read and write is happening). I need two scripts.
I want a script which will rename all the interval partitions, but not for today. As I get the following error when I try to rename the partition which is still active(The system does not allow me to rename the active partition). The following query renames all the partitions(including today's which is active)
declare hv varchar2(9); begin for x in (select partition_name, high_value from user_tab_partitions where table_name = 'FOO' and partition_name not like 'PART_%') loop execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from d ual' into hv; dbms_output.put_line('alter table foo rename partition '||x.partition_name ||' to PART_'||hv); end loop; end;
I get the following error when I try to execute this. All the partition gets renames except for today's.
Error report: ORA-00054: resource busy and acquire with `NOWAIT` specified or timeout expired ORA-06512: at line 9 00054. 00000 - "resource busy and acquire with `NOWAIT` specified" *Cause: Resource interested is busy. *Action: Retry if necessary.
- I want another script which will rename yesterday's partition(this will be running on daily basis) so that it keeps on renaming recently generated partition every day. I could run the previous script, but that will take more time to execute which I don't want.