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

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

Quote:

---

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

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

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

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

Quote:

---

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.