Create tablespace concern!!

[NDT]

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

[julian]

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.

[NDT]

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

[mageri]

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!

[NDT]

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]