unexpected ORA-1652: alter index rebuild partition tablespace 
Author Message
 unexpected ORA-1652: alter index rebuild partition tablespace

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

http://www.***.com/



Sat, 12 Mar 2005 00:10:10 GMT
 unexpected ORA-1652: alter index rebuild partition tablespace



Quote:
>questions:
>Why doesn't oracle allocate the temporary segment in the temporary tablespace?

That temporay segment could be your new index under creation.....

Quote:
>Why does it use the destination ts?

See above.

Quote:
>What can I do to keep the datafiles in the newly created ts as small as
>possible?

Create them with a minimal initial size and set AUTOEXTEND ON. During
allocation of extents the datafiles will extend as necessary.

Quote:
>How can I estimate the needed new space more correctly/more robust?

The segments you are moving, will *at most* demand same space in the
new tablespace as in the old one, unless you

a) Use dictionary managed tablespace and i.e specify pctincrease > 0
b) Use  LMT with an *enormous* uniform extent size

Quote:
>What can I do to keep the datafiles in the newly created ts as small as
>possible?

>Many thanx in advance!!

>Marc Blum

>http://www.marcblum.de

- Kenneth Koenraadt


Sat, 12 Mar 2005 02:39:59 GMT
 unexpected ORA-1652: alter index rebuild partition tablespace

Quote:

>>How can I estimate the needed new space more correctly/more robust?
>The segments you are moving, will *at most* demand same space in the
>new tablespace as in the old one, unless you

>a) Use dictionary managed tablespace and i.e specify pctincrease > 0
>b) Use  LMT with an *enormous* uniform extent size

yep, it was b)

thanx for the hint

Marc Blum

http://www.marcblum.de



Sun, 13 Mar 2005 01:01:07 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. ORA-1652: unable to extend temp segment by 7680 in tablespace

2. ORA-1652: unable to extend temp segment by 8192 in tablespace

3. Question re: ora-1652

4. ora-1652 Help Please

5. Odd ora-1652

6. ORA 1652

7. ORA-1652

8. ora-1652 - old subject, new twist?

9. ORA-1652: unable to extend temp segment

10. Ora 1652 during loading data via sql*loader even if i have enough space in Temp,data, and index tablespace

11. rebuild tablespace HELP ORA 01652


 
Powered by phpBB® Forum Software