-
Hi all,
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:
SQL> create tablespace NDT
2 datafile 'F:\TEST\MYDATA.DBF';
Tablespace created.
The TS was created successfully. I thought Oracle would return an error because this file exists. Can anyone explain this case ??? Thanks for your help.
I'm using 8.17 EE for NT
-
Yes, Oracle will give you
ORA-01119: error in creating database file
'F:\test\mydata.dbf'
ORA-27038: skgfrcre: file exists
in case you do not use REUSE.
-
Julian, I only get that error when I included the SIZE command if I do not use the REUSE or the SIZE, the tablespace created w/o anyproblem...don't know why!!
-
The filename isn't the fully specified filename. You can have many files with the same name under different directories. The filename consist of 'drive:\path...\file'.
ex.
===
SQL> create tablespace users2 datafile 'C:\ORACLE\ORADATA\MAGDB\DATA\USERS201.dbf' size 1M;
Tablespace created.
SQL> create tablespace users3 datafile 'C:\ORACLE\ORADATA\MAGDB\DATA\USERS201.dbf' reuse;
ERROR at line 1:
ORA-01537: cannot add data file 'C:\ORACLE\ORADATA\MAGDB\DATA\USERS201.dbf' - file already part of d
BUT if you copy the file 'C:\ORACLE\ORADATA\MAGDB\DATA\USERS201.dbf' to
'C:\ORACLE\' then
SQL> create tablespace users3 datafile 'C:\ORACLE\USERS201.dbf' reuse;
Tablespace created.
Works fine!
-
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
'C:\ORACLE\' then
SQL> create tablespace users3 datafile 'C:\ORACLE\USERS201.dbf' reuse;
Tablespace created.
Works fine!
=================
Since you used the REUSE keyword, it will work for sure. Have you tried to run this command instead??
SQL> create tablespace users3 datafile 'C:\ORACLE\USERS201.dbf' ;
[Edited by NDT on 09-28-2001 at 01:58 AM]
