Home » RDBMS Server » Backup & Recovery » creating tables using datafiles (oracle 11g)
creating tables using datafiles [message #657638] Wed, 16 November 2016 23:13 Go to next message
albayenes
Messages: 4
Registered: November 2016
Junior Member
Hi everyone,

After recover database using backup controlfile, database gives me error that some datafiles are missing. I could not dropped these datafiles because they were non-empty. So, I decided to drop tablespace and recreate tablespace again and add datafiles to the new tablespace.

I dropped tablespace keeping datafiles. I create new tablespace using same name. I added datafiles to tablespace. But there is no table that I am looking for. My datafiles size is approximately 150 GB. I can not find my tables. Where have my tables gone affter adding datafile to tablespace? How can I bring tables back? Do you have any idea?

Thanks in advance.

Report message to a moderator

Re: creating tables using datafiles [message #657643 is a reply to message #657638] Thu, 17 November 2016 00:39 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
If there were objects in the tabespace, you must have dropped it with DROP TABLESPACE...INCLUDING CONTENTS. SO the the tables are gone. There is nothing you can do. Unless you have a full backup somewhere.

Report message to a moderator

Re: creating tables using datafiles [message #657645 is a reply to message #657643] Thu, 17 November 2016 01:55 Go to previous messageGo to next message
albayenes
Messages: 4
Registered: November 2016
Junior Member
John Watson wrote on Thu, 17 November 2016 08:39
If there were objects in the tabespace, you must have dropped it with DROP TABLESPACE...INCLUDING CONTENTS. SO the the tables are gone. There is nothing you can do. Unless you have a full backup somewhere.
Thanks for reply. I recover my database but this time there is missing file which belongs to other incarnations (they have diffirent resetlog times). How can I change these missing files with my original but belongs to previous incarnation datafiles? Is there any alternation command like "change this missing files with this datafiles"?

Is Instead of drop tablespace....includeing contents, only drop tablespace better or not for my scenerio?

[Updated on: Thu, 17 November 2016 01:57]

Report message to a moderator

Re: creating tables using datafiles [message #657646 is a reply to message #657645] Thu, 17 November 2016 02:33 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
If there is a table in the tablespace, a simple DROP TABLESPACE cannot succeed. The only way is with the INCLUDING CONTENTS clause. SO if you have indeed dropped the tablespace, you already know what commands work and what commands do not.

Do you understand what a relational database is? The abstraction of logical storage (tables) from physical storage (datafiles)? You can't expect your database to understand the contents of datafiles when you have the removed the object definitions from the data dictioranry.

Report message to a moderator

Re: creating tables using datafiles [message #657649 is a reply to message #657646] Thu, 17 November 2016 02:45 Go to previous messageGo to next message
albayenes
Messages: 4
Registered: November 2016
Junior Member
John Watson wrote on Thu, 17 November 2016 10:33
If there is a table in the tablespace, a simple DROP TABLESPACE cannot succeed. The only way is with the INCLUDING CONTENTS clause. SO if you have indeed dropped the tablespace, you already know what commands work and what commands do not.

Do you understand what a relational database is? The abstraction of logical storage (tables) from physical storage (datafiles)? You can't expect your database to understand the contents of datafiles when you have the removed the object definitions from the data dictioranry.
Thanks for reply. I am sorry my silly question. Is there any way changing missing files with my datafiles after recover my database or Is there any way adding my datafiles (they have different resetlogs_time so i cannot only rename my missing files with my original datafiles) to table space after recover my database? If I don not delete missing files from my tablespace database gives error about there is missing files that cannot be read.

Report message to a moderator

Re: creating tables using datafiles [message #657651 is a reply to message #657649] Thu, 17 November 2016 02:51 Go to previous message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
You need to do some studying. I have already told you why what you suggest is impossible. Think about it: how can your current data dictionary understand the contents of datafiles from some indeterminant time in the past?

Report message to a moderator

Previous Topic: Drop table
Next Topic: RMAN background work
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Feb 22 21:45:16 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.