Differences between exp/imp and expdp/impdp

Differences between exp/imp and expdp/impdp

During normal slave database and database migration, it usually takes several hours to use exp when encountering a large database, which takes a lot of time. It takes much less time for Oracle10g to use expdp to export the database than exp, and the file size is much smaller.

1. To use expdp, you must first create a directory in the database and grant the read and write permissions to the corresponding users.

SQL> Differences Between 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 \'\) \ <\ '2017-04 \'\"

Common parameters:

SCOTT/TIGER: User/password (*)

DUMPFILE: exported file name (*)

DIRECTORY: location where exported files are stored (on the server side )(*)

CONTENT: CONTENT contained in the exported file (default: ALL, optional DATA_ONLY/METADATA_ONLY)

FILESIZE: Specifies the size of the exported file (in bytes ).

JOB_NAME: name used by the export process to facilitate tracking and query (optional)

LOGFILE: log File Name (default: export. log)

INCLUDE: the specified type is included in the export.

(Example: INCLUDE = TABLE_DATA,

INCLUDE = TABLE: "LIKE 'tab % '"

INCLUDE = TABLE: "not like 'tab % '"...)

EXCLUDE: Data Type excluded during export (for example, EXCLUDE = TABLE: EMP)

FULL: used for FULL Database Export (FULL with the same EXP; default value: N)

SCHEMA: exports all data in a SCHEMA.

TABLES: export by table (the method here is the same as EXP)

TABLESPACE: specify a TABLESPACE for export.

QUERY: when exporting data by table, use conditional statements to limit the export range (same as QUERY in exp)

TRANSPORT_FULL_CHECK:

TRANSPORT_TABLESPACES:

FLASHBACK_SCN:

FLASHBACK_TIME:

PARALLEL: PARALLEL Operation

PARFILE:

NETWORK_LINK:

When Data Pump Export (DPE) is running, press Control-C; To prevent messages from being displayed on the screen, but do not stop the Export process itself. Instead, it displays the DPE prompt (as shown below ). Processes are now considered in "interactive" Mode:

Export>

This method allows you to enter commands on the DPE job to query and control the current job.

3. You can use the PARALLEL parameter to export more than one thread to significantly accelerate the job. Each thread creates a separate dump file, so the parameter dumpfile should have the same project as the degree of parallelism. You can specify a wildcard as a file name instead of explicitly entering each 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, indicating that the file will be created as needed. The format will be expCASES_nn.dmp, where nn starts from 01 and then increases as needed.

In parallel mode, the status screen displays four working processes. (In the default mode, only one process is visible) All worker processes synchronously retrieve data and display their progress on the status screen.

It is important to separate the input/output channels for accessing data files and dumping directory file systems. Otherwise, the overhead related to Data Pump job maintenance may exceed the benefits of parallel threads and thus reduce performance. Parallelism is effective only when the number of tables exceeds the number of parallel values and the number of tables is large.

4. Differences between exp/imp and expdp/impdp:

(1) Import usera objects to userb. The difference is that 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 tablespace. When using exp/imp, You need to manually modify the tablespace where the table is located,

Such as alter table xxx move tablespace_new.

If impdp is used, use remap_tablespace = 'tabspace _ old': 'tablespace _ new'

(3) When you specify tables and use exp/imp, tables is used as tables = ('table1', 'table2', 'table3 ′).

The usage of expdp/impdp is tables = 'table1', 'table2', 'table3 ′

(4) Whether to export data rows

Exp (ROWS = Y exports data ROWS, ROWS = N does not export data ROWS)

Expdp content (ALL: Object + export data row, DATA_ONLY: only export object, METADATA_ONLY: only export data records)

(5) expdp is a new feature of [10 Gb] and can only be executed on the server. Exp/imp is common.

(6) There is a new feature in oracle11g. When the table has no data, no segment is allocated to save space. Therefore, exp cannot export empty tables. The solution is to use expdp. Of course, you can also set the deferred_segment_creation parameter or insert a row and then roll back, but this is very troublesome.