NAVEEN

Tuesday, December 29, 2009

EXPORT - IMPORT

EXP/IMP IN ORACLE 10G
=====================


Ref: http://www.oracleracexpert.com/2009/08/oracle-data-pump-exportimport.html
http://husnusensoy.wordpress.com/2008/07/12/migrating-data-using-transportable-tablespacetts/

first create a directory and grant access to it


SQL>create directory as '/ora10gdata/export'
SQL>grant read,write on directory to eg scott or hr or system



schema(user) lever
-----------------
expdp scott/tiger123 directory=johnson dumpfile=johnsonexp.dmp logfile=expjohnson.log schemas=scott
impdp scott/tiger123 directory=johnson dumpfile=johnsonexp.dmp logfile=impjohnson.log

table level
----------
expdp scott/tiger123 directory=johnson dumpfile=tableexp.dmp logfile=exptable.log tables=EMP,DEPT
impdp scott/tiger123 directory=johnson dumpfile=tableexp.dmp tables=EMP,DEPT logfile=imptable.log

full database
-------------
expdp system/sys directory=johnson dumpfile=nagiosdb.dmp logfile=expnagios.log full=y

impdp system/sys directory=johnson dumpfile=nagiosdb.dmp logfile=impnagios.log full=y

TABLESPACE LEVEL
----------------
expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log



TRANSPORTABLE TABLESPACES:
----------------------------

SQL> CREATE TABLESPACE dumil LOGGING
DATAFILE 'C:\oracle\product\10.1.0\oradata\test\dumil.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

SQL> begin
sys.dbms_tts.transport_set_check('dumil', TRUE);
end;
/


SQL> ALTER TABLESPACE dumil READ ONLY;


C:\expdp system/test directory=expbkp TRANSPORT_TABLESPACES=dumil dumpfile=dumil_migration.dmp logfile=dumil_miglog.log

Note: In 9i [ exp system/test tablespaces=dumil transport_tablespace=y file=dumil_migration.dmp log=dumil_miglog.log ]



create directory impbkp as '/ora10gdata/impbkp'

grant read,write on directory impbkp to system;

impdp system/sys directory=impbkp transport_datafiles='/ora10gdata/gsvdb/gsvdb/DUMIL.DBF' dumpfile=dumil_migration.dmp logfile=impdplog.log

1 comment:

  1. find even more here:
    http://chandu208.blogspot.com/2011/04/oracle-data-pump.html

    ReplyDelete