Some operating systems keep pointers to files, even if the file has been physically deleted. That might be causing your problem. You didn't specify your operating system, so it is hard to be sure. Another possibility is that the file you removed was not part of the database. Try this test case to be sure:
- CREATE TABLESPACE test_ts DATAFILE '/directory/test_ts01.dbf';
- CREATE TABLE test_table TABLESPACE test_ts AS SELECT * FROM all_objects;
- SELECT * FROM test_table; (to verify table is good)
- SELECT file_name FROM dba_data_files WHERE tablespace_name='TEST_TS';
- Use an OS command to delete the file from step #4.
- CREATE TABLE new_test_table TABLESPACE test_ts AS SELECT * FROM test_table;
You should receive an error from that last step.
This was first published in October 2004