DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Large Table

  1. #1
    Join Date
    May 2004
    Posts
    3

    Thumbs up Large Table

    Due to the space constraint on our Database server, we decided to move the old data i.e more than 1 yr from the production table to the similar table under different non-production table space. This table has more than 200 million records(450 GB index space, 330 GB data table space).
    My problem is …
    · How the space can be released from index table space and data table
    space after deleting the records
    · I can’t imagine to use coalesce due to the size of database
    · Any faster way of moving the records from one table to another
    Please advise,
    Nag

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134
    Well, to free up the index tablespace, create a new TBSP, re-build the indexes into this new TBSP, then drop the old one. This way you can re-size the new one accordingly and drop the old, bigger tbsp.

    I'm not sure about the data tbsp, you may have to create a new one, do an exp/imp, and drop the old one.
    I remember when this place was cool.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What's your version? 9i+ gives you data segment compression, which would help you, i think.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2004
    Posts
    3
    Thanks for the insight!!

    My main goal is to recoup the space from deleting the records.

    1)Copy the records older than 1 yr from the active production tablespace to non-production tablespace
    2) Delete the same records from the production tablespace once records are successfully copied.
    3) Looking for the fastest method to copy and delete the records
    4) Also want to recoup the space by deleting the records

    Please let me know, if you have any thoughts!!

  5. #5
    Join Date
    Oct 2000
    Posts
    211
    Hi,
    Use the 'alter table move;' without specifiying the tablespace name. This will reclaim the space and reset the HWM. Similar thing you can follow for the index also. Only thing in index will be the 'move' will invalidate the indexes and so after the move, you need to rebuild them.

    HTH

    Thanks
    Manjunath
  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by nagkaraka
    3) Looking for the fastest method to copy and delete the records
    Well COPY command with appropriate array size will be good ( ofcoure with copy commit set )..

    and for delete, if you need to delete more than 60% of rows then better retain the records in temp table..truncate main table and insert back.. (PS by truncate you will claim back the unused space that will be left if you used delete)

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    If you're on 10g, this might interest you.

    http://asktom.oracle.com/pls/ask/f?p...7312316112393,

  8. 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