Create tablespace concern!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Create tablespace concern!!

  1. #1
    Join Date
    Aug 2001
    Posts
    147
    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

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.


  3. #3
    Join Date
    Aug 2001
    Posts
    147
    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!!

  4. #4
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70

    Smile

    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!

  5. #5
    Join Date
    Aug 2001
    Posts
    147
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width