1、创建需要恢复的目录，以hr用户为例
1、连接sys用户，给hr用户授于创建目录的权限
TEST@ocp> conn / as sysdba
Connected.
SYS@orcl> show user;
USER is "SYS"
SYS@orcl> grant create any directory to hr;
Grant succeeded.
2、连接hr用户，创建备份目录
SYS@orcl> conn hr/hr@ocp
Connected.
HR@ocp> create directory hr_dump as /home/oracle/hrdump ;
Directory created.
3、在系统目录下创建hrdump目录
[11 oracle@oraocp ~]$ mkdir /home/oracle/hrdump -p
2、expdp命令查看
查看expdp的协助命令：
[11 oracle@oraocp ~]$ expdp help=y
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the expdp command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object s process order number in the master table.
ACCESS_METHOD
Instructs Export to use a particular method to unload data.
Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.
CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.
DIRECTORY
Directory object to be used for dump and log files.
DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.
ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.
ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.
ESTIMATE_ONLY
Calculate job estimates without performing the export [NO].
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"= HR ".
FILESIZE
Specify the size of each dump file in units of bytes.
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Export entire database [NO].
HELP
Display Help messages [NO].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of export job to create.
KEEP_MASTER
Retain the master table after an export job that completes successfully [NO].
LOGFILE
Specify log file name [export.log].
LOGTIME
Specifies that messages displayed during export operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
METRICS
Report additional job information to the export log file [NO].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [NO].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file name.
QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].
SAMPLE
Percentage of data to be exported.
SCHEMAS
List of schemas to export [login schema].
SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.
SOURCE_EDITION
Edition to be used for extracting metadata.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to export.
TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.
VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.
VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
ADD_FILE
Add dumpfile to dumpfile set.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].
START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.
STOP_WORKER
Stops a hung or stuck worker.
TRACE
Set trace/debug flags for the current job.
3、expdp的导出案例
1、导出全库
使用system登录数据库，限制备份的数据文件的大小为300M，一旦备份数据文件满，则自动创建一个新的备份文件，
使用替换变量%U实现。
[12 oracle@oraocp ~]$ expdp system/oracle@ocp dumpfile=hr_dump:db_%u.dat filesize=300m job_name=db full=y
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 09:37:10 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."DB": system/********@ocp dumpfile=hr_dump:db_%u.dat filesize=300m job_name=db full=y
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.117 KB 40 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 9.984 KB 97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.375 KB 1 rows
. . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows
. . exported "WMSYS"."WM$ENV_VARS$" 6.015 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows
. . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "MDSYS"."RDF_PARAM$" 6.515 KB 3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.976 KB 2 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.515 KB 12 rows
. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "HR"."EMPLOYEES" 17.07 KB 107 rows
. . exported "HR"."T_DROP" 17.07 KB 107 rows
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."EMP2" 10.47 KB 56 rows
. . exported "TEST"."EMP" 0 KB 0 rows
. . exported "TEST"."EMP2" 0 KB 0 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "TEST"."DEPT" 0 KB 0 rows
. . exported "SCOTT"."SALGRADE" 6.570 KB 50 rows
. . exported "TEST"."SALGRADE" 0 KB 0 rows
. . exported "U01"."T01" 5.906 KB 1 rows
. . exported "U01"."T02" 5.906 KB 1 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "TEST"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."DB" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.DB is:
/home/oracle/hrdump/db_01.dat
Job "SYSTEM"."DB" successfully completed at Wed Mar 22 09:39:58 2023 elapsed 0 00:02:44
注：一些系统模式不能被导出，由于它们不是用户模式，它们包含Oracle管理的数据和元数据，系统模式中不能导出的
有SYS、ORDSYS和MDSYS。
4、导出hr用户案例
1、导出一个模式，用system用户导出
[16 oracle@oraocp ~/hrdump]$ expdp system/oracle@ocp schemas=hr dumpfile=hr_dump:schema_by_hr.dat
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 09:57:54 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@ocp schemas=hr dumpfile=hr_dump:schema_by_hr.dat
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.07 KB 107 rows
. . exported "HR"."T_DROP" 17.07 KB 107 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hrdump/schema_by_hr.dat
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 22 09:58:42 2023 elapsed 0 00:00:46
查看两个数据字典视图：
select * from dba_datapump_sessions;
select * from dba_datapump_jobs;
2、使用hr用户本身导出
[17 oracle@oraocp ~/hrdump]$ expdp hr/hr@ocp directory=hr_dump dumpfile=hr_by_hr.dat
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 10:07:38 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@ocp directory=hr_dump dumpfile=hr_by_hr.dat
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.07 KB 107 rows
. . exported "HR"."T_DROP" 17.07 KB 107 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/hrdump/hr_by_hr.dat
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 22 10:08:29 2023 elapsed 0 00:00:50
5、导出个别表
1、使用system导出hr用户的三张表
[34 oracle@oraocp ~/hrdump]$ expdp system/oracle@ocp tables=hr.employees,hr.jobs,hr.departments directory=hr_dump dumpfile=table_by_system.dat
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 10:24:41 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@ocp tables=hr.employees,hr.jobs,hr.departments directory=hr_dump dumpfile=table_by_system.dat
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.07 KB 107 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/hrdump/table_by_system.dat
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 22 10:25:05 2023 elapsed 0 00:00:23
2、使用hr用户导出hr用户的三张表
[34 oracle@oraocp ~/hrdump]$ expdp hr/hr@ocp tables=hr.employees,hr.jobs,hr.departments directory=hr_dump dumpfile=table_by_hr.dat
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 10:26:47 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "HR"."SYS_EXPORT_TABLE_01": hr/********@ocp tables=hr.employees,hr.jobs,hr.departments directory=hr_dump dumpfile=table_by_hr.dat
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES" 17.07 KB 107 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/home/oracle/hrdump/table_by_hr.dat
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 22 10:27:06 2023 elapsed 0 00:00:18
6、导出指定的表空间
1、导出users表空间，多个表空间用，分隔
[38 oracle@oraocp ~/hrdump]$ expdp system/oracle@ocp tablespaces=users parallel=2 directory=hr_dump dumpfile=tablespace_by_system.dat
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 10:31:13 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/********@ocp tablespaces=users parallel=2 directory=hr_dump dumpfile=tablespace_by_system.dat
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."EMP2" 10.47 KB 56 rows
. . exported "TEST"."EMP" 0 KB 0 rows
. . exported "TEST"."EMP2" 0 KB 0 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "TEST"."DEPT" 0 KB 0 rows
. . exported "SCOTT"."SALGRADE" 6.570 KB 50 rows
. . exported "TEST"."SALGRADE" 0 KB 0 rows
. . exported "U01"."T01" 5.906 KB 1 rows
. . exported "U01"."T02" 5.906 KB 1 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "TEST"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/home/oracle/hrdump/tablespace_by_system.dat
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Wed Mar 22 10:31:29 2023 elapsed 0 00:00:14
7、导出行数据，不要表结构
通过content参数，可以指定导出的内容：
ALL：导出表数据和元数据；
DATA_ONLY：只导出表行数据；
METADATA_ONLY：只导出元数据，即对象的定义
[38 oracle@oraocp ~/hrdump]$ expdp system/oracle@ocp schemas=hr content=data_only directory=hr_dump dumpfile=hr_by_system_dataonly.dat
8、只计算导出作业所需空间
[43 oracle@oraocp ~/hrdump]$ expdp system/oracle@ocp full=y estimate_only=y
Export: Release 12.2.0.1.0 - Production on Wed Mar 22 11:28:44 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@ocp full=y estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated "SYS"."KU$_USER_MAPPING_VIEW" 64 KB
. estimated "ORDDATA"."ORDDCM_DOCS" 1.25 MB
. estimated "WMSYS"."WM$CONSTRAINTS_TABLE$" 320 KB
. estimated "SYS"."AUD$" 256 KB
. estimated "WMSYS"."WM$LOCKROWS_INFO$" 192 KB
. estimated "WMSYS"."WM$UDTRIG_INFO$" 192 KB
. estimated "LBACSYS"."OLS$AUDIT_ACTIONS" 64 KB
. estimated "LBACSYS"."OLS$DIP_EVENTS" 64 KB
. estimated "LBACSYS"."OLS$INSTALLATIONS" 64 KB
. estimated "LBACSYS"."OLS$PROPS" 64 KB
. estimated "SYS"."DAM_CLEANUP_EVENTS$" 64 KB
. estimated "SYS"."DAM_CLEANUP_JOBS$" 64 KB
. estimated "SYS"."DAM_CONFIG_PARAM$" 64 KB
. estimated "SYS"."TSDP_ASSOCIATION$" 64 KB
. estimated "SYS"."TSDP_CONDITION$" 64 KB
. estimated "SYS"."TSDP_FEATURE_POLICY$" 64 KB
. estimated "SYS"."TSDP_PARAMETER$" 64 KB
. estimated "SYS"."TSDP_POLICY$" 64 KB
. estimated "SYS"."TSDP_PROTECTION$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_DATA$" 64 KB
. estimated "SYS"."TSDP_SENSITIVE_TYPE$" 64 KB
. estimated "SYS"."TSDP_SOURCE$" 64 KB
. estimated "SYS"."TSDP_SUBPOL$" 64 KB
. estimated "SYSTEM"."REDO_DB" 64 KB
. estimated "SYSTEM"."REDO_LOG" 64 KB
. estimated "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 64 KB
. estimated "WMSYS"."WM$CONS_COLUMNS$" 64 KB
. estimated "WMSYS"."WM$ENV_VARS$" 64 KB
. estimated "WMSYS"."WM$EVENTS_INFO$" 64 KB
. estimated "WMSYS"."WM$HINT_TABLE$" 64 KB
. estimated "WMSYS"."WM$MODIFIED_TABLES$" 64 KB
. estimated "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 64 KB
. estimated "WMSYS"."WM$NEXTVER_TABLE$" 64 KB
. estimated "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_LOCKING_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TABLE$" 64 KB
. estimated "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 64 KB
. estimated "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 64 KB
. estimated "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 64 KB
. estimated "WMSYS"."WM$VERSION_TABLE$" 64 KB
. estimated "WMSYS"."WM$VT_ERRORS_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACES_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 64 KB
. estimated "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 64 KB
. estimated "LBACSYS"."OLS$AUDIT" 0 KB
. estimated "LBACSYS"."OLS$COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$DIP_DEBUG" 0 KB
. estimated "LBACSYS"."OLS$GROUPS" 0 KB
. estimated "LBACSYS"."OLS$LAB" 0 KB
. estimated "LBACSYS"."OLS$LEVELS" 0 KB
. estimated "LBACSYS"."OLS$POL" 0 KB
. estimated "LBACSYS"."OLS$POLICY_ADMIN" 0 KB
. estimated "LBACSYS"."OLS$POLS" 0 KB
. estimated "LBACSYS"."OLS$POLT" 0 KB
. estimated "LBACSYS"."OLS$PROFILE" 0 KB
. estimated "LBACSYS"."OLS$PROFILES" 0 KB
. estimated "LBACSYS"."OLS$PROG" 0 KB
. estimated "LBACSYS"."OLS$SESSINFO" 0 KB
. estimated "LBACSYS"."OLS$USER" 0 KB
. estimated "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB
. estimated "LBACSYS"."OLS$USER_GROUPS" 0 KB
. estimated "LBACSYS"."OLS$USER_LEVELS" 0 KB
. estimated "SYS"."FGA_LOG$FOR_EXPORT" 256 KB
. estimated "SYS"."SQL$TEXT_DATAPUMP" 192 KB
. estimated "SYS"."SQLOBJ$DATA_DATAPUMP" 192 KB
. estimated "SYS"."SQL$_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$PLAN_DATAPUMP" 128 KB
. estimated "SYS"."SQLOBJ$_DATAPUMP" 128 KB
. estimated "SYSTEM"."SCHEDULER_JOB_ARGS" 128 KB
. estimated "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 128 KB
. estimated "SYS"."AUDTAB$TBS$FOR_EXPORT" 64 KB
. estimated "SYS"."DBA_SENSITIVE_DATA" 64 KB
. estimated "SYS"."DBA_TSDP_POLICY_PROTECTION" 64 KB
. estimated "SYS"."NACL$_ACE_EXP" 64 KB
. estimated "SYS"."NACL$_HOST_EXP" 64 KB
. estimated "SYS"."NACL$_WALLET_EXP" 64 KB
. estimated "MDSYS"."RDF_PARAM$" 16 KB
. estimated "WMSYS"."WM$EXP_MAP" 16 KB
. estimated "WMSYS"."WM$METADATA_MAP" 16 KB
. estimated "HR"."COUNTRIES" 64 KB
. estimated "HR"."DEPARTMENTS" 64 KB
. estimated "HR"."EMPLOYEES" 64 KB
. estimated "HR"."JOBS" 64 KB
. estimated "HR"."JOB_HISTORY" 64 KB
. estimated "HR"."LOCATIONS" 64 KB
. estimated "HR"."REGIONS" 64 KB
. estimated "HR"."T_DROP" 64 KB
. estimated "SCOTT"."DEPT" 64 KB
. estimated "SCOTT"."EMP" 64 KB
. estimated "SCOTT"."EMP2" 64 KB
. estimated "SCOTT"."SALGRADE" 64 KB
. estimated "U01"."T01" 64 KB
. estimated "U01"."T02" 64 KB
. estimated "SCOTT"."BONUS" 0 KB
. estimated "TEST"."BONUS" 0 KB
. estimated "TEST"."DEPT" 0 KB
. estimated "TEST"."EMP" 0 KB
. estimated "TEST"."EMP2" 0 KB
. estimated "TEST"."SALGRADE" 0 KB
Total estimation using BLOCKS method: 7.421 MB
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Mar 22 11:28:56 2023 elapsed 0 00:00:11
9、impdp导入命令的协助信息
[44 oracle@oraocp ~/hrdump]$ impdp help=y
Import: Release 12.2.0.1.0 - Production on Wed Mar 22 11:30:48 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Import runs by entering the impdp command followed
by various parameters. To specify parameters, you use keywords:
Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object s process order number in the master table.
ACCESS_METHOD
Instructs Import to use a particular method to load data.
Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH,
EXTERNAL_TABLE, and INSERT_AS_SELECT.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].
CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keywords are: DISABLE_APPEND_HINT, SKIP_CONSTRAINT_ERRORS, REJECT_ROWS_WITH_REPL_CHAR and VALIDATE_TABLE_DATA.
DIRECTORY
Directory object to be used for dump, log and SQL files.
DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.
ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.
ESTIMATE
Calculate network job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"= HR ".
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Import everything from source [YES].
HELP
Display help messages [NO].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of import job to create.
KEEP_MASTER
Retain the master table after an import job that completes successfully [NO].
LOGFILE
Log file name [import.log].
LOGTIME
Specifies that messages displayed during import operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
MASTER_ONLY
Import just the master table and then stop the job [NO].
METRICS
Report additional job information to the import log file [NO].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [NO].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file.
PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].
QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REMAP_DATAFILE
Redefine data file references in all DDL statements.
REMAP_SCHEMA
Objects from one schema are loaded into another schema.
REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.
REUSE_DATAFILES
Tablespace will be initialized if it already exists [NO].
SCHEMAS
List of schemas to import.
SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.
SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.
SOURCE_EDITION
Edition to be used for extracting metadata.
SQLFILE
Write all the SQL DDL to a specified file.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STREAMS_CONFIGURATION
Enable the loading of Streams metadata [YES].
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to import.
TARGET_EDITION
Edition to be used for loading metadata.
TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,
LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, SEGMENT_CREATION,
STORAGE, and TABLE_COMPRESSION_CLAUSE.
TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.
TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
Only valid in NETWORK_LINK mode import operations.
TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
VIEWS_AS_TABLES
Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
Note that in network import mode, a table name may be appended
to the view name.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.
STOP_WORKER
Stops a hung or stuck worker.
TRACE
Set trace/debug flags for the current job.
10、导入案例
1、导入全库
导入整库至少需要两个参数，一个是FULL，设置FULL=Y，说明是导入全库，一个是DUMPFILE，说明要导
入的备份文件的目录和名称。
[47 oracle@oraocp ~/hrdump]$ impdp system/oracle@ocp full=y directory=hr_dump dumpfile=db_01.dat
2、导入表空间
[47 oracle@oraocp ~/hrdump]$ impdp system/oracle@ocp tablespaces=users directory=hr_dump dumpfile=tablespace_by_system.dat table_exists_action=replace
table_exists_action选项：当表存在的情况如何处理，replace是替代
3、导入指定的表
[47 oracle@oraocp ~/hrdump]$ impdp hr/hr@ocp tables=employees directory=hr_dump dumpfile=table_by_hr.dat table_exists_action=replace
4、只导入表结构，比上边的命令多加了content选项
[47 oracle@oraocp ~/hrdump]$ impdp hr/hr@ocp tables=employees directory=hr_dump dumpfile=table_by_hr.dat content=metadata_only table_exists_action=replace
5、只导入表数据，把上一步的表结构中加入数据
[47 oracle@oraocp ~/hrdump]$ impdp hr/hr@ocp tables=employees directory=hr_dump dumpfile=table_by_hr.dat content=data_only
6、导入指定的数据库对象
[47 oracle@oraocp ~/hrdump]$ impdp system/oracle@ocp tables=hr.employees dumpfile=hr_dump:schema_by_hr.dat include=table_data
7、重新映射一个新的用户，注意test2用户根本不存在
[124 oracle@oraocp ~/hrdump]$ impdp system/oracle@ocp dumpfile=hr_dump:schema_by_hr.dat remap_schema=hr:test2
登入sys用户，更改test2用户密码
@> conn / as sysdba
Connected.
SYS@orcl> alter user test2 identified by test2;
User altered.
SYS@orcl> conn test2/test2@ocp
Connected.
TEST2@ocp> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
T_DROP TABLE
把hr用户的数据恢复至test2用户
8、提取SQL脚本
[133 oracle@oraocp ~/hrdump]$ impdp system/oracle@ocp dumpfile=hr_dump:schema_by_hr.dat directory=hr_dump sqlfile=schema_by_hr.sql
© 版权声明
文章版权归作者所有，未经允许请勿转载。如内容涉嫌侵权，请在本页底部进入<联系我们>进行举报投诉!
THE END
暂无评论内容