Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01537 when creating tablespace
ORA-01537 when creating tablespace [message #230266] Wed, 11 April 2007 06:30 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i want to create a new tablespace, and gave the following
commands


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF


SQL> select a.TABLESPACE_NAME, round(total,1) Total_M, round(free) Free_M, round
(100*(1-free/total),1) Usage
  2  from (select TABLESPACE_NAME,sum(BYTES)/(1024*1024) total from dba_data_fil
es group by TABLESPACE_NAME) a
  3  ,(select TABLESPACE_NAME,sum(BYTES)/(1024*1024) free from dba_free_space gr
oup by TABLESPACE_NAME) b
  4  where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) order by 4 DESC;

TABLESPACE_NAME                   TOTAL_M     FREE_M      USAGE
------------------------------ ---------- ---------- ----------
SYSTEM                                490          5         99
USERS                                21.3          1       97.1
SYSAUX                                360         13       96.4
EXAMPLE                               100         23       77.4
UNDOTBS1                               30         18       41.7


SQL> create tablespace ts_saletarget datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\
ORCL\EXAMPLE01.DBF' size 15M;
create tablespace ts_saletarget datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\
EXAMPLE01.DBF' size 15M
*
ERROR at line 1:
ORA-01537: cannot add file
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' - file already part of
database


SQL> create tablespace ts_saletarget datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\
ORCL\EXAMPLE01.DBF' size 15M;
create tablespace ts_saletarget datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\
EXAMPLE01.DBF' size 15M
*
ERROR at line 1:
ORA-01537: cannot add file
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF' - file already part of
database






i wanted to create a tablespace, and i am getting this
problem, as am trying to use an existing datafile


Re: ORA-01537 when creating tablespace [message #230268 is a reply to message #230266] Wed, 11 April 2007 06:44 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Isnt the error text
Quote:
file already part of database

clear ?

google..
Quote:

Oracle Error :: ORA-01537

cannot add file "string" - file already part of database
Cause

During CREATE or ALTER TABLESPACE, a file being added is already part of the database.
Action

Use a different file name.

[Updated on: Wed, 11 April 2007 06:46]

Report message to a moderator

Re: ORA-01537 when creating tablespace [message #230271 is a reply to message #230266] Wed, 11 April 2007 06:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't have the same physical datafile belonging to more than one tablespace..
Previous Topic: IP Address of Users
Next Topic: deleting duplicate rows
Goto Forum:
  


Current Time: Tue Dec 06 06:30:32 CST 2016

Total time taken to generate the page: 0.13769 seconds