Shrinking Index Tablespaces

[THoey]

I had created one very large index tablespace to store my handful of indexes in. Now, I have broken them out into three separate tablespaces and have recreated the indexes using them. I want to shrink that initial tablespace, but I get the following error:

ORA-03297 file contains num blocks of data beyond requested RESIZE value
Cause: Some portion of the file in the region to be trimmed is currently in use by a database segment. 

Action: Drop or move segments containing extents in this region prior to resizing the file, or choose a resize value such that only free space is in the trimmed region.

The file was set to 3 gig, only has 800 meg of index in it, and I want to shrink it to 1 gig. I just dropped and recreated these indexes. Doesn't it create them starting from the beginning of the space? I can't imagine that it is creating the index in the middle of a 3 gig file.

Any suggestions?
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

The simplest approach would be to drop the indexes, do the resize, and only then recreate the indexes. That should enable you to do the resize without getting an error.

It sounds to me as if you are having problems with fragmentation. That would explain why 800M of indexes may not actually fit into a 1G tablespace.

 

[sem]

If you're brave enough you may try to execute ALTER TABLESPACE...COALESCE to defragmentate your tablespace (backup it, if you're not SO brave ), but the method provided by Karluk is more durable.

 

[THoey]

Thanks guys. I will give Karluk's suggestion a try. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Guest_imported]

Hi
To solve this problem do this if you do not want to drop the index.
Alter index <index_name> rebuild
tablespace <Another tablespace>
/
Do it for all the indexes and make sure nothing is there in the tablespace by following query.
select segment_name from all_segments
where tablespace_name = 'Your tablespace'
/
Then drop the tablespace and recreate it.
Or resize it
Vinayak