Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Problems from removing a non-system data file when database was running

I removed a non-system data file when the database was up and running. Then I also was able to create tables to that tablespace that contain only the data file that I removed. After this I did "alter system switch logfile" 10-15 times frequently, it has to check the data file and write the contents to the data file. It is switching without any error. My database is in archivelogmode, the data file is showing in dba_data_files available, and there are no files in v$recover_files. It is online in v$datafile. It is a locally managed tablespace.

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:

  1. CREATE TABLESPACE test_ts DATAFILE '/directory/test_ts01.dbf';
  2. CREATE TABLE test_table TABLESPACE test_ts AS SELECT * FROM all_objects;
  3. SELECT * FROM test_table; (to verify table is good)
  4. SELECT file_name FROM dba_data_files WHERE tablespace_name='TEST_TS';
  5. Use an OS command to delete the file from step #4.
  6. CREATE TABLE new_test_table TABLESPACE test_ts AS SELECT * FROM test_table;

You should receive an error from that last step.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.