The difference between Exp/imp and EXPDP/IMPDP

Source: Internet
Author: User
In the normal preparation and database migration, when the large database encountered in the use of exp is often a few hours, a lot of time to spend. oracle10g you can use EXPDP to export the database later than the time that Exp spends, and the file is much smaller.        1. Use EXPDP to first create the directory in the database and give the appropriate users Read,write permissions. The difference between sql>create Dexp and EMPDP irectory dmpdir as '/u01/dmdir '; Sql>grant read,write on directory to test;        2. Common export methods $EXPDP scott/tiger dumpfile=scott.dmp directory=dmpdir schemas=test $EXPDP scott/ Tiger Dumpfile=tmp_dump.dmp Directory=dmpdir tables= (tmp_test:p1,tmp_test:p2) job_name=tmp_dump LOGFILE=tmp_ Dump.log $EXPDP scott/tiger dumpfile=full.dmp directory=dmpdir full=y job_name=full $EXPDP scott/timer dumpfile=tmp_ 200703.dmp directory=dmpdir tables=tmp query=\ "where to_char\ (create_time,\ ' yyyy-mm-dd\ '") \<\ ' 2007-04\ ' \ " Some commonly used parameter description: Scott/tiger: User/password (*) DumpFile: Exported file name (*) DIRECTORY: Export file location (on server side) (*) Content: The contents contained in the Export file (default: all, optional data _ONLY/METADATA_ONLY) FILESIZE: Specifies the export file size (in bytes). Job_name: The name used by this export process to facilitate tracing queries (optional) LOGFILE: Log file name (default: Export.log) include: The specified type when exporting (example: include=table_data, include=table: "Like ' tab% '" include=table: "Don't like ' tab% '" ...) EXCLUDE: Data types excluded when exporting (example: exclude=table:emp) Full: Used when the entire library was exported (with the exp, default = N) SCHEMA: Export all data in a schema: Export by Table ( The method here is the same as exp) Tablespace: Specifies a table space export. Query: When exporting by table, use conditional statements to qualify the export scope (query in exp) TRANSPORT_FULL_CHECK:TRANSPORT_TABLESPACES:FLASHBACK_SCN:FLASHBACK_TIME: PARALLEL: Parallel operation Parfile:network_link: When the Data Pump Export (DPE) runs, press control-c; it blocks the message from appearing on the screen, but does not stop the export process itself. Instead, it displays the DPE prompt (shown below). The process is now considered to be in interactive mode: Export> This method allows you to enter command queries and control the current job on this DPE job.
3. You can use more than one thread for export by using the PARALLEL parameter to significantly speed up the job. Each thread creates a separate dump file, so the parameter dumpfile should have as many items as the degree of parallelism. Instead of explicitly entering individual file names, you can specify wildcard characters as file names, such as: EXPDP ananda/abc123 tables=cases directory=dpdata1 dumpfile=expcases_%u.dmp Parallel =4 job_name=cases_export Note: The DumpFile parameter has a wildcard%u that indicates that the file will be created as needed, and the format will be expcases_nn.dmp, where nn starts at 01 and then increases as needed. In parallel mode, the status screen displays four worker processes. (in default mode, only one process is visible) all worker processes take out data synchronously and display their progress on the status screen. It is important to detach the input/output channels that access the data file and dump the directory file system. Otherwise, the overhead associated with maintaining the Data Pump job may outweigh the benefits of parallel threading and thus degrade performance. Parallelism is only valid if the number of tables is greater than the parallel value and the table is large.
 4.exp/imp and EXPDP/IMPDP difference: (1) The user UserA object to the user UserB, the use of the difference is Fromuser=usera touser=userb, remap_schema= ' UserA ': ' UserA '. For example: Imp system/passwd fromuser=usera touser=userb file=/oracle/exp.dmp log=/oracle/exp.log; IMPDP system/passwd directory=expdp dumpfile=expdp.dmp remap_schema= ' UserA ': ' UserB ' logfile=/oracle/exp.log; (2) Replace the table space, when using exp/imp, to change the table space, you need to manually to deal with, such as ALTER TABLE XXX move tablespace_new and so on. Use IMPDP as long as remap_tablespace= ' tabspace_old ': ' Tablespace_new ' (3) when specifying some tables, when using EXP/IMP, tables are used in tables= (' table1′, ' table2′, ' table3′. The use of EXPDP/IMPDP is tables= ' table1′, ' table2′, ' table3′ (4) Whether you want to export data row exp (rows=y export rows of data, rows=n do not export rows of data) EXPDP content (all: Object + export rows of data, Data_only: Only Export objects, Metadata_only: records that only export data (5) EXPDP is a new feature of [10g] and can only be performed on the server. And the Exp/imp is universal. (6) ORACLE11G has a new feature, when the table has no data, do not allocate segment, to save space, so exp can not guide the empty table. The solution is to use EXPDP, of course, you can set deferred_segment_creation parameters or insert a row, and then rollback, but this is very troublesome.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.