2 Replies Latest reply: Feb 15, 2013 9:03 AM by onedbguru RSS

    error while renaming the partition names

      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

          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.

          • 2. Re: error while renaming the partition names
            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.