According to all the sources I've read, they all suggest to use the REUSE command in the Create Tablespace...DAtafile clause if the file exists. For my own learning purpose I took an existing DBF file (MYDATA.DBF) and placed it under the TEST directory to test this theory by omitting the REUSE command and guess what:
Hi mageri, thanks for helping me out, actually I tried to create the file from the same directory.This is what i did: I took of my existing tablespaces offline, and using o/s command to copy one of its datafiles to a new folder and then tried to create a brand new tablespace pointing to the new file I just copied, without using the REUSE or the SIZE command. According to OCP books, I should get an error because the file is already exist. However according to Oracle 8i Documentation, depending on the O/S, Oracle DOES allow over writing of an existing datafile WITHOUT using the REUSE command (scary eh).
By the way,the reason you got:
ERROR at line 1:
ORA-01537: cannot add data file 'C:\ORACLE\ORADATA\MAGDB\DATA\USERS201.dbf' - file already part of d
is because the datafile you tried to recreate using the REUSE command is now a part of the newly created tablespace in a running database (online). You should drop (or take it offline) the first Tablespace you created before you run the second CREATE TABLESPACE ...REUSE command. This is "in a way" equivalent to "Sharing Violation" error in Microsoft world.
For the second part:
BUT if you copy the file 'C:\ORACLE\ORADATA\MAGDB\DATA\USERS201.dbf' to