Solved

SQL imp command in Oracle 11g

Posted on 2007-12-03
5
18,383 Views
Last Modified: 2013-12-07
Hi, can someone give me the correct syntax for using the SQL imp command in Oracle? I am following several work cases in the newly released book Pro Oracle Spatial. They instruct to execute the following;

imp spatial/spatial file=gc.dmp ignore=y full=y
imp spatial/spatial file=map_large.dmp tables=us_interstates

Do I need to configure a jdbc connection or other type, to point to the source directory for these files? They were to be downloaded to the O/S harddrive that hosts the DB, but at present they are not in Oracle (as far as I know)
0
Comment
Question by:BlueSkyTelematicsInc
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:sonicefu
ID: 20400939
Hello BlueSky !

1) imp  is not an SQL command, it is oracle import utility command to import data from a dump file (fileName.dmp), created by oracle export utility (exp command).

2) Now you need a dump file to import (using imp command)

3) imp spatial/spatial file=gc.dmp ignore=y full=y
    In this case spatial is a username and spatial after / is password and dump file name is gc.dmp, you  should write it with full path, if you are using windows then file=c:\gc.dmp otherwise file=/u02/dump/gc.dmp. full=y means import all contents of this (gc.dmp) file.

*** Clarify your complete scenario where you want to use this

0
 

Author Comment

by:BlueSkyTelematicsInc
ID: 20400985
I have 11g on RHEL5
The DB and the .dmp file are on the same drive, (but the .dmp is in the o/s env;not Oracle_HOme directories..../opt/dwnlds/NAVTEQ to be specific)
So, If I understand, from anywhere in linux command line can I run the "imp" utlility? Do I need to set my path or be in the ORACLE_HOME/bin directory to run it? (I was thinking I would run it from SQL> ; but no?)
Lastly, do I have to specify anything beyond  the ignore-y and full=y options? Or will the .dmp file include the required meta for the destination tables?
0
 
LVL 13

Accepted Solution

by:
sonicefu earned 250 total points
ID: 20401036
--> from anywhere in linux command line can I run the "imp" utlility?
Yes, you can ! you have to write it as
imp userName/PaswordOfUser file=/pathWhereDmpFilePlaced/dmpFileName.dmp full=y

--> I was thinking I would run it from SQL> ; but no?
no, you were thinking wrong, its not SQL command

at linux command line

--> Do I need to set my path or be in the ORACLE_HOME/bin directory to run it?
it is not necessary

--> Lastly, do I have to specify anything beyond  the ignore-y and full=y options?
read following for details

**Help for Export/Import (exp/imp)
http://www.orafaq.com/wiki/Export
http://www.orafaq.com/wiki/Import

***Oracle data pump (expdp/impdp) is replacing simple exp/imp, read following for information about Data Pump
http://www.orafaq.com/wiki/Datapump
0
 

Author Comment

by:BlueSkyTelematicsInc
ID: 20401147
Hey sonicefu.....thank you for being sonice2me!
I will give this a shot and let you know. It will likely be tomorrow evening though, it is late here. I'll drop you a confimation if it works. I have just reviewed the "impdp help=yes" results, and they are very thorough; I should be able to take it from there. One small detail for your reference, it apears it is necessary to have the ORACLE_HOME  variable set in order to execute. The reading also states you can invoke from PL/SQL as outlined below, but I am with you in that it is a lot easier via Linux command line. Here is an excerpt from the "Datapump" url you referenced, just for your future info....

Thanks Again!

One can invoke datapump from PL/SQL -- this might be handy for scheduling a daily or weekly export with DBMS_SCHEDULER.

DECLARE
  hand NUMBER;
BEGIN
  hand := Dbms_DataPump.Open(operation => 'EXPORT',
                              job_mode => 'FULL',
                              job_name => 'FULLEXPJOB',
                               version => 'COMPATIBLE');
  Dbms_DataPump.Add_File(handle => hand,
                       filename => 'expdp_plsql.log',
                      directory => 'DMPDIR',
                       filetype => 3);
  Dbms_DataPump.Add_File(handle => hand,
                       filename => 'expdp_plsql.dmp',
                      directory => 'DMPDIR',
                       filetype => 1);
  -- Dbms_DataPump.Set_Parameter(handle => hand,
  --                               name => 'ESTIMATE',
  --                              value => 'STATISTICS');
  Dbms_DataPump.Start_Job(hand);
END;
/
0
 

Author Closing Comment

by:BlueSkyTelematicsInc
ID: 31412502
Excellent Help, and very very timely!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

932 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

19 Experts available now in Live!

Get 1:1 Help Now