Restoring a schema


Recently someone truncated all our tables in a particular schema in an Oracle DB.  The infrasture team have restored the server to a temp server in order for me to copy off the files that I need.  

What's the best way to do this?  Copy each table, view, sequence one by one.

Export to a dump file
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

If I import a dumpfile will it just overwrite the schema I have on production?

Any help would be greatly appeciated.

Kind regards,
Coco BeansDesignerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
By default, no, Oracle's import will not overwrite existing objects in a schema.  But you can include the "table_exists_action=append" option so import will load all of the data into your existing tables.
slightwv (䄆 Netminder) Commented:
Depending on the size and configuration of your database, it might be quicker to use transportable tablespaces.  This way you copy the tablespaces/datafiles over and all the object in them come along for the ride.
Coco BeansDesignerAuthor Commented:
The tablespaces are shared by many schemas.
Mark GeerlingsDatabase AdministratorCommented:
Then a DataPump export and import will likely be your best option.  If the size of the schema is a few GB or less, that should go quite fast.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Coco BeansDesignerAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.