Solved

Oracle database exports and imports question

Posted on 2013-01-08
10
346 Views
Last Modified: 2013-01-10
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
0
Comment
Question by:YZlat
  • 5
  • 5
10 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38755453
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
 
LVL 35

Author Comment

by:YZlat
ID: 38755833
I am just running throught the process for learning purposes. I have a test database on one server.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 38755898
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
 
LVL 35

Author Comment

by:YZlat
ID: 38756099
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
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 38756153
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 35

Author Comment

by:YZlat
ID: 38756288
so most likely I cannot do that as an oracle user or as myself?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 38756309
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
 
LVL 35

Author Comment

by:YZlat
ID: 38756355
OK, both exports worked for me, I just have to do imports now.

What about database cloning?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 38760522
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
 
LVL 35

Author Closing Comment

by:YZlat
ID: 38762812
Thanks for all your help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now