Import and export Utility in Oracle database

[Adriana_andros]

I am having problem with export and import utility of oracle database and unable to perform exporting and importing of database.I am unable to create a flat file and accessing method.Can you help me with the explanation and steps of performing the exporting and importing of database.

[Katty]

Oracle import and export utility is the feature provided by oracle database that is kept into $ORACLE_HOME/bin directory and it is installed automatically when oracle database is being installed . It's primary purpose is to move out and in logical object of the database.

Before executing these commands,$ORACLE_HOME, $ORACLE_SID and $PATH environment variables need to be set perfectely in Unix environment and if you are working in windows environment then the %ORACLE_SID% environment variable needs to be set.

If you are the normal user of the database and want to be facilitate from these utility then you should have exact grant from the super user of database (DBA).

[kelfro]

Using Export Utility:

Exporting the whole database into a single file my_dba.dmp in the current directory.

Log in to server:

exp SYSTEM/password FULL=y FILE=my_dba.dmp LOG=my_dba.log CONSISTENT=y

Login into the server which already has an Oracle client:

exp {user}/{password} FIlE=hr.dmp OWNER=hr

Exporting of specific schema Object to disk:
- Login into the server which already has an Oracle client:

exp SYSTEM/password FIlE=exportdata.dmp TABLES=(hr.mine,scott.emp)

The above shows two user hr and Scott,we can do it for a single user.

exp {user}/{password} FILE=hr.dmp TABLES=(mine,family)

[Zecho]

Using Import Utility:

If your database has already exported and you need to import it then you need to follow these steps below:

imp SYSTEM/password FULL=y FIlE=my_dba.dmp

To import the mine and family from the hr schema:

imp SYSTEM/password FIlE=my_dba.dmp FROMUSER=hr TABLES=(mine,family)

To import the tables and need to change the owner:

imp SYSTEM/password FROMUSER=Ruser TOUSER=hr FILE=Ruser.dmp TABLES=(Dept,manager)