Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I am trying to rename the system genrated 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.

  1. I want a script which will rename all the interval partitions, but not todays. 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 excute 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.

2. 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 excute which I don't want.

share|improve this question
You can do what you're asking by adapting the drop script in my previous answer - it already does a date comparison on the partition boundary which is all you need. (As for two different scripts for "performance" - that's not needed. You can make your script only touch the partitions that need touching.) If you're having trouble doing that, please post what you have so far so we can help you fix it. – Mat Feb 1 at 8:11
I get the following error, while trying to rename all the partition(including the one which is active) 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. – Rajesh Feb 1 at 9:29
Yes, that's not really surprising, you're not supposed to do partition maintenance on "active" partitions. Doesn't change anything to my comment above. – Mat Feb 1 at 9:34
correct. So, lets assume we are running the script today, then it will select today's system generated partition as well(which will obviously be active) and will try to rename it. So, how do I change the script so that it does not touch active partition, but still renames the rest. – Rajesh Feb 1 at 9:44
As I said, you can adapt the drop script, it already has a date comparison on the partition boundaries. – Mat Feb 1 at 9:49

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.