{{parent page="Oracle"}}

===DataPump===
From 10g onwards, Oracle adds the datapump facilities to perform export and import.

%%
expdp dba/2008db dumpfile=nco20080320.dmp directory=dump_dir schemas=nco
%%

Note that directory is actually referring to directory objets in oracle. So you will find the following command handy:

%%
create or replace directory dump_dir as '/home/oracle/dumps';
%%

=== Export ===

%%
exp user/passwd indexes=Y constraints=Y rows=Y feedback=2000 file=tablespace_name.dat log=tablespace_name.log

exp user/passwd indexes=Y constraints=Y rows=Y feedback=2000 file=tablespace_name.dat
file=export1.dat, export2.dat filesize=2048m log=tablespace_name.log
%%

=== Import ===
Drop everything first if target tablespace is not empty. Otherwise, records will be appended.
%%
#As tablespace owner
select 'drop table ' || table_name || ' cascade constraints;' from user_tables;
select 'drop index ' || index_name || ';' from user_indexes;
select 'drop view ' || view_name || ';' from user_views;
%%

Import dump file & rebuild index on INDEX_TABLESPACE.
%%
imp user/passwd indexes=Y constraints=Y rows=Y file=dumpfile.dat log=tablespace_name.implog
imp sys/xxx fromuser=xxowner touser=xxowner tables=(table1, table2) indexes=Y constraints=Y rows=Y feedback=2000 file=export.dat log=tablesspace.implog

select 'alter index ' || index_name || ' rebuild tablespace INDEX_TABLESAPCE;' from user_indexes;
%%

Constraint conflicts
may occur during import, the following command can be used to disable constraint
%%
alter table table_name disable constraint constraint_name
%%

==Full export/import==
%%
exp system/blah full=Y consistent=y feedback=2000 file=165dump.dat log=165dump.log
imp system/blah FULL=y FIlE=165dump.log log=165dump.implog
%%

===Check datapump status===
%%
SYS@xxxxx1> select * from dba_datapump_jobs

SYS@xxxxx1> select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS where username = 'SYSTEM';

USERNAME
------------------------------
OPNAME
----------------------------------------------------------------
TARGET_DESC SOFAR TOTALWORK
-------------------------------- ---------- ----------
MESSAGE
--------------------------------------------------------------------------------
SYSTEM
SYS_EXPORT_FULL_01
EXPORT 0 142
SYS_EXPORT_FULL_01: EXPORT : 0 out of 142 MB done
%%