how to delete n-1 datafiles from tablepsace

[thanigai]

Hi all,
I have tablespace with 5 datafiles but i donot want 4 of them(each 1500M).
How shall I proceed.Advice...
Database ..v.8.1.6 on sunOS
I have the following in my mind....
Method1.take the datafiles offline....and drop???????
method2:resize the datafile less than 5DBlocks..
Method3:resize the files to 2m and forget it..let it use those files too..
Thanks in advance.
Thanigai.

[roobaron]

Probably the easiest method would be to drop the tablespace and recreated after moving off any data which resides in the tablespace.

Steps:
1) Make space for existing objects within tablespace (to be dropped) in another tablespace. This might involve using the resize command on existing datafiles if disk space is an issue.
2) Use either CTAS/rename to create temp tables in another tablespace, you could use copy command as well.
3) drop empty tablespace
4) delete OS datafiles
5) recreate tablespace with appropriate sizing
6) Move or recopy tables back into new tablespace.

You will be surprised how fast copy or CTAS commands are. Normally much faster than the equivalent exp/imp methods

Have Fun

[marist89]

I would use method #3 if the data files were really empty.

That being said, I would create a new tablespace with the datafiles you want and then move/rebuild the objects to the new tablespace.

[thanigai]

Thanks for the info folks...
I cannot do the drop tablespace because (unfortunately)
two projects are using the same schema.../tablespaces..
Some how mistake took place...Now I have to set right.
Because one is already in production another one is coming to production.
So I stop the second user and created the new schema/tablepsace and asked him to use that.
But the first fellow having 4gb where he needs only 20M.and we are in .24/7....
So have to resize/delete those files..Thats why I have to delete files without dropping tablepsaces.
Advice.
Thanks.
Thanigai.