Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle database schema import

Posted on 2013-11-20
11
Medium Priority
?
1,279 Views
Last Modified: 2013-12-12
I need to copy an Oracle database from a database A to database B. Someone created a database dump file (schema export, schema name account1) for me: abc.dmp.gz
The dump files has 9 underlying tables with records of the source database A and schema account1.

Now I need to import the above to my local database B (Oracle again). Could you let me know the command I need to use to import the dump file into my database B. In the database B I do not have a schema named account1. Thanks a lot.
0
Comment
Question by:toooki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 19

Expert Comment

by:Thommy
ID: 39662351
Your import command should look like that:

imp system/xxxxxx fromuser=account1 touser=account1 file=abc.dmp commit=Y ignore=Y log=imp_abc.log
0
 
LVL 19

Expert Comment

by:Thommy
ID: 39662354
Before importing verify that your schema is created in your database.

Elsewhere create a schema: Create user account1...
0
 
LVL 23

Expert Comment

by:David
ID: 39662438
It will significantly improve your chances if if you unpack the gzip compressed file, first. http://www.gnu.org/software/gzip/

Thommy and I are also assuming you're using the older imp/exp executables rather than Datapump.  Correct us if that's incorrect, there's different syntax.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 19

Expert Comment

by:Thommy
ID: 39662569
OK, unpacking the gz file before importing should go without saying!

Of course, it is true what dvz mentioned: I was assuming the older import/export utilities

If your dump file is from a newer datapump export then it's a little bit more complicated, because you cannot access your dmp file directly. For the DIRECTORY parameter you have to refer to a database directory object, which specifies the location where impdp utility can find your dmp file.

The import command should go like that:
impdp system/xxxxx   SCHEMAS=account1
                                          DIRECTORY=DATA_PUMP_DIR
                                          DUMPFILE=abc.dmp       
                                          LOGFILE=impdp_abc.log


For database directory objects please see CREATE DIRECTORY
0
 

Author Comment

by:toooki
ID: 39663978
Thank you all. I do not have access to the database server but I have access to the database via a regular user. So I think the imp or impdp commands shall not work for me.
Is there any way to import the data via a regular Oracle database user?
Thanks a lot.
0
 
LVL 23

Accepted Solution

by:
David earned 1000 total points
ID: 39664043
imp/exp require access to the drive holding the .exp file, whether it's on the server or the client.  Secondly, on the client, they would have to be installed (as part of the standard Oracle client install).

So it's feasible for a user to export and import one's own tables, etc. -- but anything higher would require system privileges to be granted.
0
 
LVL 19

Expert Comment

by:Thommy
ID: 39665107
If your .exp file is a newer type datapump export, then you cannot import from an Oracle client machine. expdp/impdp utilities are server-based and not part of an Oracle client installation. For executing datapump you definitely need access to the database server!

Can you actually clarify what kind of .exp file you have? Do you have an export logfile that comes with your .exp file? From a logfile you can determine the kind of utility that was used for exporting, exp.exe or expdp.exe.

You can even try importing by imp.exe from your client machine. If it's not a valid older type export file, you will get following errors:
IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
0
 

Assisted Solution

by:Ven_Aluri
Ven_Aluri earned 1000 total points
ID: 39679278
unzip the dumpfile

SQL > Create user account1 identified by password default tablespace users;
SQL > create dump directory
SQL > grant read,write on directory dumpdir to system

$ impd system/password directory=dumpdir dumpfile=abc.dmp logilfe=abc_imp.log
fromuser=account1 touser=account1 remap_tablespace=Source_tablespacename:Users
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39679446
Ven_Aluri,

I see you are new to the site.  Welcome.

I don't see how your impdp command will help answer this question when the asker stated " I do not have access to the database server" in http:#a39663978

You also posted this "SQL > create dump directory" implying it is a SQL command.  It isn't.

In the doc links I have, I don't see a FROMUSER/TOUSER parameter for datapump.  Those are original imp parameters.  If you have reference material for them in datapump import,  please post it, I'll take a look.
0
 
LVL 23

Expert Comment

by:David
ID: 39681825
"Importing Objects of One Schema to another Schema

The following example loads all tables belonging to hr schema to scott schema

$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

REMAP_SCHEMA=hr:scott

If SCOTT account exist in the database then hr objects will be loaded into scott schema. If scott account does not exist, then Import Utility will create the SCOTT account with an unusable password because, the dump file was exported by the user SYSTEM and imported by the user SYSTEM who has DBA privileges. "

HTH
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

670 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