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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top