Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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....

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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';

alter  TABLESPACE EXAMPLE  drop 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...

share|improve this answer
    
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
add comment

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 :-)

share|improve this answer
    
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
    
You're welcome :-) –  Uwe Hesse Sep 21 '11 at 9:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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