-
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
-
what command u executed for shrinking.
use
alter database datafile 'path\datafile' resize 500m
Santosh Jadhav
8i OCP DBA
-
I am using DBA Studio...
Originally posted by sjadhavdba
what command u executed for shrinking.
use
alter database datafile 'path\datafile' resize 500m
-
then resize it in the studio.../
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
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.
-
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
-
Yours datas don´t continuos, shrinking don´t resolve.
You need export data, drop tablespace, create new tablespace with new size and import.
Márcio de Souza Almeida
DBA Oracle / SQLServer / PostgreSQL
Rua Cupa, 139 Apto 85 A
Penha - São Paulo - SP
03640-000 - Brasil
http://www.directory.com.br
-
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))
-
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.
-
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))
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|