In the normal storage and database migration, when encountering a large database when using exp often takes a few hours, time consuming. oracle10g later can use EXPDP to export the database to spend much less time than Exp spent, and the file is much smaller. 1. Use EXPDP to first create the directory in the database and give the appropriate user 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 :p 2) 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\ ' \ "Common parameters Description: Scott/tiger: User/password (*) DumpFile: Exported file name (*) DIRECTORY: Export file location (on server side) (*) Content: What is contained in the export file (default: All, optional data_only/metadata_only) FILESIZE: Specifies the size of the export file in bytes. Job_name: The name used by this export process to facilitate tracking of queries (optional) LOGFILE: Log file name (default: Export.log) include: Export with the specified type (example: include=table_data,include= TABLE: "Like ' tab% ' "include=table:" Not like ' tab% ' "...) EXCLUDE: Data type excluded from export (example: exclude=table:emp) Full: Used for whole library export (same as EXP, default N) SCHEMA: Export all data under a schema tables: 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 range (same as query in exp) TRANSPORT_FULL_CHECK:TRANSPORT_TABLESPACES:FLASHBACK_SCN:FLASHBACK_TIME: PARALLEL: Parallel operation Parfile:network_link: When the Data Pump export (DPE) is run, press control-c; it will prevent the message from appearing on the screen but not stopping 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 the 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 projects as the degree of parallelism. Instead of explicitly entering individual file names, you can specify a wildcard character as the file name, for example: 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, which indicates that the file will be created as needed, in the format of Expcases_nn.dmp, where nn starts from 01 and then increases as needed. In parallel mode, the status screen displays four worker processes. (In the default mode, only one process is visible) all the worker processes synchronize the data out and display their progress on the status screen. It is important to separate the input/output channels that access the data files and dump the directory file system. Otherwise, the overhead associated with maintaining the Data Pump job might outweigh the benefits of parallel threading and therefore degrade performance. Parallel mode is only valid if the number of tables is larger than the parallel value and the table is large. The difference between 4.exp/imp and EXPDP/IMPDP: (1) UserA the userThe object is directed to the user UserB, the usage 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, with Exp/imp, To change the table space in which the table is located, you need to handle it manually, such as ALTER TABLE XXX move tablespace_new. Use IMPDP as long as the remap_tablespace= ' tabspace_old ': ' Tablespace_new ' (3) When you specify some tables, Exp/imp uses tables when you are specifying them (' tables=, ' table2′, ' table3′). The usage of EXPDP/IMPDP is tables= ' table1′, ' table2′, ' table3′ (4) whether to export data rows exp (rows=y Export data rows, rows=n do not export data rows) EXPDP content (all: Object + Export Data rows, Data_only: Export only objects, Metadata_only: Only records that 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) There is a new feature in oracle11g, when there is no data in the table, the segment is not allocated to save space, so exp cannot lead to empty table. The solution is to use EXPDP, of course, you can set the Deferred_segment_creation parameter or insert a row, and then rollback, but this is cumbersome.
Exp/imp differs from EXPDP/IMPDP
