[Placeholder for all my data pump notes] [Example assumes expdp from dbstage schema u and impdp to dbprod schema u or y] * Traditional exp|imp to data pump expdp|impdp cmdline parameter conversion. Official list is (for import, export, respectively): http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref389 http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref181 Mine: exp|imp file -> expdp|impdp dumpfile, but only filename, not path exp|imp log -> expdp|impdp logfile, but only filename, not path exp compress -> no equivalent; some still mistakenly believe `exp compress=y' compresses dump file; note expdp compression CAN really compress, on metadata only in 10g and all data in 11g exp|imp rows -> expdp|impdp content exp owner -> expdp schemas exp consistent=y -> expdp flashback_{time|scn} (see the section starting with "exp consistent=y can be simulated") imp indexfile -> impdp content or sqlfile imp fromuser touser -> impdp remap_schema imp ignore -> impdp table_exists_action for tables (for tables only, unlike imp ignore for any object, which has no exact equivalent in impdp) imp statistics=none -> exclude=(table_statistics,index_statistics) (It's not impdp estimate!); it's very benefitial to exclude stats during impdp or you would contaminate shared pool with lots of literal SQLs like INSERT INTO "SYS"."IMPDP_STATS"... (Ref: Bug 7185178). If you forgot, purge those SQLs with grant select on sys.impdp_stats to dba and revoke it, or flush shared pool. imp resumable -> not needed; impdp does it by default exp|imp recordlength -> not supported by expdp|impdp exp|imp trace=y -> The undocumented SQL trace parameter accepts much more options in data pump than just y or n and can do more than SQL trace, but requires higher privilege in data pump. Level 1 SQL trace can use value e.g. 400301. See Note:286496.1. NLS_LANG for exp|imp -> environment variable not needed because expdp|impdp automatically matches client characterset with server exp|imp through a pipe, usually to compress dumpfile or to pass to ssh to send the dump across servers to avoid intermediate dump file -> no equivalent; expdp|impdp does not work with pipes or any sequential access devices (Ref: Note:463336.1). Expdp compression does real compression already. And dbms_datapump package allows for cross-server data pump export and import (Ref: http://www.freelists.org/post/oracle-l/Data-Pump-over-NETWORK-LINK,9) Note:286496.1 has a list of data pump parameters not shown in help=y: trace, metrics, keep_master. Notes 469439.1 and 813396.1 have access_method, which can take value direct_path or external_table (not external_tables). * impdp remap_schema and remap_tablespace can do multiple remapping: impdp remap_schema=(scott:adam,john:mary) or impdp remap_schema=scott:adam remap_schema=john:mary Remap_tablespace does such a thoroughly clean job that not only LOBs for which exp|imp can't deal with are relocated, but a partitioned table's default tablespace (xxx_part_tables.def_tablespace_name) is also remapped (so no need to 'alter table modify default attributes tablespace'). * If you `kill -9' an expdp process on UNIX/Linux, your terminal may be messed up. Try `stty sane' to make it work again. * expdp has a number of memory leak bugs that make it almost unusable under certain conditions. For example, Bug 5095025 is triggered when you list two many schemas, and Bug ??? when you have too many grants. In one case, my expdp process depleted system memory at a rate of 10MB per second. * What's the use of version parameter for expdp|impdp? Bugs such as 7413726 (POOR EXPDP PERFORMANCE WHEN DB COMPATIBLE=10.2.0.3 OR 10.2.0.4) can be worked around by setting this version parameter to a lower Oracle version than the database compatible parameter (e.g. version=10.2.0.1). If not, you would have to change the DB parameter and bounce, and you can't change DB compatible to one a major version down. Not sure what exactly is involved. 10g no longer has v$compatibility or v$compatseg. * Data pump impdp is only about twice as fast as traditional import in my test. expdp and export are both already fast. * When you expdp from a remote DB, you can create a DB link in the local DB. In dbprod, as the user that expdp logins as: create database link dbstage connect to u identified by password123 using 'dbstage'; Make sure export_dir directory object exists in dbprod. expdp system/xxxxxxx schemas=u network_link=dbstage directory=export_dir dumpfile=expdp_dbstage_u.dmp logfile=expdp_dbstage_u.log or (if the link is in sys schema) expdp \'/ as sysdba\' schemas=u network_link=dbstage directory=export_dir dumpfile=expdp_dbstage_u.dmp logfile=expdp_dbstage_u.log (public db link would work too) In impdp, you don't even have to create the user in the target database: impdp system/xxxxxxx directory=export_dir dumpfile=expdp_dbstage_u.dmp schemas=u logfile=db_user_imp.log Or you can remap the schema from u to y (but you won't know y's new password): impdp system/xxxxxxx directory=export_dir dumpfile=expdp_dbstage_u.dmp remap_schema=u:y logfile=db_user_imp.log One limitation when you use network_link is that tables with LONG and RAW columns will be silently skipped. Note it's SILENTLY skipped! Also be aware of Note:6630677.8 "IMPDP skips table exported just before table with LONG column using NETWORK_LINK" which is fixed in 11.0.6. * exp consistent=y can be simulated by expdp flashback_time="to_timestamp(current-time)". The syntax is so confusing especially on UNIX that some people just use a parameter file. If not, you can follow this syntax: On UNIX, it is (assuming you use sysdate): flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\" On Windows, remove all \: flashback_time="to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')" But due to Bug:6641104, sysdate cannot be specified once on the command line; otherwise it would be re-evaluated for each table during the expdp period, and there would be no one single read consistent point in time. Instead, hardcode the time with actual time, or just use flashback_scn. I hope this bug can be fixed soon so you can just use sysdate. [This bug seems to be fixed in 11.1.0.7. See Note:601739.1.] Here's my workaround (make sure there's no time delay between setting $DATE and expdp, it's almost the same as flashback_time=current-time or sysdate): DATE=`date "+%Y-%m-%d %H:%M:%S"` expdp flashback_time=\"to_timestamp\(\'$DATE\',\'yyyy-mm-dd hh24:mi:ss\'\)\" * However, expdp flashback_{time|scn} uses completely different mechanism than exp consistent=y. expdp uses flashback technology while exp runs set transaction read only at the beginning of the session, which is (in case you don't know!) silently ignored if the login user is SYS or any user with sysdba privilege. Besides, consistent=y is ignored when exporting partitions of a partitioned table. * The power of flashback_{time|scn} is what the name suggests. You can flashback tables to a previous point in time. Note the undocumented restriction that SQL "flashback table ... to {scn|timestamp}" throws ORA-604 and ORA-997 when the table has a LONG column. expdp flashback_{time|scn} works fine in this case (as long as you don't also specify network_link; see Note:553337.1). * Remap schema or tablespace may cause server free memory to drop sharply and the data pump session gets ORA-4030 if there're too many to remap. See Bugs 5464834, 5095025, 5292551, etc. * Parallel has problems. You may get errors like: ORA-31693: Table data object "APPUSER"."FILE_ENTRY_T" failed to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-11010: unable to open at least one dump file for load If you do use parallel, you must specify multiple datafiles in expdp and the same in impdp; otherwise only one of multiple worker threads would do real work. * More syntax examples (remove \ if on Windows or in parfile; on UNIX \ must prefix EVERY punctuation char!!): expdp yong/yong dumpfile=yong.dmp exclude=table:\"= \'TABLEINCAPITAL\'\" logfile=yong.log schemas=yong directory=yongtest rm yong.dmp #11g has reuse_dumpfiles=y; logfile can be overwritten expdp yong/yong dumpfile=yong.dmp exclude=table:\"like \'TEST%\'\" query=testpart:\"where x\>20\" logfile=yong.log schemas=yong * Traditional import has no way to bypass redo, even if the table is nologging and the exp was done with direct=y. impdp bypasses redo if the table is nologging. This can be easily checked by looking at v$datafile.unrecoverable_time for the tablepspace where the table resides. If it's the time any nologging operation was done, that operation had no redo (or more technically correct, minimum redo). * If impdp immediately quits and comes back to the shell prompt without any error or warning, check privilege of the user you run impdp as. If still unsure of the cause, create a logon trigger and check trace file in udump: create trigger theuser.sqltrace_trig after logon on theuser.schema begin execute immediate 'alter session set events ''10046 trace name context forever, level 4'''; end; / Appendix (may be deleted): flashback_time syntax reference (it took me a while to get it right): Note:204334.1 (Parameters FLASHBACK_SCN And FLASHBACK_TIME: Point In Time Export) exp system/manager file=exp.dmp flashback_time='"2004-06-19 13:24:26"' exp system/manager parfile=exp.par exp.par ------- file=exp.dmp flashback_time='2004-06-19 13:24:26' exp system/manager file=exp.dmp flashback_time=\"TO_TIMESTAMP('19-06-2004 13:24:26', 'DD-MM-YYYY HH24:MI:SS')\" exp system/manager file=exp.dmp flashback_time=\"TO_TIMESTAMP\(\'19-06-2004 13:24:26\', \'DD-MM-YYYY HH24:MI:SS\'\)\" When using the TO_TIMESTAMP function in a parameter file in Oracle10g, use double quotes. E.g.: FLASHBACK_TIME="TO_TIMESTAMP('19-06-2004 13:24:26', 'DD-MM-YYYY HH24:MI:SS')"