Loading...

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
oracle-export-import-utility

Which are the Import/Export 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

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.
VirtualNuggets 1957478857065512731

Post a Comment

emo-but-icon

Home item

Popular Posts

Labels

Random Posts

Flickr Photo