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.

This was first published in October 2004

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.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: