Shrink tablespace

[kpate]

Hi,
We have a production database indexes and data are on seperate tablespace.After doing export import on dev. server ,looks like some
of the index tablespace are fragmented.So what would be the best way
to free up space and shrink datafile...
--Do full export.
-- Drop user
-- Shrink datafile (40g to 25g)
-- import

[abhaysk]

Rebuild Index Online....

[kpate]

what about shrinking datafile..

[jovery]

Rebuilding the indexes may not necessarily allow you to shink the tablespace, If the index requires contiguous blocks which are only availaible in the upper portion of the datafile.

[kpate]

Can anybody please help me understand what will happen if i have a
tablespace 20g after doing full export and droping user then resize that tablespace to 10g and then import.

[jovery]

You don't state your version so I will assume you are now using LMT's and that you have exported your data with compress=n. If that is the case and the objects will fit into the new 10Gb tablespace with thier original extent values the import will be fine.

However if you have opted for compress=y and have a lot of unused space in your objects then it's possible you import will fail due to lack of space as it will attempt to rebuild the objects based on thier size at export.

Regards

[ccastaneda]

Originally posted by kpate
We have a production database indexes and data are on seperate tablespace...

Ppl is assumming that you wanna make this change with the database online.

Originally posted by kpate
Can anybody please help me understand what will happen if i have a
tablespace 20g after doing full export and droping user then resize that tablespace to 10g and then import.

This process could be done online (except the drop user part of course..) but database performance will pay the price.. imp will fail because indexes that you are trying to import already exists... so u will have to drop them all before importing (An option??..)

Index rebuilt will get rid the index fragmentation problem, but not necesarilly allow you to shrink the datafile as jovery said.

If shrinking the datafile is your objective you could do this..
I assume that your index tablespace name is INDEX_OLD, and contains nothing but indexes.

1. Create a new tablespace NEW_INDEX with a new smaller datafile.
2. Rebuilt all your indexes moving them from the old index tablespace to the new index tablespace (ALTER INDEX xxx REBUILD TABLESPACE NEW_INDEX)
3. Drop old index tablespace (OLD_INDEX), delete old datafile.

Hope it helps..

[kpate]

thanks ccastaneda
i have to keep index tablespace name same as we are using third party application. Database down time is not problem during weekend.
so i am thinking shutdown database and export then drop user then
resize datafiles and then import.my concern is if i export as compress=n while importing it will not look for same block..

[SANJAY_G]

Creating new index tablespace and moving indexes there could have been the best option but as you said you can't change the tablespace name.

Another option can be..
generate index creation scripts.
Drop all the indexes.
drop the tablespace.
recreate the tablespace with smaller datafile.
create all the indexes.

You can improve the creation performance by using parallel and nologging options. I think this option will be definitely faster than full export/import and can save you from some hassles.

HTH

[kpate]

thanks,
There are some tablespace which has both tables and index also .Is it right if i take full exp then imp then resize datafile
..... can you resize datafile after import.if there is data on that extent it will give you an error right!!!.so you are not messing up any data will resizing.