SQL imp command in Oracle 11g

Posted on 2007-12-03
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)
Question by:BlueSkyTelematicsInc
  • 3
  • 2
LVL 13

Expert Comment

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


Author Comment

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?
LVL 13

Accepted Solution

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)

***Oracle data pump (expdp/impdp) is replacing simple exp/imp, read following for information about Data Pump

Author Comment

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.

  hand NUMBER;
  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');

Author Closing Comment

ID: 31412502
Excellent Help, and very very timely!!

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
use lov values 2 71
Oracle encryption 12 59
Assistance required for troubleshooting an issue - ORA-12154: TNS error - MS SQL Server 2008 3 52
Pivoting oracle table 9 72
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

713 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