Space required for MOVE op !

[cruser3]

We have a part. table prod. 48 Gb ( 7 partitions) in size extent alloc system with the least extent size: 65536 and the greatest: 67108864

Indexes: 3 locally part.

It had 65 M rows and we delete approx 2M rows and 450K rows are
inserted daily. The deletes were put in place to control growth and filling up of the filesystem.

How much space is required to do an ALTER TABLE MOVE PARTITION ? As in our UAT env it fails midway with an
ORA-01652: unable to extend temp segment by 4096 in tablespace
error.

TIA

[davey23uk]

twice the space

[cruser3]

Tx - I hear this everywhere i.e twice the space - is this documented anywhere ? Searched Meatalink but coul'nt find anthying...

[jmodic]

Not necessarily twice the space - in most cases you'll need the same or less free space that your table/partition is currently using, but in some cases it could even be more.

It depends on how sparce your blocks are currently filled and also whether you change your storage parameter for the rebuilded table/partition during the move operation.

And this "twice the space" is probably not documented anywhere for this exact reason - because there is not one exact answer to your question. It could be less, it could be more, it could be the same. But the whole thing is very well documented in Concepts manual - if you understand the storage structures of the oracle database (rows, blocks, extents, segments) you can allways predict how much additional space you'll need for MOVE operation for each particular case.

[cruser3]

TX -JM

Makes it a little clearer now...