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

Thread: Fragmented Tablespace

  1. #1
    Join Date
    Aug 2002
    Posts
    176

    Fragmented Tablespace

    We are working with a database of size 46GB on Windows NT.
    Our HDD is 80GB wiht 2gb Ram.

    We have a Tablespace of 37 GB. It contains a Total free space is 2GB, but The maxmimum freespace is only 9mb. There are 1321 fragmentations.. Tables with the next extent of even 10mb cannot extend and shows errors while import happens. The Tablespace is Dictionary managed. The Tablespace Contains 1417 objects of two schema.

    How can we defragment the Tablespace and make use of the 2GB. Will converting the Tablespace to LMT solve the problem. IF so how long(time) will it take to convert a 37gb Tablespace to LMT. If any other options are possible please provide it. All the objects are self contained in the same tablespace for both schemas.

    Import And Export cannot be done on that box because of space constraint.

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    converting to LMT will not solve this as it will still be fragmented.

    Have you tried coalescing the free extents.

    You could rebuild all your objects with the same initial and next extent and then you wont get so fragmented.

    better still create a new lmt with uniform extents and move your objects acoss

  3. #3
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    Originally posted by davey23uk
    converting to LMT will not solve this as it will still be fragmented.

    Have you tried coalescing the free extents.

    You could rebuild all your objects with the same initial and next extent and then you wont get so fragmented.

    better still create a new lmt with uniform extents and move your objects acoss
    Thats a good idea, also you can separate your objects to 2 or 3 size categories (small/middle/big) and move them to those tablespaces (with localy manages uniform size).

  4. #4
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    You can use: alter table xxx MOVE to move each table (1 at a time)
    to another tablespace ... Coalesce the free extents in the tablespace.
    and move back ... you need to map the blocks in the tablespace in
    question to find out what objects need to be moved and removed...
    Note: this will invalidate indexes associated with the tables that are moved... You will have to rebuild the indexes.

    HTH
    Gregg

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    And while your blowing off steam and drinking a cup of coffee, read "how to stop defragmenting and start living" the defragmentation-bible written by Oracle.

    Link :http://www.alise.lv/Alise/technolog....f?OpenDocument

    (it is placed on metalink, but I don't know if you have a password)

    HTH,
    Erik
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Well, I read it and other than having some "good to know info" it's pretty much out dated. If your company is on the bleeding edge of tech. Fragmentation is pretty much a thing of the past. Unless of course you have some goof ball create the tablespaces using dictionary managed. yes, you can still do it in 9i
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by OracleDoc
    . . . it's pretty much out dated.
    Well, that's the trouble with databases - they're designed to store data. So any database that does it job tends to get out of date. (I guess I'm "out of date" too.) There's a poll round here somewhere which suggested that a very significant minority are still struggling with vsn7.

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