Version
All versions
Explanation
The following error message is output when ALTER TABLESPACE ... DROP DATAFILE is executed:
Cause
The following error description can be viewed with the AltiErr utility:
$ altierr 0x0109D
0x11036 ( 69686) smERR_ABORT_CannotRemoveDataFileNode The data file is in use.
# *Cause: The data file is in use.
# *Action: Please remove an unnecessary data file.
Even if the user executed an INSERT statement and followed it with a DELETE statement so that all the datafiles can only have free pages, a previously used datafile cannot be dropped.
This error occurs when an attempt is made to drop a datafile that has previously been used.
Only datafiles that have never been used can be dropped.
Action
It is impossible to drop a datafile that was used even once.
To drop a datafile, the tablespace must be recreated as below:
1. Export the data and object scripts that are created in the tablespace.
2. Drop and create the tablespace.
3. Import the data and create the object.
Reference
N/A