Forum Stats

  • 3,770,163 Users
  • 2,253,079 Discussions
  • 7,875,352 Comments

Discussions

Partitioned index parallel rebuild - fails with ORA-12801 and ORA-01652

936451
936451 Member Posts: 4
edited May 23, 2013 10:30AM in General Database Discussions
Hello forum, I have a bit of a problem...

We have a scheduled move of tables from one tbsp to another once a week(when the DB is not used that much). With this move we have to rebuild all indexes to a new indx tbsp.
For every index the indx tablespace with the most free space is chosen(we have 4 tbspaces specially for indxes). It so happens that during execution of this migration at one point the tbsp with least free space is chosen and the rebuild fails with:
ORA-12801: error signaled in parallel query server P010, instance instance1:PROD1
ORA-01652: unable to extend temp segment by 64 in tablespace IDX01

This ocurs to one index, then the migration of the other tables proceeds and their indexes are created in the other tbspsces till again the job choses IDX01 and fails. And so on and so on.

Can someone explain to me is it possible that the other tbspaces are filled with temp segments during index rebuild(due to parallel rebuild), and when we query dba_free_space for the tbsp with moast free space IDX01 is returned? And when the creation of those indxes is finished free space is released and the new ones can use it?

If you think another reason may be causing this ... please share?

Is there a way to forecast the space needed for index rebuild parallel?

(As some of you may say "add a new datafile to IDX01" - I can not do that... maybe I can add new tbsp or add datafiles to other tbspaces... just not this one. Restrictions on a higher lvl.)

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
PL/SQL Release 11.2.0.3.0
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0
NLSRTL Version 11.2.0.3.0

Answers

  • rahulras
    rahulras Member Posts: 876
    I don't think you are running out of temp space.

    My guess is, there is a time lag between you checking the free space and you actually using that free space. In that time between checking and using the space, some other process/session is using (i.e. is allocated) the free space.
    Something like, 10 processes checked for tablespace (with most free space) at same time, they all found same tbs to use (coz at that point that is the tbs with most free space). Then all 10 processes started building indexes in there, 9 processes took all the space and 10th fell short before completing the rebuild.
  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    ORA-01652: unable to extend temp segment by 64 in tablespace IDX01
    When you rebuild an index it is rebuilt as a temporary segment in the relevant tablespace.
    When the rebuild is finished the temporary segment becomes permanent and the original segment can be dropped.

    So, in a worst case scenario, if you were rebuilding all your indexes concurrently, you can see how you might need a significant quantity of free space.
    We have a scheduled move of tables from one tbsp to another once a week
    Why?
  • 936451
    936451 Member Posts: 4
    Dom Brooks wrote:
    ORA-01652: unable to extend temp segment by 64 in tablespace IDX01
    When you rebuild an index it is rebuilt as a temporary segment in the relevant tablespace.
    When the rebuild is finished the temporary segment becomes permanent and the original segment can be dropped.

    So, in a worst case scenario, if you were rebuilding all your indexes concurrently, you can see how you might need a significant quantity of free space.
    Only two objects are rebuilt in the same time but as they are huge tables with huge indexes ... this may also take up the space I think..
    Dom Brooks wrote:
    We have a scheduled move of tables from one tbsp to another once a week
    Why?
    For example we have a subpartitioned table with ~ 12 000 000 000 rows that is about 400 GB in size. Ofc we move only certain partitions each week (from tbsp with faster disks that make one tbsp to tbsp with slower disks where we keep historised data). There is one index on that table but you can imagine it is also huge and also subpartitioned(arround the same size as the table) . We rebuild indexes as we execute:

    alter index table_index01_subpartition rebuild parallel;

    We have 24 cpu... so the load should not be a problem for the parallel degree... but as each subpartition is not that big (arround 40 mb) it should not take up the space in our biggest tbsp which has arround 40 GB free. Even with 10 subpartitions for each index to be moved on one migration... I don't know how this is filled so fast.

    We ran the scripts again and now it all passed without any errors. Still wondering why it failed the previous time as there was nothing going on in the database. Only this job running.

    Any suggestions what I should monitor as I would try to add more logging to the job scripts?
    Except the space usage on the tablespaces?
This discussion has been closed.