Shrinking Tablespaces...

[Sekhar]

Hi,

I have created a table which we need to test for performance impace in a tablespace with 2 GB. We have some more objects which are already existed in this tablespace. Our table have been occupied nearly 1.7 GB.

Now after our all experiments I droped that Table. and i got the 1.7 GB freeSpace in that tablespace. But I could not able to shrink that tablespace till to my requirement.

When i am trying to shrinking it .. it is giving oracle error that I cannt give the min space which i occupied. but actually now it is just 200 MB is occupied. I want to give 500 MB for this and use the remaining 1.5 GB for another purpose.

Please help me to come out from this problem.

Thankx and Regards,
Sekhar

[sjadhavdba]

what command u executed for shrinking.

use

alter database datafile 'path\datafile' resize 500m

[Sekhar]

I am using DBA Studio...

Originally posted by sjadhavdba
what command u executed for shrinking.

use

alter database datafile 'path\datafile' resize 500m

[Tarry]

then resize it in the studio.../

[Sekhar]

Its not allowing me to resize .. Its giving Problem that the size which i gave is not free to give .. but the space is available now which we used before.

[haferkamp]

Hi, you can't free space of a datafile (with 'alter database datafile '..' resize') which was already allocated to an object (like a table) - even if this object doesn't exist anymore (highwatermark principle). The only possibility for you to make the tablespace/the datafile smaler is to export all the objects inside this tablespace, drop & recreate the tablespace (with a smaler size) and import the objects again.

Greetings,

Kirsten

[marcio.68]

Yours datas don´t continuos, shrinking don´t resolve.
You need export data, drop tablespace, create new tablespace with new size and import.

[gab]

I had the same problem when archiving a partitioned table. If you still have objects in that tablespaces is not necessary to drop them, you can move them in a different tablespace, then resize the file then move them back. After I truncated the table, even though the size was zero, the segment was still there somewere and that's why I got the same error message like you. Same for the indexes.
I send you the code, maybe it helps

"alter table sfia.vehicle_history truncate partition " + Partition_Name(i) + " drop storage"
"alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_data"
"alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_data"
"alter database datafile 'e:\sfiadbs\dbs\sfia_history_data_" & Trim(Str(i)) & ".ora' resize 10k"
"alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))
"alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))

[Sekhar]

Yes, I did this export , import things earlier . . But through DB studio, it is allowing me to shrink the Datafile Sizes if it is never used with any object.

[Sekhar]

This looking helpful for me.. I do try it..

Thankx

Originally posted by gab
I had the same problem when archiving a partitioned table. If you still have objects in that tablespaces is not necessary to drop them, you can move them in a different tablespace, then resize the file then move them back. After I truncated the table, even though the size was zero, the segment was still there somewere and that's why I got the same error message like you. Same for the indexes.
I send you the code, maybe it helps

"alter table sfia.vehicle_history truncate partition " + Partition_Name(i) + " drop storage"
"alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_data"
"alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_data"
"alter database datafile 'e:\sfiadbs\dbs\sfia_history_data_" & Trim(Str(i)) & ".ora' resize 10k"
"alter table sfia.vehicle_history move partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))
"alter index sfia.veh_hist_idx rebuild partition " + Partition_Name(i) + " tablespace sfia_hist" & Trim(Str(i))