Space required for MOVE op !
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Space required for MOVE op !

  1. #1
    Join Date
    Jul 2003
    Posts
    323

    Space required for MOVE op !

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    twice the space

  3. #3
    Join Date
    Jul 2003
    Posts
    323
    Tx - I hear this everywhere i.e twice the space - is this documented anywhere ? Searched Meatalink but coul'nt find anthying...




  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jul 2003
    Posts
    323
    TX -JM

    Makes it a little clearer now...


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width