I have created a user xxx on tablespace_1 and temp tablespace tmp_1.
To load 50m test data I have extended the tablespace_1 with 5 datafile each of 2GB. now I have removed the testdata I need only 100MB of one datafile, my question how to remove the datafile without affecting the database.
There is no SQL statement that specifically drops a datafile. The only means of dropping a datafile is to drop the tablespace that contains the datafile. For example, if you want to remove a datafile from a tablespace, you could do the following:
1.Create a new tablespace
2.Move the data from the old tablespace to the new one
4.Drop the old tablespace
You use alter table <tablename> move tablespace <new tablespace>
BUT dont forget to rebuild all indexes associated with the tables as they will become invalid.
Write a sql query against dba_tables to write your 'alter table move' commands for all tables belonging to your old tablespace. And you can do a similar thing to rebuild all indexes where status<>'VALID'