Thursday, September 22, 2016

Import Oracle Database

In the name of Allah, Most Gracious, Most Merciful
Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that,

Well, after discussing the export utility  to create an Oracle binary-format dump file, we will logically need to learn about  reading this binary file through using the Import utility.

Task Definition:   

Import  data  from dump files into an Oracle  database

 What Is the Import Utility?

        The Import utility is used to read data from Export files into an Oracle database. Export dump files can only be read by the Oracle Import utility. 

   Before using Import :            

  • Run the catexp.sql or catalog.sql script.
  • Verify that you have the required access privileges.

Task Solution:

There are two Import utility methods  you have to consider:
1.     imp (Old & not recommended).
  2.     impdp  (New & recommended).

Both methods will do the same function; import a dump file e.g. file_name .dmp and store the imported data into the database.

 The question you may ask is...
 What is the difference between both methods if they are giving me the same output ?

The Answer to Your Question:

·        Data Pump recreates  the user, whereas the old imp utility requires you or the DBA to create the user ID before importing.
·        The original import utility dates back to the earliest releases of Oracle, and it's quite slow and primitive compared to Data Pump that  gives 15 – 50%  performance improvement than exp/imp.
·        It's much easier to implement as you will see.

Now,let me ask you:  How do you export  your data ...?
 You have only two answers either using exp or using expdp method.

·         Using exp and a full schema requires these Solution Steps:
1.    Create the user:

create user identified by default tablespace quota unlimited on ;

2.    Grant the rights:

Grant connect, create session, imp_full_database to ;

3.    Start importing Data:

    Select Start  menu> select Run> write CMD then a window will open write the following command according to your naming requirements:

impdp user_name/password@database_instance   file= file_name.dmp  log=log_file_name.log full=y;
 impdp Scott/tiger@orcl  file=test.dmp  log= test_log.log  full=y;     
·         Using expdp and a full schema requires you to only write this command line:
 Select Start  menu> select Run> write CMD then a window will open write the following command according to your naming requirements:

impdp user_name/password@database_instance   file= file_name.dmp  log=log_file_name.log    full=y;
impdp Scott/tiger@orcl  file=test.dmp  log= test_log.log full=y;
          The Data Pump Import utility is invoked using the impdp command. The characteristics of the import operation are determined by the import parameters you specify. These parameters can be specified either on the command line or in a parameter file.

·        The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.

Learn more about:

·        Export Utility

Hope this helps...

My success only comes from Allah, pls. note your comments and suggestions are great help for me in progress thanks in advance.