OraFAQ Forum: Backup & Recovery » Can you create new tablespace to existing .dbf file without overwriting the data?

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can you create new tablespace to existing .dbf file without overwriting the data? [message #617354]

Fri, 27 June 2014 11:08

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.

Report message to a moderator

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

Michel Cadot
Messages: 68421
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?

Report message to a moderator

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

John Watson
Messages: 8805
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.

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8805
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.

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8805
Registered: January 2010
Location: Global Village

Senior Member

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5003.htm

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

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

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

thanks!

Report message to a moderator

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

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?

Report message to a moderator

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

Michel Cadot
Messages: 68421
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

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

Thanks Michael.

Report message to a moderator

Previous Topic:	know the size of our database backup file
Next Topic:	Hot backup using RMAN

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 31 03:28:05 CDT 2023