Export User and All Data in Oracle

I know there is a way but I can't seem to find the proper syntax for me to export a particular user from an oracle DB and import it into the cloned version of that same db...any help would be appreciated.
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.

slightwv (䄆 Netminder) Commented:
Mark GeerlingsDatabase AdministratorCommented:
Yes, expdp is the way to export a user (schema) and any/all objects that user owns.  Then, you can coy the resulting "dump" file(s) to your other server, and use impdp to read it/them, and import the data and objects.  You may need to create the user account first in the target system.  Also, if your tablespaces are different in the target database, you will need to provide additional parameters to impdp to accommodate the difference(s).
jjmekkattilAuthor Commented:
Not sure what I'm doing wrong here but here exactly what I typed:

D:\>expdp sys DIRECTORY=TEST DUMPFILE=test_user.dmp SCHEMAS=test_user

Export: Release - Production on Tue May 26 14:02:37 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Username: sys/password as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release - 64bit

With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name TEST is invalid

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
Did you create the TEST directory?  This is a DATABASE directory, not one on the file system.

connect to sqlplus and issue:
select * from dba_directories;
jjmekkattilAuthor Commented:
ok got it....now when doing an import is there anything in particular I need to do in order to conduct an import? ie shutoff database
slightwv (䄆 Netminder) Commented:
Only the things markgeer pointed out:
The user will need to exist prior to importing.

You will need to decide on what to do about objects that might already exist.  There is a TABLE_EXISTS action.

Depending on what you are doing, it might be best to drop and recreate the user then import.

I don't know what you mean by "shutoff database" but if you mean shut down, then you cannot.  The database has to be up to import.
jjmekkattilAuthor Commented:
Ok so here is the scenario...the production database has a corrupted schema that the table that it owns is kills a particular program that accesses that table.  So I am going to a "snapped" version of that database exporting that data and importing into the production database...this should work correct?
slightwv (䄆 Netminder) Commented:
When dealing with a production system and mentioning corruption:  I would work directly with Oracle Support!!!

What have you done to diagnose the actual 'corruption'?
Why export/import an entire schema to fix one table?

The export/import might actually make the problem worse.

I would never do a "just try it and see" approach when dealing with a production system.
jjmekkattilAuthor Commented:
well my "plan" was to export the corrupted schema into the test database (after exporting the working schema) then import that into the test database...after which reimporting the working schema to see if it works if not...I got to do a restore of the entire database for the one schema.
slightwv (䄆 Netminder) Commented:
>>...I got to do a restore of the entire database for the one schema.

I don't understand your need to do this but you know the issues...

Still seems like you are just trying things to see if it works instead of identifying the exact cause of the problems.

Can you export from production and import into test, sure.

Does that mean the 'problem' is 'fixed', no way to say.  Just because the 'problem' seems to 'go away', it might just be masked temporarily...
jjmekkattilAuthor Commented:
The user corrupted the data...when I asked how he didn't know I tried what I everything I could to fix it but it only looked as though it was getting worse....it has to do with an ESRI product with the backend being oracle...ESRI's helpdesk made it worse.  So this is my predicament.
slightwv (䄆 Netminder) Commented:
If you have bad data I don't see how exporting and importing will fix it.

If you have block corruption, there are other ways to repair it.

Again, I would work directly with Oracle Support.  Especially since you say it is getting worse...

You might get into a position that you cannot get out of and actually lose data.

I would not recommend export/import as a way to try and 'magically fix' a corruption problem.

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
Mark GeerlingsDatabase AdministratorCommented:
It looks to me like the problem is not (physical) "data corruption", at least not how Oracle defines "data corruption".  It looks like what you have is "logical corruption", that is, an update (or maybe a delete?) resulted in changes to data that now make it incorrect.

When something like this happens, I think an Oracle database feature that you may not be aware of called: "flashback query" would be more useful next time, than attempting an export, and an import into a second system.  In this case, since a day has passed, you likely cannot use "flashback query" to help you.  But, depending on how busy your database is (or not) and on how large your undo tablespace is, and on what value your database has for "undo retention", that may be able to help you even this time.

That Oracle database feature allows us to write SQL queries like this example (to look back one hour):

select * from [table_name] as of timestamp sysdate - 1/24
where ... [whatever conditions you want]

That can show you the values a table had at points in time in the recent past.  How long ago is "recent"?  That depends on what I mentioned:
1. how busy (that is how many transactions, not queries, are being done)
2. how large your undo tablespace is
3. what value you have in your init*ora or spfile for "undo_retention".  The unit of measure for this is seconds, and the default is usually: 900, or 15 minutes.  I have that value though set to 28000 (for 8 hours) in one of our busy systems.

Even if you do a successful export and import now, that will be after the data has been changed/corrupted.  If you have nightly or weekly database backups, you should be able to restore one of them from prior to the problem.  Then you could use expdp to extract out a single table, then import that into a test system, or into a separate schema in your main database.  Then you could compare the data, and/or fix the data in your main table(s) *IF* what you restored contains what you are looking for.

If you don't have nightly or weekly backups, you should start doing them.  And, for modestly-sized databases (up to a few hundred GB at least) I like doing nightly exports in addition to nightly backups, since recovery from "logical" data corruption is often much easier from an export than from a backup.
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
Oracle Database

From novice to tech pro — start learning today.