Importing and Exporting command in Oracle

Importing and Exporting are the two commands which are used for backup and restore the data from the Oracle database and to the database. And these are mainly work on the logical structure of the database.

Importing is the command which is mainly used for insert/import the data from file to database.

Exporting is the command used from extracting the data from database to a file.

IMPDP and EXPDP  is the command used to export the file and import the file from one instance to another or same.

Here DP means Data Pump.

It's a feature which is provides fast parallel data load. And it is more faster then the traditional exp/imp. Because traditional exp/imp are running under client side but expdp / impdp are running on server side (And you know very well that the programs which are running in server is usually faster then in programs running on the client machine.)

Some Disadvantages of Data pump

But the Startup time for data pump is longer. Because it will going to set some of the components. for example set up the jobs,queues and Master Table. And at the end of the export operation the master table data is written to the dump file set.

Advantages:

1. We can export the same data into different files of different  disks.

2. Exported from remote database by using database link.

3 You can change the target file name while doing the impdp.

4. Data can be imported from one DB to another without writing dump files.

The exports can be used to recover single data structures to the date and time the export was taken.

There are 3 types of Exports present 1. full 2. Cumulative   and 3. Incremental.

Full export is means taking out the full backup of the logical copy of the database and it's structure.

A cumulative provides a complete copy of altered structures since the last full or the last cumulative export. Incremental exports provide a complete copy of altered structures since the last incremental, cumulative, or full export.

Before you are going to some practical thing lets have some of the term which you should know,so that it will helped us what is this command means and what are the parameters are needed for doing the export and import the files into the database and from the database.

For export the export utility is used (i think you know very well what do you mean a utility - its a program which helps to run other program) .

Format be : EXP keyword==value or keyword==(list of values)

Example: EXP AULT/AUTHOR GRANTS=N TABLES=(CHAPTERS, EDITORS,ADVANCES).

Steps to import and export the file.

First be sure that you have a directory called C:\Temp (Need for windows user)

Otherwise create directory dmpdir as 'c:\temp';

Select the directory _name ,path from dba_directories;

 

How to do the dmpdir directory in \temp

 

First of all login and then create the directory.

 

C:\Temp>sqlplus system/oracle

 

the above command make you connected to the oracle database 11g express edition 

 

Now create the directory called dmpdir on that path.

 

SQL> create directory dmpdir as 'c:\temp';

 

It will successfully created a directory for you.

 

Now check the direcotry_name ,path from the dba_directories

 

SQL> SELECT directory_name, directory_path from dba_directories.

 

When you will execute the query ,it will show the full path of the directory dpdir which you have just created . i.e c:\temp 

 

 

Now lets we are going to export a schema called SCOTT without knowing the  password of the user.

 

You need to use the following syntex.

 

C:\Temp> expdp system/oracle dumpfile=scott.dmp directory=dmpdir schemas=scott logfile= scott_exp.log

 

 This will export the files for you.

 

Now go and check whether the dump file has exported or not.

 

Use the below query :

 

C:\Temp>dir

 

It will you show you the log file as well as the dump file that you just exported in the above steps.

 

 

 

Now the steps for Importing the dump

 

The command you should use while importing the files into the database.

 

c:\temp>  impdp system/oracle dumpfile=scott.dmp directory=dmpdir schemas=scott logfile=scott_imp.log 

 

 

The best thing for the expdp/impdp is that , both are using the same parameter to export and import the file. I guess as of know you have understood the concepts what i am trying to tell you.

 

 

 

For more study on it please refer the below link, you will get more clarity .

 
http://www.database.fi/2011/05/using-expdp-and-impdp-export-and-import-in-10g-11g/

Or if you want to make a batch file then ust proceed with the site.

https://gist.github.com/bulain/1921864