This discussion is archived
2 Replies Latest reply: Feb 15, 2013 7:03 AM by onedbguru RSS

error while renaming the partition names

987518 Newbie
Currently Being Moderated
Why can't I rename or drop the older partitions when my table is live(Insert statements will keep on firing on that). I get the following error when I try to do that.

resource busy and acquire with NOWAIT specified or timeout expired
Is it a limitation of oracle or there is any way I can come out of it?
  • 1. Re: error while renaming the partition names
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    so your previous post has not been answered yet. With this answer you should close both.

    The reason for this is quite simple and documented in the manual: ALTER TABLE
    TABLE LOCK Oracle Database permits DDL operations on a table only if the table can be locked during the operation. Such table locks are not required during DML operations.
    The lock is done with NOWAIT option and this causes the error.

    Starting from Oracle 11g you can specify a wait timeout by issuing the following command:
    ALTER SESSION SET ddl_lock_timeout=60;
    The command above will wait 60 seconds trying to lock the table.

    I hope this clarify.

    Regards.
    Al
  • 2. Re: error while renaming the partition names
    onedbguru Pro
    Currently Being Moderated
    Just an obsevation, In 11gR2, I have become a very big fan of interval partitions - where the partitions are named by the system. I am also a fan of letting Oracle deal with partitions without having to try and "influence" it. It seems to d a very good job of doing partition pruning whatever the partition name.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points