This content has been marked as final. Show 5 replies
how to drop the datafiles.With MUCH difficulty!
1) CREATE TABLESPACE NEW_TS
2) move all objects from existing tablespace into NEW_TS
3) drop old tablespace & files.
Moved from SQL and PL/SQL to Forum Home » Database » Database - General
#drop a datafile
ALTER DATABASE DATAFILE '/u02/oradata/dhktst/users01.dbf' OFFLINE DROP;
ALTER TABLESPACE users DROP DATAFILE '/u02/oradata/dhktst/users01.dbf';
There is no way to drop the datafiles once they are added to the tablespace. The command given by Asif , offline drop won't actually drop the datafile but would be an indication only to Oracle that you would be dropping the entire tablespace after issuing this command. In 11g, there is a command to drop the datafile but only if it's completely empty.1 person found this helpful