Oracle database exports and imports question

I need to learn how to do database exports and imports at user level and full ones.

Can someone guide me through the process?

I also need to learn how to clone a database using expdp/impdp
LVL 35
YZlatAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Good article here on this:

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

Cloning a database via export/import can be easy or hard depending on a few factors.

Does your clone database already exist?
Are there a lot of foreign key constraints (had problems in the past with this, haven't tried recently to see if it's better)

Personally, I've found it to be much faster to do a restore from backup when cloning a database.  Not only does it get the job done (usually faster unless it's a very small database), you also get to test your backups while you're at it.
0
 
YZlatAuthor Commented:
I am just running throught the process for learning purposes. I have a test database on one server.
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
OK then for ease of use:

Create another empty database much like your first one (users, tablespaces etc).

Export as per the article above from the first database.
Import as per the article above into the second database.

If you want to do it more than once, you will need to take action to decide what to do with any existing tables.   Check the TABLE_EXISTS_ACTION option in the parameters for impdp.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
YZlatAuthor Commented:
I just tried to run full export on my db and got permissions errors. What permissions does a user need to run a full database export?
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
See: http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#SUTIL2641

"To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all database administrators (DBAs).

If you do not have the system privileges contained in the EXP_FULL_DATABASE role, then you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it."
0
 
YZlatAuthor Commented:
so most likely I cannot do that as an oracle user or as myself?
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Guess that depends if you have DBA access or not.

If you're not the DBA, you can only export objects owned by you.

If you have DBA access (or the EXP_FULL_DATABASE privilege) on the user you connect to the database as, then you can.

Do you have an account you can run expdp as that has the required privs?
0
 
YZlatAuthor Commented:
OK, both exports worked for me, I just have to do imports now.

What about database cloning?
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
I see you've opened a new question on this topic, hadn't had a chance to respond to this yet, will let that part of the question be answered in the new topic.

As mentioned above (and in the replies to the new question) an import isn't a "clone" - you need to have an existing database to import into.
0
 
YZlatAuthor Commented:
Thanks for all your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.