Home » RDBMS Server » Server Administration » error in re-creation of a datafile
error in re-creation of a datafile [message #577118] Tue, 12 February 2013 08:57 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,

i can't re-create a datafile.


c:\> sqlplus

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select name,ts# from v$tablespace;

NAME                                  TS#
------------------------------ ----------
SYSTEM                                  0
SYSAUX                                  1
UNDOTBS1                                2
USERS                                   4
TEMP                                    3
CATALOGTS                               9
CATTS                                   7

7 rows selected.

SQL> drop tablespace catalogts including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace catts including contents and datafiles;

Tablespace dropped.

SQL> select name,ts# from v$tablespace;

NAME                                  TS#
------------------------------ ----------
SYSTEM                                  0
SYSAUX                                  1
UNDOTBS1                                2
USERS                                   4
TEMP                                    3

SQL> create tablespace rcatts datafile 'c:\oradata\rcat\rcatts01.dbf' size 100m
  2  autoextend on next 10m maxsize 500m;
create tablespace rcatts datafile 'c:\oradata\rcat\rcatts01.dbf' size 100m
*
ERROR at line 1:
ORA-01119: error in creating database file 'c:\oradata\rcat\rcatts01.dbf'
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

SQL> 


why can't it create re-create the datafile even i dropped tablespace with its content and its datafiles?

thanks.......
Re: error in re-creation of a datafile [message #577120 is a reply to message #577118] Tue, 12 February 2013 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
restart Oracle DB
Re: error in re-creation of a datafile [message #577122 is a reply to message #577120] Tue, 12 February 2013 09:39 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks for your response sir,

but i did like that

c:\> sqlplus

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace ts datafile 'c:\oradata\rcat\ts01.dbf' size 100m;

Tablespace created.

SQL> drop tablespace ts including contents and datafiles;

Tablespace dropped.

SQL> create tablespace tss datafile 'c:\oradata\rcat\ts01.dbf' size 100m;

Tablespace created.

SQL>



am i missing anything?

thanks.....
Re: error in re-creation of a datafile [message #577125 is a reply to message #577122] Tue, 12 February 2013 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Tue, 12 February 2013 16:01
restart Oracle DB

Re: error in re-creation of a datafile [message #577528 is a reply to message #577125] Mon, 18 February 2013 04:15 Go to previous messageGo to next message
TuneMyQuery
Messages: 9
Registered: February 2013
Junior Member

Hi,

In some Operating systems (especially windows), the datafile does not get physically deleted, enventhough you specify including contents and datafiles. If you need to recreate a datafile with the same name, a classical workaround is to add the reuse keyword in your create tablespace statement.

Best regards,

Emmanuel
Re: error in re-creation of a datafile [message #577541 is a reply to message #577528] Mon, 18 February 2013 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
especially windows


This is not my experience.
Show us.

Regards
Michel
Re: error in re-creation of a datafile [message #577563 is a reply to message #577541] Mon, 18 February 2013 06:52 Go to previous messageGo to next message
TuneMyQuery
Messages: 9
Registered: February 2013
Junior Member

Hi,

I have personnally experienced it and it is documented in oracle Support note 389467.1 :

[i]Drop Tablespace Including Contents And Datafiles The Datafiles Are Not Automatically Deleted [ID 389467.1]



Best regards,

Emmanuel

[Edit MC: Remove content of the metalink note due to copyright violation]

[Updated on: Mon, 18 February 2013 07:00] by Moderator

Report message to a moderator

Re: error in re-creation of a datafile [message #577565 is a reply to message #577563] Mon, 18 February 2013 07:05 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that this no more exist in the current version and patchset.
And in the other ones, if you restart the database as mentioned, the files can be deleted.

Regards
Michel
Previous Topic: Shared Server problem
Next Topic: Migration of Oracle from AIX to Solaris
Goto Forum:
  


Current Time: Thu Mar 28 17:17:09 CDT 2024