How to Reduce the size of tablespace?

[Reshmi Jacob]

Hello..........

My user tablespace which I used before for all transactions is now about 8 GB of my Harddisk. I have dropped users & objects but still the same size. I went to DBA studio and tried to decrease the size, but it is saying the size is too small to contain data. I am sure that this tablespace has not much data now. Does oracle have some way to COMPACT this.

I am a beginner hence, pls explain the solution....


Thanks & Regards
Reshmi

[saravanankm]

Hi Reshmi

Try this one

ALTER DATABASE DATAFILE 'name.dbf' RESIZE 10mb;

If you cannot resize the files, then create a new tablespaces, move all objects to that tablespace, drop the original tablespace origts.

create tablespace newts ... ;

-- you can get a list out of dba_segments and dba_objects
alter table t1 move tablespace newts;
alter table t2 move tablespace newts;
alter index i1 rebuild tablespace newts;
alter index i2 rebuild tablespace newts;
...

drop tablespace origts;
-- remove the datafiles

alter tablespace newts rename to tbs01;

With Regards
Captain

[SQLNAVIGATOR]

Hi Reshmi

Try this one

ALTER DATABASE DATAFILE 'name.dbf' RESIZE 10mb;

If you cannot resize the files, then create a new tablespaces, move all objects to that tablespace, drop the original tablespace origts.

create tablespace newts ... ;

-- you can get a list out of dba_segments and dba_objects
alter table t1 move tablespace newts;
alter table t2 move tablespace newts;
alter index i1 rebuild tablespace newts;
alter index i2 rebuild tablespace newts;
...

drop tablespace origts;
-- remove the datafiles

alter tablespace newts rename to tbs01;

With Regards
Captain

HI Some times export and import the same tablespace will also help to compress the extents and segments.You can compress individual segments also.

cheers
SN

[Kevin Sargeant]

SQLNAVIGATOR makes an excellent point but doesn't take it to full advantage. If you export (exp) all of the contents of your tablespace you are then free to either drop all contents or drop the tablespace entirely. Once you do this it is trivial to recreate the tablespace or resize it using the ALTER DATABASE DATAFILE. The conclusion is to move your data back in via import (imp).

The tablespace swapping exercise that saravanankm suggests is for an active multi-user environment when you can't assume that the data is static and allows for change will you work under the covers.