1 Reply Latest reply: Dec 18, 2012 7:39 AM by 758358 RSS

    Reorganizing or shrinking Oracle Queeus Online

      Dear Friends,

      I want to reorganizing a Oracle QUEUE table. When I tried to re-org this table I am getting many locking blocking issues as this queue is runnign 24/7 basis. and getting this error

      SQL> /
      ERROR at line 1:
      ORA-42008: error occurred while instantiating the redefinition
      ORA-12034: materialized view log on
      "ADMIN"."AQ$_CUSTOMQUEUE_H" younger than last refresh


      then I tried shrinking the space and getting this error

      ERROR at line 1:
      ORA-10636: ROW MOVEMENT is not enabled

      Question 1>> What would be the impact on performace if we would keep row movement enabled for the tables permanently.

      Question 2>> Instead of rebuilding the indexes post table shrink can we use cascade option with table shrink space itself.

      e.g. alter table admin.CUSTOMQUEUE shrink space cascade;

      what would be the difference if we use cascade option?