Shulou(Shulou.com)05/31 Report--

This article is about how the ORACLE data pump imports and exports data. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Summary

In the usual preparation of libraries and database migration, when you encounter a large database, it often takes several hours to use exp, which takes a lot of time. Oracle10g can later use expdp to export the database in much less time than exp takes, and the files are much smaller.

II. The difference between exp/imp and expdp/impdp

(1) Import the object of user usera to user userb. The usage difference lies in fromuser=usera touser=userb and 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) change the tablespace. If you want to change the tablespace when using exp/imp, you need to deal with it manually.

Operations such as alter table xxx move tablespace_new.

To use impdp, just use remap_tablespace='tabspace_old':'tablespace_new'.

(3) when using exp/imp when specifying some tables, the use of tables is 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 + exported data rows, DATA_ONLY: export only objects, METADATA_ONLY: export only data records)

(5) expdp is a new feature of [10g] and can only be executed on the server. And exp/imp is universal. 11g/12c can be run on the client side and saved on the server.

(6) there is a new feature in oracle11g, when the table has no data, segment is not allocated to save space, so exp cannot export an empty table. The solution is to use expdp, of course, you can also set the deferred_segment_creation parameter or insert line, and then rollback, but this is troublesome.

3. Export data

Step1. Create a logical directory, which does not create a real directory on the operating system, but is best created by an administrator such as system.

Create directory data_pump name as' data_pump directory'

Step2. Check the administrator directory (also check to see if the operating system exists, because Oracle doesn't care if the directory exists, and if it doesn't exist, an error occurs)

Select directory_name,directory_path from dba_directories where DIRECTORY_NAME='data_pump name'

Step3. Give users permission to operate in a specified directory, preferably by administrators such as system.

Grant read,write on directory data_pump directory to user

(1)。 Guide the entire database

C:\ Users\ Administrator > expdp system/xxxxxx@clonepdb_plug dumpfile=ful.dmp

DIRECTORY=systemdmp logfile=full.log full=y

Export: Release 12.2.0.1.0-Production on January 22 16:12:01 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Produc

Tion

FLASHBACK is automatically enabled to maintain database integrity.

Start "SYSTEM". "SYS_EXPORT_FULL_01": system/****@clonepdb_plug dumpfile=ful.

Dmp DIRECTORY=systemdmp logfile=full.log full=y

Dealing with object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Dealing with object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Dealing with object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Dealing with object type DATABASE_EXPORT/STATISTICS/MARKER

Dealing with object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Dealing with object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Dealing with object type DATABASE_EXPORT/TABLESPACE

Dealing with object type DATABASE_EXPORT/PROFILE

Dealing with object type DATABASE_EXPORT/SYS_USER/USER

Dealing with object type DATABASE_EXPORT/SCHEMA/USER

Dealing with object type DATABASE_EXPORT/ROLE

Dealing with object type DATABASE_EXPORT/RADM_FPTM

Dealing with object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Dealing with object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Dealing with object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Dealing with object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Dealing with object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT

Dealing with object type DATABASE_EXPORT/RESOURCE_COST

Dealing with object type DATABASE_EXPORT/SCHEMA/DB_LINK

Dealing with object type DATABASE_EXPORT/TRUSTED_DB_LINK

Dealing with object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Dealing with object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Dealing with object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

Dealing with object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM

Dealing with object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Dealing with object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTE

M

Dealing with object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Dealing with object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE

Dealing with object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER

Dealing with object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE

Dealing with object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE

Dealing with object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Dealing with object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Dealing with object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER

Dealing with object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Dealing with object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE

Dealing with object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

. . Exported "SYS". "KU$_USER_MAPPING_VIEW" 6.125 KB 40 lines

. . Exported "SYSTEM". "REDO_DB" 25.58 KB 1 line

. . Exported "ORDDATA". "ORDDCM_DOCS" 252.9 KB 9 lines

. . Exported "WMSYS". "WM$WORKSPACES_TABLE$" 12.10 KB 1 line

. . Exported "WMSYS". "WM$HINT_TABLE$" 9.984 KB 97 lines

. . Exported "LBACSYS". "OLS$INSTALLATIONS" 6.953 KB 2 lines

. . Exported "WMSYS". "WM$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 lines

. . Exported "SYS". "DAM_CONFIG_PARAM$" 6.523 KB 14 lines

. . Exported "SYS". "TSDP_SUBPOL$" 6.320 KB 1 line

. . Exported "WMSYS". "WM$NEXTVER_TABLE$" 6.375 KB 1 line

. . Exported "LBACSYS". "OLS$PROPS" 6.234 KB 5 lines

. . Exported "WMSYS". "WM$ENV_VARS$" 6.015 KB 3 lines

. . Exported "SYS". "TSDP_PARAMETER$" 5.945 KB 1 line

. . Exported "SYS". "TSDP_POLICY$" 5.914 KB 1 line

. . Exported "WMSYS". "WM$VERSION_HIERARCHY_TABLE$" 5.976 KB 1 line

. . Exported "WMSYS". "WM$EVENTS_INFO$" 5.812 KB 12 lines

. . Exported "LBACSYS". "OLS$AUDIT_ACTIONS" 5.75 KB 8 lines

. . Exported "LBACSYS". "OLS$DIP_EVENTS" 5.539 KB 2 lines

. . The line "LBACSYS". "OLS$AUDIT" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$COMPARTMENTS" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$DIP_DEBUG" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$GROUPS" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$LAB" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$LEVELS" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$POL" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$POLICY_ADMIN" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$POLS" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$POLT" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$PROFILE" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$PROFILES" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$PROG" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$SESSINFO" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$USER" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$USER_COMPARTMENTS" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$USER_GROUPS" 0 KB 0 is exported

. . The line "LBACSYS". "OLS$USER_LEVELS" 0 KB 0 is exported

. . The line "SYS". "AUD$" 0 KB 0 is exported

. . The line "SYS". "DAM_CLEANUP_EVENTS$" 0 KB 0 is exported

. . The line "SYS". "DAM_CLEANUP_JOBS$" 0 KB 0 is exported

. . The line "SYS". "TSDP_ASSOCIATION$" 0 KB 0 is exported

. . The line "SYS". "TSDP_CONDITION$" 0 KB 0 is exported

. . The line "SYS". "TSDP_FEATURE_POLICY$" 0 KB 0 is exported

. . The line "SYS". "TSDP_PROTECTION$" 0 KB 0 is exported

. . The line "SYS". "TSDP_SENSITIVE_DATA$" 0 KB 0 is exported

. . The line "SYS". "TSDP_SENSITIVE_TYPE$" 0 KB 0 is exported

. . The line "SYS". "TSDP_SOURCE$" 0 KB 0 is exported

. . The line "SYSTEM". "REDO_LOG" 0 KB 0 is exported

. . The line "WMSYS". "WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 is exported

. . The line "WMSYS". "WM$CONSTRAINTS_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$CONS_COLUMNS$" 0 KB 0 is exported

. . The line "WMSYS". "WM$LOCKROWS_INFO$" 0 KB 0 is exported

. . The line "WMSYS". "WM$MODIFIED_TABLES$" 0 KB 0 is exported

. . The line "WMSYS". "WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$NESTED_COLUMNS_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$RIC_LOCKING_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$RIC_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$RIC_TRIGGERS_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 is exported

. . The line "WMSYS". "WM$UDTRIG_INFO$" 0 KB 0 is exported

. . The line "WMSYS". "WM$VERSION_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$VT_ERRORS_TABLE$" 0 KB 0 is exported

. . The line "WMSYS". "WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 is exported

. . Exported "MDSYS". "RDF_PARAM$" 6.507 KB 3 lines

. . Exported "SYS". "AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 lines

. . The line "SYS". "DBA_SENSITIVE_DATA" 0 KB 0 is exported

. . The line "SYS". "DBA_TSDP_POLICY_PROTECTION" 0 KB 0 is exported

. . The line "SYS". "FGA_LOG$FOR_EXPORT" 0 KB 0 is exported

. . The line "SYS". "NACL$_ACE_EXP" 0 KB 0 is exported

. . Exported "SYS". "NACL$_HOST_EXP" 6.914 KB 1 line

. . The line "SYS". "NACL$_WALLET_EXP" 0 KB 0 is exported

. . The line "SYS". "SQL$TEXT_DATAPUMP" 0 KB 0 is exported

. . The line "SYS". "SQL$_DATAPUMP" 0 KB 0 is exported

. . The line "SYS". "SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 is exported

. . The line "SYS". "SQLOBJ$DATA_DATAPUMP" 0 KB 0 is exported

. . The line "SYS". "SQLOBJ$PLAN_DATAPUMP" 0 KB 0 is exported

. . The line "SYS". "SQLOBJ$_DATAPUMP" 0 KB 0 is exported

. . The line "SYSTEM". "SCHEDULER_JOB_ARGS" 0 KB 0 is exported

. . Exported "SYSTEM". "SCHEDULER_PROGRAM_ARGS" 9.515 KB 12 lines

. . Exported "WMSYS". "WM$EXP_MAP" 7.710 KB 3 lines

. . The line "WMSYS". "WM$METADATA_MAP" 0 KB 0 is exported

. . Exported "SCOTT". "EMP" 8.695 KB 12 lines

. . Exported "SCOTT". "DEPT" 6 KB 3 lines

. . Exported "SCOTT". "SALGRADE" 5.953 KB 5 lines

. . Exported "SCOTT". "TEST_01" 5.187 KB 12 lines

. . The line "SCOTT". "BONUS" 0 KB 0 is exported

. . The line "SCOTT". "TEST1" 0 KB 0 is exported

. . The line "SCOTT". "TEST2" 0 KB 0 is exported

The primary table "SYSTEM". "SYS_EXPORT_FULL_01" has been successfully loaded / unloaded

The dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

C:\ ORACLEBACK\ DATAPUMP\ FUL.DMP

Assignment "SYSTEM". "SYS_EXPORT_FULL_01" on Monday January 22 16:14:04 2018 elapsed

Completed successfully at 02:02

Verification

Sys@clonepdb_plugPDB > drop user scott cascade

User dropped.

Elapsed: 00:00:02.85

Sys@clonepdb_plugPDB > select from scott.emp

Select from scott.emp

*

ERROR at line 1:

ORA-00942: table or view does not exist

Elapsed: 00:00:00.01

C:\ Users\ Administrator > impdp system/xxxxx@clonepdb_plug dumpfile=systemd

Mp:ful.dmp logfile=scott_imp.log schemas=scott

Import: Release 12.2.0.1.0-Production on January 22 16:30:23 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Produc

Tion

The primary table "SYSTEM". "SYS_IMPORT_SCHEMA_01" has been successfully loaded / unloaded

Start "SYSTEM". "SYS_IMPORT_SCHEMA_01": system/****@clonepdb_plug dumpfile=sy

Stemdmp:ful.dmp logfile=scott_imp.log schemas=scott

Dealing with object type DATABASE_EXPORT/SCHEMA/USER

Dealing with object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Dealing with object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Dealing with object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Dealing with object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Dealing with object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . Imported "SCOTT". "EMP" 8.695 KB 12 lines

. . Imported "SCOTT". "DEPT" 6 KB 3 lines

. . Imported "SCOTT". "SALGRADE" 5.953 KB 5 lines

. . Imported "SCOTT". "TEST_01" 5.187 KB 12 lines

. . Imported "SCOTT". "BONUS" 0 KB 0 line

. . Imported "SCOTT". "TEST1" 0 KB 0 line

. . Imported "SCOTT". "TEST2" 0 KB 0 line

Dealing with object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Dealing with object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Dealing with object type DATABASE_EXPORT/STATISTICS/MARKER

ORA-39082: object type VIEW: "SCOTT". "V_TEST" has been created with compilation warnings

ORA-39082: object type VIEW: "SCOTT". "V_TEST1" has been created with compilation warnings

The assignment "SYSTEM". "SYS_IMPORT_SCHEMA_01" has been completed with 2 errors (Monday, January 22

16:30:43 2018 elapsed 0 00:00:18)

Sys@clonepdb_plugPDB > select * from scott.emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7566 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 78391981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7900 JAMES CLERK 7698 2450 12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

12 rows selected.

Elapsed: 00:00:00.02

(2)。 By user guide

C:\ Users\ Administrator > expdp system/xxxx@clonepdb_plug dumpfile=systemd

Mp:scott.dmp logfile=scott_imp.log schemas=scott REUSE_DUMPFILES=y

(3)。 Parallel process parallel

C:\ Users\ Administrator > expdp system/xxxx@clonepdb_plug dumpfile=systemd

Mp:ful.dmp logfile=scott_imp.log REUSE_DUMPFILES=y parallel=2 full=y

(4)。 Guide by table name

C:\ Users\ Administrator > expdp system/zncg3008ZNCG@clonepdb_plug dumpfile=systemd

Mp:expdp.dmp logfile=expdp.log REUSE_DUMPFILES=y tables=scott.emp

(5)。 Guide by query condition

C:\ Users\ Administrator > expdp scott/tiger@clonepdb_plug dumpfile=systemdmp:expdp

.dmp logfile=expdp.log REUSE_DUMPFILES=y tables=emp query='WHERE deptno=20'

(6)。 According to the table space guide

Expdp system/xxxx@clonepdb_plug DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example; 2. Parameter description-Export expdb

(1)。 CONTENT: this option is used to specify what to export. The default value is ALL

CONTENT= {ALL | DATA_ONLY | METADATA_ONLY} when CONTENT is set to ALL, the object definition and all its data are exported. When DATA_ONLY, only object data is exported, and when METADATA_ONLY, only object definitions are exported

(2)。 DIRECTORY: specify the directory where the dump and log files are located: DIRECTORY=directory_object

(3)。 EXCLUDE: this option is used to specify that the object type or related objects to be excluded are released when the operation is performed

EXCLUDE=object_type [: name_clause] [, … .] Object_type is used to specify the types of objects to be excluded, and name_clause is used to specify specific objects to be excluded. Exclude and INCLUDE cannot use Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW at the same time

(4)。 INCLUDE: export with the specified type

(example: INCLUDE=TABLE_DATA, INCLUDE=TABLE: "LIKE 'TAB%'" INCLUDE=TABLE: "NOT LIKE' TAB%'" …) EXCLUDE: data types excluded on export (example: EXCLUDE=TABLE:EMP)

(5)。 FILESIZE: specifies the maximum size of the exported file, which defaults to 0 (indicates that there is no limit to the file size) (in bytes).

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

(7)。 FLASHBACK_SCN: specifies the export of table data at a specific SCN time

FLASHBACK_SCN=scn_value:Scn_value is used to identify SCN values. Flash _ SCN and FLASHBACK_TIME cannot use Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_SCN=358523 at the same time

(8)。 FLASHBACK_TIME: specifies to export table data at a specific point in time: FLASHBACK_TIME= "TO_TIMESTAMP (time_value)"

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME= "TO_TIMESTAMP ('25-08-2004 14-14-15-35-00-14-14-15-15-14-15-15-14-15-15-14-14-15-15-15-14-15-14-15-15-15-14-14-15-15-15-14-14-15-15-15-15-14-15-15-14-15-15-14-15-15-14-15-15-14-15-15-14-15-15-14-15-15-14-14-15-15-14-15-15-14-14-15-15-14-14-15-15-14-14-15-15-14-14-15-15-14-14-15-15-15-14-14-15-15-15-14-14-14-15-

(9)。 TABLESPACE: specify a tablespace export.

(10)。 QUERY= [schema.] [table_name:] query_clause

Schema is used to specify the scheme name, table_name is used to specify the table name, and query_clause is used to specify the conditional restriction clause. The query option cannot be used with options such as CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES. Expdp scott/tiger directory=dump dumpfiel=a.dmp Tables=emp query='WHERE deptno=20'

(11)。 PARALLEL: parallel operation: specifies the number of parallel processes performing the export operation. The default is 1.

Summary

You can significantly speed up the job by using more than one thread for the export through the PARALLEL parameter. Each thread creates a separate dump file, so the parameter dumpfile should have as many items as parallelism.

Instead of explicitly entering individual file names, you can specify wildcards as file names, 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 and the format will be expCASES_nn.dmp, where nn starts at 01 and then increments up as needed.

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

It is important to separate the input / output channels that access the data file and the dump directory file system. Otherwise, the overhead associated with maintaining Data Pump jobs may outweigh the benefits of parallel threads and thus degrade performance. Parallelism is effective only if there are more tables than parallel values and the table is large.

4. Restore data

The process of importing impdp

(1)。 Import to the specified user

Impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott

(2)。 Change the owner of the table

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system TABLE_EXISTS_ACTION=TRUNCATE

(3)。 Import tablespace

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example

(4)。 Import database

Impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y

(5)。 Additional data

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION

Parameter description-Import impdp

(1)。 TABBLE_EXISTS_ACTION= {SKIP | APPEND | TRUNCATE | FRPLACE}

When this option is set to SKIP, the import job skips the existing table to process the next object

When set to APPEND, data is appended

When set to TRUNCATE, the import job truncates the table and appends new data to it

When set to REPLACE, the import job deletes the existing table and rebuilds the table disease appended data

Note that the TRUNCATE option does not apply to the cluster table and NETWORK_LINK options

(2)。 REMAP_SCHEMA

This option is used to load all objects of the source scheme into the target scheme: REMAP_SCHEMA=source_schema:target_schema

(3)。 REMAP_TABLESPACE

Import all objects from the source tablespace into the destination tablespace: REMAP_TABLESPACE=source_tablespace:target:tablespace

(4)。 REMAP_DATAFILE

This option is used to convert the source data file name to the target data file name, which may be required when moving tablespaces between different platforms.

REMAP_DATAFIEL=source_datafie:target_datafile

Thank you for reading! On "ORACLE data pump how to import and export data" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.