DATA TABLESPACE VERSUS INDEX TABLESPACE
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: DATA TABLESPACE VERSUS INDEX TABLESPACE

  1. #1
    Join Date
    Aug 2001
    Posts
    391
    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?



  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Coalesce DATA tablespace.

  3. #3
    Join Date
    Aug 2001
    Posts
    391
    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


  4. #4
    Join Date
    Oct 2001
    Location
    Pelham, AL
    Posts
    40

    Arrow

    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.

  5. #5
    Join Date
    Aug 2001
    Posts
    391
    Thanks for your reply,

    Coalesce option only eliminated fragmentation not to release DATA tablespace.

    Pleas let me know if I am wrong.


  6. #6
    Join Date
    Oct 2001
    Location
    Pelham, AL
    Posts
    40

    Arrow

    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.

  7. #7
    Join Date
    Aug 2001
    Posts
    391
    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

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131

    Lightbulb

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

  9. #9
    Join Date
    Aug 2001
    Posts
    391
    MK,

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


    Let me know what you think

  10. #10
    Join Date
    Feb 2001
    Posts
    99
    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.

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