Home » RDBMS Server » Backup & Recovery » Can you create new tablespace to existing .dbf file without overwriting the data? (11g 11.2.0.1 , windows server 2008r2 standard)
Can you create new tablespace to existing .dbf file without overwriting the data? [message #617354] Fri, 27 June 2014 11:08 Go to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
I have read online that there is no way to do this is that true? There is a REUSE statement, but that overwrites the data to existing .dbf files. Appreciate the help.
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617362 is a reply to message #617354] Fri, 27 June 2014 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Can you create new tablespace to existing .dbf file without overwriting the data?


No.

What problem are you trying to solve?

Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617363 is a reply to message #617354] Fri, 27 June 2014 11:47 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
Are you asking if you can create a tablespace that uses an existing datafile, in such a way that the segments in the datafile are usable? No way. The only way to bring an existing tablespace into a database is to use tablespace transport, which requires a Data Pump dump that describes the contents of the tablespace.
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617364 is a reply to message #617363] Fri, 27 June 2014 11:53 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Ok that answers my question. Thanks. These dbf files are the only records we have for this tablespace we need. We have no backups and the tablespace was dropped. We have another same db, but it's in MSFT SQL 2008 and I'm having issues using SQL Developer migration tool to convert that SQL db to a Oracle db.
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617365 is a reply to message #617364] Fri, 27 June 2014 11:55 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
Well, if you have all the datafiles you can re-create the controlfile and the database will open no problem.
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617680 is a reply to message #617365] Wed, 02 July 2014 12:11 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Hello John,

Any links or advice on how to re-create the controlfile? Much appreciated. Thanks.
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617682 is a reply to message #617680] Wed, 02 July 2014 12:13 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5003.htm
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617683 is a reply to message #617680] Wed, 02 July 2014 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
JP10 wrote on Wed, 02 July 2014 10:11
Hello John,

Any links or advice on how to re-create the controlfile? Much appreciated. Thanks.


consider using CREATE CONTROLFILE statement

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_5004.htm#SQLRF01203
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617685 is a reply to message #617683] Wed, 02 July 2014 12:18 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
thanks!
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617689 is a reply to message #617365] Wed, 02 July 2014 12:36 Go to previous messageGo to next message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
John,

So, if I recreate the control file your saying the missing tablespaces will be available as well?
Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617691 is a reply to message #617689] Wed, 02 July 2014 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No.
As you dropped the tablespace the only thing you can do is to go your whole database back to the time before the tablespace was deleted.
(Then you can export the tablespace, roll forward your database to the current time, recreate and import the tablespace.)
If you have no backup of other datafiles or are in noarchivelog mode, you are out.

[Updated on: Wed, 02 July 2014 12:46]

Report message to a moderator

Re: Can you create new tablespace to existing .dbf file without overwriting the data? [message #617693 is a reply to message #617691] Wed, 02 July 2014 12:44 Go to previous message
JP10
Messages: 29
Registered: May 2014
Location:
Junior Member
Thanks Michael.
Previous Topic: know the size of our database backup file
Next Topic: Hot backup using RMAN
Goto Forum:
  


Current Time: Thu Oct 02 05:47:04 CDT 2014

Total time taken to generate the page: 0.80224 seconds