ORACLE Data Pump Import and export data

I. Summary
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.
Ii. difference between Exp/imp and EXPDP/IMPDP
(1) The user UserA the object 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, in order to change the table space, you need to handle it manually,

Actions such as ALTER TABLE XXX move tablespace_new.

Use IMPDP as long as remap_tablespace= ' tabspace_old ': ' Tablespace_new '

(3) When specifying some tables, when using Exp/imp, the Tables usage is tables= (' table1′, ' 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 row, Data_only: Export only Object, 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. 11G/12C start can be run by the client, saved on the server.

(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.
Third, export data
Step1. Create a logical directory that does not create a real directory in the operating system, preferably created by an administrator such as system.
Create directory Data_pump name as ' Data_pump directory ';
Step2. View the Management Manager directory (also see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)
Select Directory_name,directory_path from dba_directories where directory_name= ' data_pump name ';
Step3. Give the user permission to operate in the specified directory, preferably given by an administrator such as system.
Grant Read,write on directory data_pump directories to users;

(1). Guide the entire database
C:\USERS\ADMINISTRATOR>EXPDP System/[email Protected]_plug dumpfile=ful.dmp
Directory=systemdmp Logfile=full.log full=y
Export:release 12.2.0.1.0-production on Monday January 22 16:12:01 2018

Copyright (c) 1982, 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
Working with Object Types Database_export/early_options/views_as_tables/table_data
Working with Object Types Database_export/normal_options/table_data
Working with Object Types Database_export/normal_options/views_as_tables/table_data
Working with Object Types Database_export/schema/table/table_data
Working with Object Types Database_export/schema/table/index/statistics/index_statistics
Working with Object Types Database_export/schema/table/statistics/table_statistics
Working with Object Types Database_export/statistics/marker
Working with Object Types Database_export/pre_system_impcallout/marker
Working with Object Types Database_export/pre_instance_impcallout/marker
Working with Object Types Database_export/tablespace
Working with Object Types Database_export/profile
Working with Object Types Database_export/sys_user/user
Working with Object Types Database_export/schema/user
Working with Object Types Database_export/role
Working with Object Types Database_export/radm_fptm
Working with Object Types Database_export/grant/system_grant/proc_system_grant
Working with Object Types Database_export/schema/grant/system_grant
Working with Object Types Database_export/schema/role_grant
Working with Object Types Database_export/schema/default_role
Working with Object Types Database_export/schema/on_user_grant
Working with Object Types Database_export/resource_cost
Working with Object Types Database_export/schema/db_link
Working with Object Types Database_export/trusted_db_link
Working with Object Types Database_export/schema/sequence/sequence
Working with Object Types Database_export/directory/directory
Working with Object Types Database_export/directory/grant/owner_grant/object_grant
Working with Object Types Database_export/system_procobjact/pre_system_actions/procact_system

Working with Object Types Database_export/system_procobjact/procobj
Working with Object Types Database_export/system_procobjact/post_system_actions/procact_syste
M
Working with Object Types Database_export/schema/procact_schema
Working with Object Types database_export/early_options/views_as_tables/table
Working with Object Types Database_export/early_post_instance_impcallout/marker
Working with Object Types database_export/normal_options/table
Working with Object Types database_export/normal_options/views_as_tables/table
Working with Object Types Database_export/normal_post_instance_impcallout/marker
Working with Object Types database_export/schema/table/table
Working with Object Types Database_export/schema/table/comment
Working with Object Types Database_export/schema/view/view
Working with Object Types Database_export/schema/table/index/index
Working with Object Types Database_export/schema/table/constraint/constraint
Working with Object Types Database_export/schema/table/constraint/ref_constraint
Working with Object Types Database_export/final_post_instance_impcallout/marker
Working with Object Types Database_export/schema/post_schema/procact_schema
Working with Object Types database_export/audit_unified/audit_policy_enable
Working with Object Types Database_export/post_system_impcallout/marker
. . "SYS" was exported. " Ku$_user_mapping_view "6.125 KB 40 rows
. . "SYSTEM" was exported. " redo_db "25.58 KB 1 rows
. . "Orddata" was exported. " Orddcm_docs "252.9 KB 9 rows
. . "Wmsys" was exported. " wm$workspaces_table$ "12.10 KB 1 rows
. . "Wmsys" was exported. " wm$hint_table$ "9.984 KB 97 rows
. . "Lbacsys" was exported. " Ols$installations "6.953 KB 2 rows
. . "Wmsys" was exported. " wm$workspace_priv_table$ "7.078 KB 11 rows
. . "SYS" was exported. " dam_config_param$ "6.523 KB 14 rows
. . "SYS" was exported. " tsdp_subpol$ "6.320 KB 1 rows
. . "Wmsys" was exported. " wm$nextver_table$ "6.375 KB 1 rows
. . "Lbacsys" was exported. " Ols$props "6.234 KB 5 rows
. . "Wmsys" was exported. " wm$env_vars$ "6.015 KB 3 rows
. . "SYS" was exported. " tsdp_parameter$ "5.945 KB 1 rows
. . "SYS" was exported. " tsdp_policy$ "5.914 KB 1 rows
. . "Wmsys" was exported. " wm$version_hierarchy_table$ "5.976 KB 1 rows
. . "Wmsys" was exported. " wm$events_info$ "5.812 KB 12 rows
. . "Lbacsys" was exported. " Ols$audit_actions "5.75 KB 8 rows
. . "Lbacsys" was exported. " Ols$dip_events "5.539 KB 2 rows
. . "Lbacsys" was exported. " Ols$audit "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$compartments "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$dip_debug "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$groups "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$lab "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$levels "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$pol "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$policy_admin "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$pols "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$polt "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$profile "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$profiles "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$prog "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$sessinfo "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$user "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$user_compartments "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$user_groups "0 KB 0 rows
. . "Lbacsys" was exported. " Ols$user_levels "0 KB 0 rows
. . "SYS" was exported. " aud$ "0 KB 0 rows
. . "SYS" was exported. " dam_cleanup_events$ "0 KB 0 rows
. . "SYS" was exported. " dam_cleanup_jobs$ "0 KB 0 rows
. . "SYS" was exported. " tsdp_association$ "0 KB 0 rows
. . "SYS" was exported. " tsdp_condition$ "0 KB 0 rows
. . "SYS" was exported. " tsdp_feature_policy$ "0 KB 0 rows
. . "SYS" was exported. " tsdp_protection$ "0 KB 0 rows
. . "SYS" was exported. " tsdp_sensitive_data$ "0 KB 0 rows
. . "SYS" was exported. " tsdp_sensitive_type$ "0 KB 0 rows
. . "SYS" was exported. " tsdp_source$ "0 KB 0 rows
. . "SYSTEM" was exported. " Redo_log "0 KB 0 rows
. . "Wmsys" was exported. " wm$batch_compressible_tables$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$constraints_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$cons_columns$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$lockrows_info$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$modified_tables$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$mp_graph_workspaces_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$mp_parent_workspaces_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$nested_columns_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$resolve_workspaces_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$ric_locking_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$ric_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$ric_triggers_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$udtrig_dispatch_procs$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$udtrig_info$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$version_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$vt_errors_table$ "0 KB 0 rows
. . "Wmsys" was exported. " wm$workspace_savepoints_table$ "0 KB 0 rows
. . "Mdsys" was exported. " rdf_param$ "6.507 KB 3 rows
. . "SYS" was exported. " Audtab$tbs$for_export "5.953 KB 2 rows
. . "SYS" was exported. " Dba_sensitive_data "0 KB 0 rows
. . "SYS" was exported. " Dba_tsdp_policy_protection "0 KB 0 rows
. . "SYS" was exported. " Fga_log$for_export "0 KB 0 rows
. . "SYS" was exported. " Nacl$_ace_exp "0 KB 0 rows
. . "SYS" was exported. " Nacl$_host_exp "6.914 KB 1 rows
. . "SYS" was exported. " Nacl$_wallet_exp "0 KB 0 rows
. . "SYS" was exported. " Sql$text_datapump "0 KB 0 rows
. . "SYS" was exported. " Sql$_datapump "0 KB 0 rows
. . "SYS" was exported. " Sqlobj$auxdata_datapump "0 KB 0 rows
. . "SYS" was exported. " Sqlobj$data_datapump "0 KB 0 rows
. . "SYS" was exported. " Sqlobj$plan_datapump "0 KB 0 rows
. . "SYS" was exported. " Sqlobj$_datapump "0 KB 0 rows
. . "SYSTEM" was exported. " Scheduler_job_args "0 KB 0 rows
. . "SYSTEM" was exported. " Scheduler_program_args "9.515 KB 12 rows
. . "Wmsys" was exported. " Wm$exp_map "7.710 KB 3 rows
. . "Wmsys" was exported. " Wm$metadata_map "0 KB 0 rows
. . "SCOTT" was exported. " EMP "8.695 KB 12 Line
. . "SCOTT" was exported. " DEPT "6 KB 3 rows
. . "SCOTT" was exported. " Salgrade "5.953 KB 5 rows
. . "SCOTT" was exported. " Test_01 "5.187 KB 12 rows
. . "SCOTT" was exported. " BONUS "0 KB 0 rows
. . "SCOTT" was exported. " TEST1 "0 KB 0 rows
. . "SCOTT" was exported. " TEST2 "0 KB 0 rows
The main Table "SYSTEM" was successfully loaded/uninstalled. " Sys_export_full_01 "

SYSTEM. The sys_export_full_01 dump file set is:
C:\ORACLEBACK\DATAPUMP\FUL. DMP
Job "SYSTEM". " Sys_export_full_01 "was Monday January 16:14:04 2018 Elapsed 0 00
: 02:02 completed successfully
Verify
[Email protected]_plugpdb> drop user Scott Cascade;

User dropped.

elapsed:00:00:02.85
[Email protected]_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/[email Protected]_plug Dumpfile=systemd
Mp:ful.dmp Logfile=scott_imp.log Schemas=scott

Import:release 12.2.0.1.0-production on Monday January 22 16:30:23 2018

Copyright (c) 1982, 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 main Table "SYSTEM" was successfully loaded/uninstalled. " Sys_import_schema_01 "
Start "SYSTEM". " Sys_import_schema_01 ": system/****@clonepdb_plug Dumpfile=sy
Stemdmp:ful.dmp Logfile=scott_imp.log Schemas=scott
Working with Object Types Database_export/schema/user
Working with Object Types Database_export/schema/grant/system_grant
Working with Object Types Database_export/schema/role_grant
Working with Object Types Database_export/schema/default_role
Working with Object Types Database_export/schema/sequence/sequence
Working with Object Types Database_export/schema/procact_schema
Working with Object Types database_export/schema/table/table
Working with Object Types Database_export/schema/table/table_data
. . "SCOTT" was imported. " EMP "8.695 KB 12 Line
. . "SCOTT" was imported. " DEPT "6 KB 3 rows
. . "SCOTT" was imported. " Salgrade "5.953 KB 5 rows
. . "SCOTT" was imported. " Test_01 "5.187 KB 12 rows
. . "SCOTT" was imported. " BONUS "0 KB 0 rows
. . "SCOTT" was imported. " TEST1 "0 KB 0 rows
. . "SCOTT" was imported. " TEST2 "0 KB 0 rows
Working with Object Types Database_export/schema/view/view
Working with Object Types Database_export/schema/table/constraint/constraint
Working with Object Types Database_export/schema/table/index/statistics/index_statistics
Working with Object Types Database_export/schema/table/constraint/ref_constraint
Working with Object Types Database_export/schema/table/statistics/table_statistics
Working with Object Types Database_export/statistics/marker
ORA-39082: Object type VIEW: "SCOTT". " V_test "created with compile warning

ORA-39082: Object type VIEW: "SCOTT". " V_test1 "created with compile warning

Job "SYSTEM". " Sys_import_schema_01 "has been completed, but there are 2 errors (in Monday January 22
16:30:43 2018 Elapsed 0 00:00:18 completion)
[Email protected]_plugpdb> select * from Scott.emp;

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

  7369 SMITH Clerk 7902 1980-12-17 00:00:00 7499 ALLEN salesman       7698 1981-02-20 00:00:00 7521 WARD salesman 7698 1981-02-22 00:00:00  1250 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20         7654 MARTIN salesman 7698 1981-09-28 00:00:00 1250 1400-7698 BLAKE MANAGER       7839 1981-05-01 00:00:00 2850 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 7839 KING President 1981-11-17 00:00:00 5000 10 78 7698 TURNER salesman 1981-09-08 00:00:00 0 7900 JAMES Clerk 7 698 1981-12-03 00:00:00 950 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3 000 20 EMPNO ename JOB MGR hiredate SAL COMM DEPTNO 

  7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

Rows selected.

elapsed:00:00:00.02
(2). According to the User guide
C:\USERS\ADMINISTRATOR>EXPDP System/[email Protected]_plug Dumpfile=systemd
Mp:scott.dmp Logfile=scott_imp.log Schemas=scott reuse_dumpfiles=y
(3). Parallel process Parallel
C:\USERS\ADMINISTRATOR>EXPDP System/[email Protected]_plug Dumpfile=systemd
Mp:ful.dmp logfile=scott_imp.log reuse_dumpfiles=y parallel=2 full=y
(4). by Table name
C:\USERS\ADMINISTRATOR>EXPDP System/[email Protected]_plug Dumpfile=systemd
Mp:expdp.dmp Logfile=expdp.log reuse_dumpfiles=y tables=scott.emp

(5). Guided by query criteria
C:\USERS\ADMINISTRATOR>EXPDP Scott/[email Protected]_plug DUMPFILE=SYSTEMDMP:EXPDP
. DMP Logfile=expdp.log reuse_dumpfiles=y tables=emp query= ' WHERE deptno=20 '

ORACLE Data Pump Import and export data