Problem in Resizing tablespace

[sandeepagarwal]

I got a tablespace of size 1.5 GB and I'm only using 16 MB. My goal is to reduce the size of this tablespace to 1 GB.
First I gave the following command:

alter tablespace <tablespace name> coalesce;

Then I issued the following command:

alter database datafile <filename with path>
resize 1000M;
I'm getting the following error:
ORA-03297: file contains used data beyond requested RESIZE value.

Can anybody help me in fixing this.

Thanks
Sandy.

 

[ThomVF]

How many data files are in the tablespace and what sizes ?

Look at DBA_FREE_SPACE_COALESCED for that tablespace to see the coalesce activity.
You can also look at DBA_FREE_SPACE to see how many adjacent free block groups exist that were not coalcesed.

 

[carp]

You may have to export all of the objects in the tablespace, drop them, resize the datafile(s), and then import the objects back in again.

 

[sandeepagarwal]

Thanks ThomVF for your suggestions.

Carp, I will go ahead with your idea. Tell me whether dropping all objects (after export ofcourse) affect the index tablespace?? Will importing back everything also creates all indexes which I created explicitly on non-unique columns.

Thanks
Sandeep.

 

[carp]

Assuming you export the indexes, yes.
If you are using 8i, you might also look at moving the tables rather than exporting/dropping them:
1. For each table in the tablespace issue the command
ALTER tablename MOVE TABLESPACE some_other_tablespace;
2. Resize your datafiles.
3. Move the tables back:
ALTER tablename MOVE TABLESPACE original_tablespace;

This will leave your indexes alone and keep your tables accessible.

 

[sandeepagarwal]

Carp, I think moving tables to different tablespace is a nice idea. (Ya, I'm using 8i)

I'll try that.
Thanks
Sandeep.