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

Thread: defragment tablespace

  1. #1
    Join Date
    Dec 2002
    Posts
    89

    defragment tablespace

    Hello,
    i have a production database 50G.It seeems there is a lot of def. indexes.When i export and import on development server its 35G.
    1)How do i implement in production to reduce size .I should take full backup then shrink tablespace then import!!!!.
    2)We have exact same two development boxes.I am able to imp in one of
    box and on other it looks like freeze after importing 10 tables ..any
    clue


    thanks,

  2. #2
    Join Date
    Dec 2002
    Posts
    89
    HELP ME....

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    well if you are on 8i ...then no need to do import export..just create a new tablespace locally managed and then do

    1)alter table move new_tablespace
    2)rebuild all the indexes on that table in nologging mode thats it..

    You will reduce fragmentattion automagically..:-)


    i dont have a clue about your second problem..

    regards
    Hrishy

  4. #4
    Join Date
    Apr 2001
    Posts
    108
    1) You seem to indicate that the majority of "lost" space in your production database comes from indexes? If you have alot of dead space in your indexes from massive inserts/deletes, you could just rebuild the indexes rather than perform the exp/imp routine. That will clear up space within a database. You didn't specify what version of Oracle you are using. If you are running 8i or better you could also rebuild/move tables to other tablespaces to reclaim space lost due to honeycomb fragmentation. Remember if you to this to use uniform extent sizes. Just having alot of extents does not indicate fragmentation.

    2) As to this problem, are there any errors listed when the import stops?
    Eric Hanson

    There are 10 types of people in the world:

    Those who understand binary and those who don't!

  5. #5
    Join Date
    Dec 2002
    Posts
    89
    NO ERRORS .IT JUST STUCK THERE

  6. #6
    Join Date
    Dec 2002
    Posts
    89
    AND ALSO REBUILDING INDEXES WILL FREE UP SPACE BUT NOT SHRINK DATAFILE

  7. #7
    Join Date
    Dec 2002
    Posts
    89
    what will be the best way...
    let say i have 10g tablespace after rebuilding indexes it uses 2gig.
    can i shrink that tablespace to 5g....

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by kpate
    what will be the best way...
    let say i have 10g tablespace after rebuilding indexes it uses 2gig.
    can i shrink that tablespace to 5g....
    Probably, it depends on where the the indexes physically end up in the TS. Usually they will be rebuilt towards the "bottom" end so this will work - it did for me. (I'm sure someone out there is now busy constructing a counter-example!).

    If there are tables in the same TS, this will not work if you have one physically beyond the 5Gb mark.

    (Note that you can use "alter index rebuild" to move indexes to a different TS, if that helps.)
    Last edited by DaPi; 02-21-2003 at 12:02 PM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Mar 2002
    Posts
    534
    As Dapi allready said, I would rebuild your index on a new tablespace so that afterwards you can drop your old 10GB tbs.

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by kpate
    NO ERRORS .IT JUST STUCK THERE
    Are you using the COMMIT=Y for the IMP? If in 9i and using RESUMABLE, it could be waiting for an extension of the datafile.
    OCP 8i, 9i DBA
    Brisbane Australia

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