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