Forum Stats

  • 3,751,638 Users
  • 2,250,379 Discussions
  • 7,867,490 Comments

Discussions

Exp/imp using sql developer!

446
446 Member Posts: 119
edited Jun 5, 2014 9:12AM in SQL Developer


Hi - Using sql develepor(3.0) i took the export(Tools -> Database export) of an oracle database (residing in remote linux server) and stored the dump file in the local directory,now i need to import the oracle dump to the oracle XE 10g(installed in my local machine) ,but i don't find any option in sql developer to import using dump file.

Since am using v 3.0, i tried to use expdp in the remote database but i don't have privilege to do this on remote server environment.

Is their any other way to do it?

«1

Answers

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Are you saying SQL developer allows you to export but not import?

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    If you have the dumpfile sitting on your local workstation, then you can still use the imp command line to import it.

    Here is some information on the import utility:

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_import.htm#i1023560

  • 446
    446 Member Posts: 119

    is their any other way to import the dmp file using some of the utility available in sql developer?

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
  • 446
    446 Member Posts: 119

    Thanks..When i tried to take expdp/impdp using DBA option ,am getting insufficient privilege .Also Since data pump is a server side utility can we take and store the backup in the local machine and import the same to the oracle XE database.

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy

    grant the user that you are performing the expdp/impdp operation the following roles (DATAPUMP_EXP_FULL_DATABASE / DATAPUMP_IMP_FULL_DATABASE)

    SQL> grant DATAPUMP_EXP_FULL_DATABASE to user;

    and try again.

    Regards,

  • Hi - Using sql develepor(3.0) i took the export(Tools -> Database export) of an oracle database (residing in remote linux server) and stored the dump file in the local directory,now i need to import the oracle dump to the oracle XE 10g(installed in my local machine) ,but i don't find any option in sql developer to import using dump file.
    
    Since am using v 3.0, i tried to use expdp in the remote database but i don't have privilege to do this on remote server environment.
    Is their any other way to do it?
    

    What version of sql developer are you using? Version 3.0.02 doesn't have a 'Tools => Database export' it has 'Tools => Database unload'.

  • 446
    446 Member Posts: 119

    We are using v3.2 and have the option Tools->Database Export ,when i took the backup using this option it got stored (.sql dump file) in my local directory,is their any other option to make use of this dmp to import to Oracle XE env?

    Also,by making use of expdp/impdp options,can we store the dmp in our local machine since the destination database(oracle xe) is resding in our local machine?

    Source ->oracle db ,linux server(remote) ..destination -> oracle XE,windows 7 local machine

  • We are using v3.2 and have the option Tools->Database Export ,when i took the backup using this option it got stored (.sql dump file) in my local directory,is their any other option to make use of this dmp to import to Oracle XE env?
    
    

    Using 'Export' in Sql Developer is NOT the same as using Oracle's Data Pump functionality (expdp/impdp).

    1. Sql dev is a 'client' tool; Data Pump is a server tool

    2. Sql dev creates 'text' files (e.g. INSERT statements); Data Pump creates 'binary' files in a proprietary format.

    If you used sql developer then the file (or files) you have can be executed as sql scripts. Open one with an editor (or load it into a Sql dev editor) and look at it.

    Also,by making use of expdp/impdp options,can we store the dmp in our local machine since the destination database(oracle xe) is resding in our local machine?

    Data Pump is a 'server' side tool. It produces a file on the server. You can then 'store' that file whereever you want. But it is a BINARY file. So if you move it from a *nix machine to a Windows machine you need to move it as a BINARY file and not a text file.

    Because the file does NOT depend on the original OS then yes, you can import a data pump file on a server that uses a different platform.

    Which method you use, Sql Dev or Data Pump, depends on 1) how you plan to use the data, 2) how much data you need to deal with and, most importantly, whether you need to import that data back into the original system.

    Data Pump has a LOT of options for which parts of the exported data you want to import. See the Utilities doc and the internet for examples

    http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm

  • 446
    446 Member Posts: 119

    I have some questions-

    Since datapump is a server side utility, the backup will get stored in the server side (here we are not stroing back to original source) and we want to import the dump file in Oracle XE environment which is stored in my local machine(Windows 7).

    Can we use the option available in SQL developer (DBA-> expdp/impdp wizard) to import the same dmp file to the oracle XE environment?

This discussion has been closed.