1 Reply Latest reply: Apr 27, 2011 7:46 AM by AleC RSS

    DBMS_REDEFINITION.START_REDEF_TABLE ORA-12008 ORA-04030

    AleC
      RDBMS: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
      OS: Redhat 4.6

      I'm trying to migrate to a partitioned table using the DBMS_REDEFINITION.

      But I've an error:
      BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('m', 'ij', 'ij_tmp', NULL, DBMS_REDEFINITION.cons_use_pk, 'LIID, ID'); END;
      
      *
      ERROR at line 1:
      ORA-12008: error in materialized view refresh path
      ORA-04030: out of process memory when trying to allocate 262656 bytes
      (callheap,kllcqgf:kllsltba)
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 40
      ORA-06512: at "SYS.DBMS_REDEFINITION", line 1035
      ORA-06512: at line 1
      The table size is 3648Mb and the memory parameters are:

      - db_cache_size = 629145600
      - shared_pool_size = 734003200
      - pga_aggregate_target = 524288000
        • 1. Re: DBMS_REDEFINITION.START_REDEF_TABLE ORA-12008 ORA-04030
          AleC
          Probably, it's a OS resource problem: the vmstat output says there is a lot of paging activity.
           r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
           3  1 149264  23588   1068 6808412    3    0 19970 42500 3244 10185 15  3 72 11
           1  2 149260  25060    916 6779212    0    0 20519 49500 3365 10872 15  3 71 11
           1  1 149256  21948    776 6743732    0    0 18691 41952 3359 12623 14  3 70 12
           2  1 189076  20996    996 6740368    0 3829 18503 44338 3161 10137 15  5 69 12
           0  2 322256  23268   1844 6820916   35 12266 17653 53150 3358  9923 14  3 72 11
           2  2 326692  22172   1200 6808204  104 1692 19615 45048 3328 10400 14  3 72 11
           3  0 326492  23620   1096 6771884   70    0 20627 44648 3432 10617 15  3 72 10
           2  1 326356  25108   1056 6741512   42    0 23278 50368 3586 11414 15  3 71 11
           1  1 326248  24180    864 6703308  136    0 22004 50579 3405 11365 15  3 71 10
           1  1 326144  25076    700 6665744   40    1 21762 46633 3510 11604 15  4 71 11
           2  1 174232 732548    932 6642040 3438    0 10542 21220 2180  7409 13  2 73 11
           2  0 174228 725124   1388 6650164    0    0    42  2114 1287  4195 12  0 84  3