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
