* 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 [Update 2011-06] Beginning with 11gR2, data pump has legacy mode for the parameters: http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_legacy.htm My parameter conversion: 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, and it may compress better than gzip (Ref: http://www.oracle.com/technology/oramag/oracle/09-jul/o49data.html?_template=/ocom/print) 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 (in its presense, schemas parameter, if used, points to the original or source schema, not the remapped one) imp ignore -> impdp table_exists_action for tables (for tables only, unlike imp ignore for any object, which has no exact equivalent in impdp) exp|imp statistics=none -> expdp|impdp exclude=statistics (documentation is wrong in saying "A parameter comparable to STATISTICS is not needed. Statistics are always saved for tables."); before 11gR2, it's very benefitial to exclude stats or you would contaminate shared pool with lots of literal SQLs like INSERT INTO "SYS"."IMPDP_STATS"... during impdp (Bug 7185178, fixed in 11gR2). If you forgot, right after impdp, purge those SQLs with grant select on sys.impdp_stats to dba and revoke it, or flush shared pool if you don't mind the impact. Note: it seems impdp exclude=(table_)statistics is available in 10g but not 11g. expdp is fine. imp resumable -> not needed; impdp does it by default, and it shows on screen "ORA-39171: Job is experiencing a resumable wait" except during the final index creation phase. imp resumable_timeout -> none. If 2 hour default is not suitable, after impdp starts, exec dbms_resumable.set_session_timeout to set it (which also works around Bug 6140308 that DB level setting is ignored, and Bug 2968139 that parallel sessions won't take the alter session setting). 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 in exp|imp, and can do more than SQL trace in data pump, 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 uses database nls_characterset parameter on the servers. But see Note:227332.1 Sec.7. 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) (where parentheses are optional) 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. * 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. * When you expdp from a remote DB, you can create a DB link in the local DB. In dbprod, as the user that expdp logs into: 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 or it's a public db link expdp \'/ as sysdba\' schemas=u network_link=dbstage directory=export_dir dumpfile=expdp_dbstage_u.dmp logfile=expdp_dbstage_u.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. * Apart from the bug stated below, exp consistent=y can be simulated by expdp flashback_time="to_timestamp()". 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 in 10g, due to Bug:6641104, sysdate cannot be specified only once on the command line for multiple tables; 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, you must hardcode the time with actual time, or just use flashback_scn. So use sysdate only if you export one table. Here's my workaround. Use one single line with two commands separated by semicolon to make sure there's minimum time gap. It's almost the same as flashback_time=current-time or sysdate): DATE=`date "+%Y-%m-%d %H:%M:%S"`; expdp username flashback_time=\"to_timestamp\(\'$DATE\',\'yyyy-mm-dd hh24:mi:ss\'\)\" I think this bug is fixed in 11.1.0.7. See Note:601739.1. * 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 that in spite of 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 with LONG (as long as you don't also specify network_link; see Note:553337.1). * Old 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 too 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. * 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 most punctuation characters): 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 DB is in archivelog mode). If it's the time any nologging operation was done, that operation had no redo (or rather, minimum redo). (This feature is probably added as a result of enhancement in Bug 4568451, except the create-table-as-nologging part doesn't seem to be implemented, even in 11.2.0.1.) * 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; / * In many cases impdp (imp as well) has bottleneck at creating indexes. You may be able to speed up with a logon trigger changing some parameters: grant alter session to impuser; --if not have it create trigger impuser.tmptrig after logon on impuser.schema begin execute immediate 'alter session set workarea_size_policy=manual'; execute immediate 'alter session set sort_area_size=300000000'; execute immediate 'alter session set db_file_multiblock_read_count=128'; end; / Remember to drop the trigger after impdp is done (thanks to Kumar Madduri's correction). For imp, you can drop it sooner, like right after imp starts. * Minimum privilege to dump your own data Exp can export your data as long as you can connect to your schema. Expdp requires read and write privilege on the directory object, create table and create session privilege (or connect role), and some quota on his default tablespace (at least 512k if db_block_size is 8k): create user u identified by ... quota 512k on ...; grant create session, create table to u; grant read on directory ... to u; grant write on directory ... to u expdp u schemas=u directory=... dumpfile=... * If you get errors absolutely irrelevant such as LRM-00112: multiple values not allowed for parameter 'directory' when you did not, or UDE-00014: invalid value for parameter, 'attach'. when you didn't even specify attach, make sure the parameter is followed by equal sign and value. * If you get error ORA-39065: unexpected master process exception in ... ORA-39079: unable to enqueue message DG,KUPC$... make sure aq_tm_processes is not explicitly set to 0: select value, isdefault from gv$parameter where name='aq_tm_processes'; and correct it if so: alter system set aq_tm_processes=1 scope=memory; (more info about this param at ./aq_tm_processes.txt) * Parallel=y is reported to be not working to load package bodies http://www.freelists.org/post/oracle-l/11201-64bit-OEL-54-Performance-problem-with-impdp or to load a table with LOB http://www.itpub.net/thread-1309692-1-1.html * Parallel import on RAC may require the dump file to be readable from all nodes. * Not importing all rows: ". . imported ... out of rows" See Impdp Imports 0 Rows if Expdp has not completed (Doc ID 788571.1) Bug 7703796: WRONG RESULT IN 'IMPORTED # ROWS' OF IMPDP OUTPUT WHEN DOING IMPDP VIA DB-LINK Bug 9470999: IMPDP REPORTS INCORRECT AMOUNT OF ROWS IMPORTED IN PARALLEL MODE http://manchev.org/index.php/database/104-datapump-characterset-cl8mswin1251-al32utf8 * Huge undo tablespace used by import Impdp no longer supports commit=y. According to http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_legacy.htm#sthref361 COMMIT This parameter is ignored. Data Pump Import automatically performs a commit after each table is processed. If you have a huge table, the only thing you can do to reduce the undo (v$transaction.used_ublk) is see if all the indexes on the table can be not imported with exclude=index. If the table needs precreated, import with table_exists_action=(append|truncate) exclude=index For some reason, the truncate option may not work well (to fully deallocate space). Manually truncate the tables first, preferably with drop all storage clause. * What is my impdp doing? Sometimes it seems to not move, get stuck, "locked up", "deadlocked" (as a layman database user calls it). The trace parameter doesn't help much or the trace file is too verbose to read. Unfortunately all the data pump related sessions have either idle wait events or a fairly generic "Datapump dump file I/O". Worse, their v$session.sql_id or even prev_sql_id is empty. Their v$open_cursor has dozens of SQLs but you're not sure which one you should focus on. Their 'consistent gets' statistic in v$sesstat keeps going up. But we need to find the SQL causing it. One way to achieve that is looking at v$sql(area) where users_executing>0, and a very recent last_active_time, combined with select * from v$session_longops where sofar!=totalwork. If the buffer_gets of the SQL is high and is still increasing rapidly, that may be it. That's how I identified the case affected by a bug documented in this note DataPump Import (IMPDP) Seems To Hang On Processing Object Type SCHEMA_EXPORT/TABLE/TABLE_DATA [ID 789700.1] * Possible undocumented parameters for data pump (* means exp also has it; only checked 10.2.0.1 expdp and exp): ip_address (IP Address for PLSQL debugger) keep_master (Retain job table upon completion) log_entry (maybe insert a msg to log as dbms_datapump.log_entry does) metrics* (Enable/disable object metrics reporting) mp_enable (Enable/disable multi-processing for current session) package_load (Specify how to load PL/SQL objects) parallel_threshold (Degree of DML Parallelism) silent* (display [banner] information, default is NONE) trace* (enable sql_trace and timed_stat, default is 0) tts_closure_check (Enable/disable transportable containment check: def is Y) In addition, exp has point_in_time_recover (point-in-time recover option: default is N) impparfile (file to create as paramfile for IMP for transportable tablespaces) file_format (format of export file names) 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')"