freeing up table

To remove data:  delete from table_test where some_date_column < add_months(sysdate,-2);
Þ

To free up allocated extents, look up coalesce.

To free up disk space from datafile size is more difficult.  How far do you want to go?

Apologies.  It's not coalesce.  It's deallocate unused.


http://www.dba-oracle.com/t_coalesce_deallocate_unused_space.htm

>> alter table table_test move tablespace tablespace_name;


I don't believe this frees up space by default.  I think you need to specify new storage parameters to actually reduce usage.

Hi,

Yes... it will free up the stoage space..

for eg.. consider i have a table with 5 million rows which occupies 30G of tablespace space. Now i have deleted 3 million rows in that table..  i need only 12G of tablespace inorder to store the remaining 2 millon rows.. so i will execute the command alter table ... move inorder to move the table to the new location in the same tablespace or i can move it to some different tablespace.. while moving it will take up only the used data blocks to the new location...  once it is completed the segment size will shows as only 12G..

Thanks..

if you are using 10g and above you could use

alter table XXX enable row movement ;
alter table shrink space compact;
then you can reorg all your indices.

if your using 9i and below then the other posts are good for you.

>>Yes... it will free up the stoage space..

OK, it should shrink it if possible but I believe down to a minimum of the initial extent size.  If the table has an initial extent of 20G, the 'new' table will not be 12G.

Thanks everyone.. one more question,
If I try the techniques above will i be able to revert it to the existing structure? the data is not important for me. I just want to make sure if something goes wrong, and  if I just take the backup of the table script will I be able to go back to the existing version.. again I am not worried about the data but the table structure because all the new data are loaded into this table weekly..
Thoughts?
Thanks

I believe move will invalidate any indexes on the table.  

any workaround?

From my first post: http:#a34903050, how far you you want to free up the space?

deallocate unused will release the space in the tablespace to be used by other objects.  You need to shrink the datafile to free up filesystem space.

What do you mean by 'free up'?

I am very new to this, so please excuse my jargon...
the database I am talking about is very small and its filling up fast. So the goal is we are moving it to a bigger DB (which we already did). but still we are using the old database as a staging table to load new data and move it to the new database incrementally. And we will be removing the data from the old database by just keeping 2 months old data.
Since we are piling up more data to the old database now without deleting the transformation takes long time.
Does this makes sense?

I ve heard ppl talk about purging. What exactly is purging process and its consequences in oracle perspective...

As for your table script question, you can use this in sql plus to extract the table structure:

select dbms_metadata.get_ddl('TABLE','<tablename>','<schema>') from dual;

Thanks everyone for your inputs...