It is CRUCIAL to understand that once added, a file is part of the database until you DROP THE TABLESPACE it is associated with !!!
See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:515222144417 <code>
I don't understand why you want to set the tablespace to read only -- looks like you are trying to transport the tablespace but that won't work since you DON'T have all of the files to begin with.
Step 1: make a full cold, consistent backup right now so you can at least get back to this point if need be.
Now, if we've caught this soon enough or you are running in archivelog mode (as you should be for a real database) and have the archives, we may be able to recover gracefully. Here is an example:
ops$tkyte@ORA817.US.ORACLE.COM> alter tablespace tmp_test add datafile '/tmp/bogus.dbf' size 1m;
Tablespace altered.
ops$tkyte@ORA817.US.ORACLE.COM> alter database datafile '/tmp/bogus.dbf' offline drop;
Database altered.
ops$tkyte@ORA817.US.ORACLE.COM> !rm -f /tmp/bogus.dbf
ops$tkyte@ORA817.US.ORACLE.COM> !ls /tmp/bogus.dbf
/tmp/bogus.dbf not found
ops$tkyte@ORA817.US.ORACLE.COM> alter tablespace tmp_test read only;
alter tablespace tmp_test read only
*
ERROR at line 1:
ORA-01230: cannot make read only - file 26 is offline
ORA-01110: data file 26: '/tmp/bogus.dbf'
So, here I am in the same situation. What I can do, since I have no backup of that datafile, is to create an empty one in its place:
ops$tkyte@ORA817.US.ORACLE.COM> alter database create datafile '/tmp/bogus.dbf';
Database altered.
ops$tkyte@ORA817.US.ORACLE.COM> alter tablespace tmp_test read only;
alter tablespace tmp_test read only
*
ERROR at line 1:
ORA-01230: cannot make read only - file 26 is offline
ORA-01110: data file 26: '/tmp/bogus.dbf'
ops$tkyte@ORA817.US.ORACLE.COM> alter database datafile '/tmp/bogus.dbf' online;
alter database datafile '/tmp/bogus.dbf' online
*
ERROR at line 1:
ORA-01113: file 26 needs media recovery
ORA-01110: data file 26: '/tmp/bogus.dbf'
So, we are not done yet -- it needs to be made consistent (i know, we didn't put anything into it but it STILL needs to be consistent. The database doesn't know nothing was in there ever).
In this example I was on a test database with noarchive log mode but I did this in a small enough window so that all of the needed logs were still available in the online redo log. You would simply:
ops$tkyte@ORA817.US.ORACLE.COM> alter database recover datafile '/tmp/bogus.dbf';
Database altered.
ops$tkyte@ORA817.US.ORACLE.COM> alter database datafile '/tmp/bogus.dbf' online;
Database altered.
and now you are back to where you started. You would NOT transport this tablespace at this point, you WOULD simply resize the file to be some small size and live with the fact that it is there.
2) this I don't understand unless you are talking about transporting in which case, as stated above, you don't want to do this. You need ALL of the files to transport. If you are missing one you cannot transport it.