11 Replies Latest reply: Jun 5, 2014 8:12 AM by Jeff Smith Sqldev Pm-Oracle RSS

    Exp/imp using sql developer!

    446


      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. Re: Exp/imp using sql developer!
          Top.Gun

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

          • 2. Re: Exp/imp using sql developer!
            Top.Gun

            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

            • 3. Re: Exp/imp using sql developer!
              446

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

              • 4. Re: Exp/imp using sql developer!
                Emad Al-Mousa

                this link has detailed snap shot images guide:

                 

                ORACLE-BASE - SQL Developer 3.1 Data Pump Wizards (expdp, impdp)

                 

                 

                Regards,

                • 5. Re: Exp/imp using sql developer!
                  446

                  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.

                  • 6. Re: Exp/imp using sql developer!
                    Emad Al-Mousa

                    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,

                    • 7. Re: Exp/imp using sql developer!
                      rp0428
                      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'.

                      • 8. Re: Exp/imp using sql developer!
                        446

                        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

                        • 9. Re: Exp/imp using sql developer!
                          rp0428
                          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

                          • 10. Re: Exp/imp using sql developer!
                            446

                            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?

                            • 11. Re: Exp/imp using sql developer!
                              Jeff Smith Sqldev Pm-Oracle

                              Yes

                               

                              http://docs.oracle.com/cd/E16655_01/server.121/e17639/dp_import.htm#SUTIL899

                               

                              But this isn't really a SQL Developer question. In terms of SQL Developer and the Data Pump import support - we assume the import file is on the database server in a database directory. So if you want to use SQL Developer to run it, you'll need to move that file over.