I have mistakenly added datafile to the tablespace... And was trying to drop it. I run the following:

alter database datafile 'datafile_name'  OFFLINE;

now when I run

alter database datafile 'datafile_name'  ONLINE;

it shows

ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: 'datafile_name'

also status of the file is RECOVER... What can I do? I cant restart database....


Found the answer:

SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';

alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';


But for production DB it needs a lot of time to recover... Can't I drop that file without recovering it?

One more question... Another datafile was available during adding that file.. is there any chance that newly created datafile was used for storing data? I think oracle uses datafile until it will not exceed its maximum size and then switching to another available file... is not it so?

I am asking this because I want to be sure that newly created file doesn't contain any necessary data...

  • 1
    Please ask the "one more question" in the body of your question in stead of in your answer (or possibly as a new question if you think that would work better)! – Jack Douglas Dec 30 '11 at 9:18

As soon as you add a datafile to an existing tablespace, a segment inside this tablespace that needs space may allocate an extent within the new datafile.

It will not wait until the other datafile(s) are full.

This is actually a feature (called extent striping) that was introduced to spread I/O across multiple devices, should datafiles reside on different drives. It is standard behavior since I don't know when - probably from the very start :-)

  • I thought like that and fortunately did not delete that datafile.... Thank you very much for useful information. – kupa Sep 17 '11 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.