EXPORT - IMPORT

EXP/IMP IN ORACLE 10G

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

**********************************END**************************************