There is no simple method to delete the tablespace data file, the only way is to delete the entire defined table space, steps below (provided that the data on this data file is not required):
If the database is running in the non-archive mode:
.. 1. MOUNT Database - startup mount2 delete data files - alter database datafile xxx offline drop3 open (OPEN) Database - alter database open 4. see all objects belonging to the tablespace: select owner, segment_name, segment_type from dba_segments where tablespace_name = 'TBS_NAME'5. Export all objects of the table space ------ Use the eXP command to do 6. Delete table space - Drop tablespace TBS_NAME INCLUDING Contents7. Delete All physical data files of this table space delete the Physical DataFiles Belonging To the tableSpace8. Rebate the table space to import the DMP files exported in front.
If the database is running in archiving mode:
1. Mount Database - Startup Mount 2. Delete Data File - ALTER DATABASE DATAFILE XXX OFFLINE (NOTE: OFFLINE This data file, this data file is part of this database, just marked its status as OFFLINE in the control file.) 3. Remove the physical data file at the operating system level 4. Open (Open) database - ALTER DATABASE OPEN5. Behind the following: Export the object to the table space Remove the table space and import the object
If the database is running in archiving mode, and the data file has a backup:
1. Mount Database 2.Offline Data File: ALTER DATABASE DATAFILE XXX OFFLINE; 3. Copy the backup data file to the position of the original data file. 4. Put the backup data file to all archive logs to the archive directory .5. Recovery Data File: Recover Automatic DataFile XXX (To enter a full path name) 6. Then Online data file: Alter Database DataFile XXX Online; 7. Open (Open) Database: ALTER DATABASE OPEN; 8. Make a database of shutdown.
