1

I have an error "ORA-01537" when I create a tablespace.

create tablespace "VOLUMT" datafile 'VOLUMT.dbf' size 250M autoextend on;

Then, it shows like following.

ERROR at line 1: ORA-01537: cannot add file 'VOLUMT.dbf' - file already part of database

I just change the tablespace name of VOLUMT to TEST01, and it works..

Tablespace created.

So, I get into the directory of db and searched the related tablespace..

${HOME}/oracle/db/oradata/ORAC12

However, I can't find any.. Coudl you help me to solve this problem? Also I need to use "VOLUMT" for tablespace creation.. not TEST01..

-- Additional --

select TABLESPACE_NAME from DBA_DATA_FILES;

TABLESPACE
----------
SYSTEM
SYSAUX
USERS
TEST01

I can see TEST01 Tablespace, but I can't find any datafiles in the directory. Also I can't find any VOLLMT tablespace in it..

Thank you.

  • Apparently you already have a file with that name. What is the output of select TABLESPACE_NAME, file_name from DBA_DATA_FILES;? – a_horse_with_no_name May 17 '18 at 6:01
  • Output is only TABLESPACE ---------- FILE_NAME -------------------------------------------------------------------------------- SYSTEM /home/oracle/db/oradata/ORAC12/ID/system01.dbf SYSAUX /home/oracle/db/oradata/ORAC12/ID/sysaux01.dbf USERS /home/oracle/db/oradata/ORAC12/ID/ID_user01.dbf – owcred601 May 17 '18 at 6:12
  • There is no such a "VOLUMT".. – owcred601 May 17 '18 at 6:12
  • @owcred601 Please edit your post and add the output of the query. Format the output as code. – miracle173 Feb 28 at 20:32
0

The solution to your issue can be:

create the tablespace named as VOLUMT , but for the file name change it a bit like VOLUMT1.dbf or else specify a different directory path to save the file in.

create tablespace "VOLUMT" datafile 'VOLUMT1.dbf' size 250M autoextend on;

please check and tell me if it worked fine.

  • IT WORKS!!! WOW! WHAT WAS THE PROBLEM??? Is $ORACLE_HOME/db/oradata/SID/ the correct directory that dbf is created? – owcred601 May 17 '18 at 6:29
  • It should be the correct directory, if other data files of the same database are stored there then definitely its the correct path – Husam Mohamed May 17 '18 at 6:34
  • The problem was that there is a data file from another database with the same name, they are all stored in the same directory. simply on OS level, you cannot have 2 files with same name. – Husam Mohamed May 17 '18 at 6:35
  • Now I can't find the "VOLUMT.dbf" file on the above directory. How can I check where the file is. Because, once I create a tablespace and remove it, then I can't create it again. ORACLE says the file already exists. I can't understand what it happens. – owcred601 May 17 '18 at 6:36
  • SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'VOLUMT'; – Husam Mohamed May 17 '18 at 6:39
1

all the guys..

First of all, really appreciate to you all for replying...

When I input the follwing

select   file_name,
         file_id,
         tablespace_name,
         status,online_status 
from     dba_data_files 
order by tablespace_name;

The output is

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE STATUS    ONLINE_
---------- ---------- --------- -------
/home/oracle/db/oradata/ORAC12/ID/sysaux01.dbf
    28 SYSAUX     AVAILABLE ONLINE

/home/oracle/db/oradata/ORAC12/ID/system01.dbf
    27 SYSTEM     AVAILABLE SYSTEM

/home/oracle/db/oradata/ORAC12/ID/ID_user01.dbf
    29 USERS      AVAILABLE ONLINE

There is no such as "VOLUMT" tablespace. When I re-try to create a "TEST01" tablespace, eventhough it's already deleted, it shows 'the file exists already'..

When I get into the directory, there are other SID's as well. And, one of them has "VOLUMT". However, they are different "SID" so they can have same name of tablespace, isn't it?

  • When you delete the tablespace, the data file may still exist on OS level (although this is a rare case, but look for the file in the path) – Husam Mohamed May 17 '18 at 6:21
  • I can't find any dbf file such as TEST**.. Could you let me know the exact oracle directory path that all the dbf files are created?? – owcred601 May 17 '18 at 6:23
  • Don't mix up between file name and tablespace name, you can have same tablespace name on different SID's, but you cannot have same file name even if they were on different databases – Husam Mohamed May 17 '18 at 6:23
  • Yes, there is no same name of directory on the SID. However, there is a VOLUMT tablespace in the one of the directory. What do you call the directory? DB? – owcred601 May 17 '18 at 6:26
0

The error you get indicates that there is an existing data file with the same name specified VOLUMT.dbf.

Check if there is an existing file with the same name.

SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE LOWER(FILE_NAME) LIKE '%volumt.dbf';

To find the data file path for TEST01 you can easily query it through the following statement

SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'TEST01';
  • Yes, as you said, I can see it. However, in the case of VOLUMT, it's not.. The result is only 'no rows selected'.. – owcred601 May 17 '18 at 6:21
0

You are only looking at the tablespaces and not at the database files. Try querying the dba_data_files Data Dictionary View.

Here an example command:

set pages 50
set lines 230
column file_name format a60

select file_name, file_id, tablespace_name, status,online_status 
from dba_data_files order by tablespace_name;

This should list all the database files and show you to which tablespace they belong.


In response to your comment:

You might have to switch the SID and run the query for each SID in order to determine which database files names have already been used.

You can't have the same database file (name) for a different SID and a different tablespace in the same directory.

Possible Solutions

  1. You will either have to delete the database file VOLUMT.dbf from the tablespace it is currently attached to, to be able to re-create the tablespace VOLUMT with the `VOLUMT.dbf database file.

  2. You will have to create the tablespace VOLUMTusing the database file VOLUMT.dbf in a different directory.

Your issue isn't the tablespace VOLUMT, but the database file VOLUMT.dbf that already exists for a different SID and a different tablespace.

  • Checked, but I can't find any kind of "VOLUMT" tablespace in my oracle directory... Except for inside of anther SID. – owcred601 May 17 '18 at 6:24
  • I have edited my answer and responded to your feedback. – hot2use May 17 '18 at 6:39
-4

delete the offending tablespace and it's contents. use

DROP TABLESPACE 'the tablespace name' INCLUDING CONTENTS;

for your case;

DROP TABLESPACE VOLUMT INCLUDING CONTENTS;
  • I seriously doubt the asker wants to drop the tablespace and lose all their data! – Colin 't Hart Feb 28 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.