DATA TABLESPACE VERSUS INDEX TABLESPACE

If I have two seperate tablespaces DATA versus INDEX tablespace. There are some indexes using the DATA tablespace so I reuild those indexes to INDEX tablespaces.
One problem I've noticed though, is if the indexes get created originally in the DATA tablespace, even if you rebuild the index in another tablespace, you cannot reclaim that space easily. Anyone have any ideas on that issue?

Coalesce DATA tablespace.

thanks for your reply, I did coalesce DATA tablespace but It didn't release any space on the DATA tablespace.

Another questions I have is if the INDEX using space in the DATA tablespace, when we rebuild the INDEX, will it allocate the INDEX tablespace and release DATA tablespace ??? Or whatever in DATA tablespace will be in there and it will storage whatever in the future in the INDEX tablespace. Can someone give me some explanation on this .


Thanks

No, each segment can only reside in one tablespace. I.e., an index can be in the data ts or the index ts, but not partially in both at the same time.

If you:

rebuild index my.index
storage (initial xx next xx)
tablespace index_ts;

then, it will be completely removed from the data ts and it will completely reside in the index ts. All storage it formerly used in the data ts will be freed. Then, you need to alter the data ts to coalesce free space.

Thanks for your reply,

Coalesce option only eliminated fragmentation not to release DATA tablespace.

Pleas let me know if I am wrong.

That is correct, coalesce takes any adjacent free extents and makes them into single contiguous extents. The previously used extents first had to be freed by a drop or rebuild operation.

Ratcheer,

so could you please tell me how to release to space from DATA tablespace if you rebuild the INDEX using INDEX tablespace ???

From what you saying, when you rebuild the INDEX it will allocate the INDEX tablespace and deallocate DATA tablespace ???

Please give me more hint

thanks

I thought when you "DROP" an object it's space is released.
MH

MK,

I thought we only rebuild the INDEX not to drop !!!


Let me know what you think

If I understand the process correctly, the coalesce combines any "freed" up extents into as many contiguous extents as it can. When the next extent is needed, oracle will scan the table looking for an extent of the size it needs. If your newly recored extents are smaller than your "next" size in the storage options, then those extents will not be used.

From my readings, the only way to truely recover that space is to 1) kick everyone off the system. 2) export the data in the DATA tablespace, telling it to compress the extents 3) Truncate all the objects, 4) reimport the data. You have to tell the import to ignore any creation errors since your objects exist. On import, it will start at the beginning of the tablespace and work its way forward. This is the method for an Oracle7 system.

I seem to recall that in 8I, you can do this on a tablespace level.

Oracle does not really have a good tool for "defragging" a tablespace.

Please, everyone jump in and correct me if I am wrong. No pride in authorship and always willing to learn a new trick.

Quote:

---

Oracle does not really have a good tool for "defragging" a tablespace.

---

How about EXP and IMP :-) About indexes: alter index index_name rebuild.

Do you mean defragmenting a (permanent) tablespace? If the tablespace does not have a lot of extents, you should not worry that much. If it has, then worry, if they are not sized appropriately. If you want to "get back" chunks of a non-contiguous freespace, then only way is to export the objects and re-create the tablespace.

If you issue 'alter tablespace coalesce' command, you will only coalesce adjacent chunks of freespace.

Quote:

---

Originally posted by mike73
MK,

I thought we only rebuild the INDEX not to drop !!!


Let me know what you think

---

Mike,

Either rebuild or drop/create into another tablespace will free the space in your data tablespace.