Solved

SQL imp command in Oracle 11g

Posted on 2007-12-03
5
18,411 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
[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
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

739 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