Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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/[email protected] full=y consistent=y file=D:\BACKUP\dump&&v_instdate..dmp log=D:\BACKUP\dump&&v_instdate..log
exit

Which are the Import/Export modes?

Full export/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

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/[email protected] 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/[email protected] 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/[email protected] OWNER=ORAFIN FILE=F:\Dump\orafin_16022015.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= F:\Dump\orafin_16022015.log

IMP SYSTEM/[email protected] 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.


This post first appeared on Oracle DBA Online Training, please read the originial post: here

Share the post

Discussion about Import/Export Utility in Oracle Database?

×

Subscribe to Oracle Dba Online Training

Get updates delivered right to your inbox!

Thank you for your subscription

×