Solved

SQL imp command in Oracle 11g

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

744 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

10 Experts available now in Live!

Get 1:1 Help Now