Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: