Shrinking Tablespaces...

Printable View

03-21-2002, 10:09 AM
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
03-21-2002, 10:21 AM
sjadhavdba

what command u executed for shrinking.

use

alter database datafile 'path\datafile' resize 500m
03-21-2002, 10:26 AM
Sekhar

I am using DBA Studio...

Quote:

Originally posted by sjadhavdba
what command u executed for shrinking.

use

alter database datafile 'path\datafile' resize 500m
03-21-2002, 10:34 AM
Tarry

then resize it in the studio.../
03-21-2002, 10:36 AM
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.
03-21-2002, 10:43 AM
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
03-21-2002, 10:46 AM
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.
03-21-2002, 10:56 AM
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))
03-21-2002, 10:56 AM
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.
03-21-2002, 10:59 AM
Sekhar

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

Thankx

Quote:

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))