Discussion about Import/Export Utility in Oracle Database?

Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files.

A simple automated script to export full database

SET ORACLE_SID=ORCL3

Column instnc new_value v_inst noprint

column instdate new_value v_instdate noprint

SELECT TO_CHAR(sysdate,'DDMMYYHH24') instdate FROM dual;

host exp system/oracle@orcl3 full=y consistent=y file=D:\BACKUP\dump&&v_instdate..dmp log=D:\BACKUP\dump&&v_instdate..log

exit

Which are the Import/Export utility modes?

Full export/export:

The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use the full export parameter for a full export.

Tablespace:

Use the tablespaces export parameter for a tablespace export.

User:

This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the FROMUSER import parameter for a user (owner) export-import. 

Table:

Specific tables (or partitions) can be Exported/Imported with table export mode. Use the tables export parameter for a table Export/ Import mode. 

For more details example follow the other post: Using Import/Export

                              *** Insert Multiple Table From A Single Query in DBA  ****

Is it possible to exp/ imp to multiple files?

Yes, is possible. Here is an example:  

exp SCOTT/TIGER FILE=C:\backup\File1.dmp,C:\backup\File2.dmp LOG=C:\backup\scott.log

Export and Import Schema in the same and different database

EXP SYSTEM/SYSMAN@mujazorc.world OWNER=KAFAFIN FILE=H:\dump\kafafin_dump.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y LOG= H:\dump\kafafin.DMP.LOG

The above two commands will export the 'kafafin' schema from mujazorc database and import (restore) into the 'awaed' schema located on the same database.

But in the case if you want to import into new schema of new tablespace you need to quota 0 on old_tablespace and quota unlimited on new_tablespace before importing.

For Example: if you have schema KAFAFIN with tablespace KAFA_FIN_DBF and need to import into new schema AWAED of new tablespace AWAED then before import you must set quota 0 on old_tablespace and quota unlimited on new tablespace.

alter user AWAED quota 0 on KAFA_FIN_DBF quota unlimited on AWAED;

revoke unlimited tablespace from AWAED;

IMP SYSTEM/sysman@mujazorc.world FILE=D:\backup\kafafin_dump.DMP FROMUSER=KAFAFIN TOUSER=AWAED LOG=D:\backup\kafafin.DMP.LOG

after import you will need to set it again ALTER USER AWAED DEFAULT TABLESPACE AWAED

QUOTA UNLIMITED ON AWAED;

While in case if you want to import different schema on different database there is no need to set the quota 0 for old_tablespace

create tablespace AWAED datafile

'D:\ORACLE\ORA92\MUJAZORC\AWAED.DBF' size 20480m autoextend on;

create user AWAED IDENTIFIED BY AWAED

TEMPORARY TABLESPACE TEMP

DEFAULT TABLESPACE AWAED

QUOTA UNLIMITED ON AWAED;

GRANT CONNECT, RESOURCE TO AWAED;

EXP SYSTEM/SYSMAN@sadhan OWNER=ORAFIN FILE=F:\Dump\orafin_16022015.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= F:\Dump\orafin_16022015.log

IMP SYSTEM/sysman@MUJAZORC FILE=F:\Dump\orafin_16022015.DMP FROMUSER=ORAFIN TOUSER=AWAED LOG=F:\Dump\orafin_16022015.DMP.log

The above two commands will export the 'ORAFIN' schema from SADHAN database and import (restore) into the different schema (awaed) on the mujazorc database.