Re: Oracle asking for the dropped datafile....

As an interim, try adding the datafile back into the tablespace; the alter.... command does not delete the operating system file, so if you haven't deleted it manually or if you can recover it if you have, it should return your tablespace to a usable state--- Adding records while the datafile was active could have placed records in that datafile, even if the first datafile was not full - Oracle deals with the tablespace as a whole and will write to any available datafile and there is no way that I know of to control what datafile in a tablespace will be used to store a particular bit of data.

Hope it helps,
John Greco
Oracle DBA
Email to john.greco_at_dot.state.mn.us

Tansel Ozkan <tansel_at_openix.com> wrote:

>Hello all,
>
>Somebody in our group first added a new datafile to a tablespace
>thinking that there was more space needed to load new data. However,
>after realizing that that was not the case, he dropped the datafile
>using 'Alter database datafile 'filename' offline drop;'. However,
>before dropping this datafile, he attempted to load some data, and
>according to his account, he only loaded 26 records. Even after dropping
>the datafile, the v$datafile dictionary table shows the file in
>'recover' status, and when we try to load new data to the new tablespace
>after a certain point, probably when the first datafile is full, it is
>giving an error message asking for the dropped datafile. We tried
>truncating the table and dropping the table to be loaded so that all its
>storage parameters not used.
>Does oracle keep all the datafile names even after they are dropped?
>
>Any suggestions..
>
>Thanks..
>
>Tansel
Received on Mon Apr 13 1998 - 10:26:20 CDT