unexpected ORA-1652: alter index rebuild partition tablespace

From: Marc Blum <marc_at_marcblum.de>
Date: Mon, 23 Sep 2002 18:10:10 +0200
Message-ID: <a4euoucd1lbt2i32oeencsbluo17p31bag@4ax.com>

Ora EE 8.1.7.3 on NT4

Dear gurus,

situation:
a large table partitioned by some timestamp, each partition holding data for a month; three local indexes on that table

requierement:
make a specified month read only, back it up once (and forget about it)

solution:

1) analyze, how much space the partitions-to-be-moved consume
2) create a new tablespace with adequate space (aka datafiles)
3) first move the table partition, then move the index partitions into that

tablespace
4) make the ts read only, back up the controlfile, back it the ts (and forget about it)

problem:
when rebuilding/moving the last index partition, I get a ORA-1652

observation:
oracle allocates a temporary segment in the destination ts, this forces the crash

questions:
Why doesn't oracle allocate the temporary segment in the temporary tablespace? Why does it use the destination ts?
What can I do to keep the datafiles in the newly created ts as small as possible?
How can I estimate the needed new space more correctly/more robust? What can I do to keep the datafiles in the newly created ts as small as possible?

Many thanx in advance!!

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon Sep 23 2002 - 11:10:10 CDT