Oracle EXPDP和IMPDP指令详解
来源:互联网 发布:java开发app教程 编辑:程序博客网 时间:2024/05/01 19:23
概要
Oracle 11g及以上版本提供了数据泵技术,可以用来进行数据库的逻辑备份和恢复,Oracle建议使用数据泵来代替EXP和IMP技术。数据泵技术基于Oracle数据库服务器,导入导出的数据文件也保存在数据库服务器端,而EXP/IMP基于客户机运行。利用数据泵技术,Oracle要求必须使用目录对象,以防止用户误操作服务器上特定目录下的操作系统文件。
EXPDP数据访问方法
外部表:从操作系统文件中读取数据;
直接路径:使用直接路径API,很好改善了导入导出性能,因为内部流的数据格式和存储在备份文件中的数据格式相同,减少了数据转换的时间。
查询默认的目录对象
SQL> set linesize 200SQL> SELECT * FROM dba_directories t 2 WHERE t.directory_name = 'DATA_PUMP_DIR';OWNER DIRECTORY_NAME DIRECTORY_PATH------------------------------ ------------------------------ -----------------------------------SYS DATA_PUMP_DIR E:\app\alen.liu\admin\orcl\dpdump\
创建恢复目录需要CREATE ANY DIRECTORY权限;
SQL> grant create any directory to scott;Grant succeeded.SQL> alter session set current_schema=scott;Session altered.SQL> create directory scott_pump as 'd:\pump';Directory created.SQL>
EXPDP导出技术演示
EXPDP参数
D:\backup>expdp help=yExport: Release 11.2.0.1.0 - Production on Tue Jun 27 17:13:24 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.The Data Pump export utility provides a mechanism for transferring data objectsbetween Oracle databases. The utility is invoked with the following command: Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmpYou can control how Export runs by entering the 'expdp' command followedby 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 tableUSERID must be the first parameter on the command line.------------------------------------------------------------------------------The available keywords and their descriptions follow. Default values are listed within square brackets.ATTACHAttach to an existing job.For example, ATTACH=job_name.COMPRESSIONReduce the size of a dump file.Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.CONTENTSpecifies data to unload.Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.DATA_OPTIONSData layer option flags.Valid keyword values are: XML_CLOBS.DIRECTORYDirectory object to be used for dump and log files.DUMPFILESpecify list of destination dump file names [expdat.dmp].For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.ENCRYPTIONEncrypt part or all of a dump file.Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.ENCRYPTION_ALGORITHMSpecify how encryption should be done.Valid keyword values are: [AES128], AES192 and AES256.ENCRYPTION_MODEMethod of generating encryption key.Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].ENCRYPTION_PASSWORDPassword key for creating encrypted data within a dump file.ESTIMATECalculate job estimates.Valid keyword values are: [BLOCKS] and STATISTICS.ESTIMATE_ONLYCalculate job estimates without performing the export.EXCLUDEExclude specific object types.For example, EXCLUDE=SCHEMA:"='HR'".FILESIZESpecify the size of each dump file in units of bytes.FLASHBACK_SCNSCN used to reset session snapshot.FLASHBACK_TIMETime used to find the closest corresponding SCN value.FULLExport entire database [N].HELPDisplay Help messages [N].INCLUDEInclude specific object types.For example, INCLUDE=TABLE_DATA.JOB_NAMEName of export job to create.LOGFILESpecify log file name [export.log].NETWORK_LINKName of remote database link to the source system.NOLOGFILEDo not write log file [N].PARALLELChange the number of active workers for current job.PARFILESpecify parameter file name.QUERYPredicate clause used to export a subset of a table.For example, QUERY=employees:"WHERE department_id > 10".REMAP_DATASpecify a data conversion function.For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.REUSE_DUMPFILESOverwrite destination dump file if it exists [N].SAMPLEPercentage of data to be exported.SCHEMASList of schemas to export [login schema].SOURCE_EDITIONEdition to be used for extracting metadata.STATUSFrequency (secs) job status is to be monitored wherethe default [0] will show new status when available.TABLESIdentifies a list of tables to export.For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.TABLESPACESIdentifies a list of tablespaces to export.TRANSPORTABLESpecify whether transportable method can be used.Valid keyword values are: ALWAYS and [NEVER].TRANSPORT_FULL_CHECKVerify storage segments of all tables [N].TRANSPORT_TABLESPACESList of tablespaces from which metadata will be unloaded.VERSIONVersion of objects to export.Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.------------------------------------------------------------------------------The following commands are valid while in interactive mode.Note: abbreviations are allowed.ADD_FILEAdd dumpfile to dumpfile set.CONTINUE_CLIENTReturn to logging mode. Job will be restarted if idle.EXIT_CLIENTQuit client session and leave job running.FILESIZEDefault filesize (bytes) for subsequent ADD_FILE commands.HELPSummarize interactive commands.KILL_JOBDetach and delete job.PARALLELChange the number of active workers for current job.REUSE_DUMPFILESOverwrite destination dump file if it exists [N].START_JOBStart or resume current job.Valid keyword values are: SKIP_CURRENT.STATUSFrequency (secs) job status is to be monitored wherethe default [0] will show new status when available.STOP_JOBOrderly shutdown of job execution and exits the client.Valid keyword values are: IMMEDIATE.
演示:
1)导出整个数据库
D:\>expdp sys directory=sys_pump dumpfile=wholedb_%u.dat filesize=100m job_name=alen full=yExport: Release 11.2.0.1.0 - Production on Wed Jun 28 09:06:24 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Password:UDE-01017: operation generated ORACLE error 1017ORA-01017: invalid username/password; logon deniedUsername: sys as sysdbaPassword:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."ALEN": sys/******** AS SYSDBA directory=sys_pump dumpfile=wholedb_%u.dat filesize=100m job_name=alen full=yEstimate in progress using BLOCKS method...Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATATotal estimation using BLOCKS method: 153.0 MBProcessing object type DATABASE_EXPORT/TABLESPACEProcessing object type DATABASE_EXPORT/PROFILEProcessing object type DATABASE_EXPORT/SYS_USER/USERProcessing object type DATABASE_EXPORT/SCHEMA/USERProcessing object type DATABASE_EXPORT/ROLEProcessing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANTProcessing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLEProcessing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTAProcessing object type DATABASE_EXPORT/RESOURCE_COSTProcessing object type DATABASE_EXPORT/TRUSTED_DB_LINKProcessing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCEProcessing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/DIRECTORY/DIRECTORYProcessing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type DATABASE_EXPORT/CONTEXTProcessing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYMProcessing object type DATABASE_EXPORT/SCHEMA/SYNONYMProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPEProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPECProcessing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT---------------------省略中间部分--------------------------------. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rowsMaster table "SYS"."ALEN" successfully loaded/unloaded******************************************************************************Dump file set for SYS.ALEN is: D:\BACKUP\WHOLE_01.DAT D:\BACKUP\WHOLE_02.DATJob "SYS"."ALEN" successfully completed at 10:25:24
2)导出某个用户
D:\>expdp scott/scott@orcl directory=scott_pump dumpfile=scott.datExport: Release 11.2.0.1.0 - Production on Wed Jun 28 09:01:42 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@orcl directory=scott_pump dumpfile=scott.datEstimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 4.187 MBProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."T_COMMISSION_BALANCE" 2.212 MB 25298 rows. . exported "SCOTT"."DEPT" 5.953 KB 5 rows. . exported "SCOTT"."EMP" 8.578 KB 14 rows. . exported "SCOTT"."SALGRADE" 5.937 KB 10 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: D:\SCOTT_PUMP\SCOTT.DATJob "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:02:12
3)导出特定的表
D:\>expdp directory=sys_pump dumpfile=scott.dat tables=scott.emp,scott.dept job_name=scott;Export: Release 11.2.0.1.0 - Production on Wed Jun 28 10:29:36 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: sys as sysdbaPassword:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."scott;": sys/******** AS SYSDBA directory=sys_pump dumpfile=scott.dat tables=scott.emp,scott.dept job_name=scott;Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 128 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."DEPT" 5.953 KB 5 rows. . exported "SCOTT"."EMP" 8.578 KB 14 rowsMaster table "SYS"."scott;" successfully loaded/unloaded******************************************************************************Dump file set for SYS.scott; is: D:\BACKUP\SCOTT.DATJob "SYS"."scott;" successfully completed at 10:29:51
4)导出指定的表空间
D:\scott_pump>expdp directory=sys_pump dumpfile=users.dat tablespaces=usersExport: Release 11.2.0.1.0 - Production on Wed Jun 28 12:30:11 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: sys as sysdbaPassword:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_TABLESPACE_01": sys/******** AS SYSDBA directory=sys_pump dumpfile=users.dat tablespaces=useEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 9.312 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/COMMENTProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "ALEN"."T_COMMISSION_BALANCE" 2.212 MB 25298 rows. . exported "SCOTT"."T_COMMISSION_BALANCE" 2.212 MB 25298 rows. . exported "OE"."PURCHASEORDER" 243.9 KB 132 rows. . exported "ALEN"."DEPT" 5.953 KB 5 rows. . exported "ALEN"."EMP" 8.570 KB 14 rows. . exported "ALEN"."SALGRADE" 5.929 KB 10 rows. . exported "ALEN"."T1" 6.75 KB 100 rows. . exported "ALEN"."TEST" 5.421 KB 2 rows. . exported "ALEN"."T_FLASH" 6.757 KB 100 rows. . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.50 KB 288 rows. . exported "OE"."CATEGORIES_TAB" 14.15 KB 22 rows. . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.585 KB 21 rows. . exported "SCOTT"."DEPT" 5.953 KB 5 rows. . exported "SCOTT"."EMP" 8.578 KB 14 rows. . exported "SCOTT"."SALGRADE" 5.937 KB 10 rows. . exported "ALEN"."BONUS" 0 KB 0 rows. . exported "SCOTT"."BONUS" 0 KB 0 rows. . exported "SH"."DIMENSION_EXCEPTIONS" 0 KB 0 rowsMaster table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is: D:\BACKUP\USERS.DATJob "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at 12:30:32
5)导出数据以及表定义,用content参数指定,用ALL来指定导出表数据和表定义,用DATA_ONLY来指定只导出数据行,用METADATA_ONLY指定导出数据表定义;
D:\>expdp scott/scott directory=scott_pump dumpfile=scott_data_only content=data_only job_name=data_onlyExport: Release 11.2.0.1.0 - Production on Wed Jun 28 10:46:04 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SCOTT"."DATA_ONLY": scott/******** directory=scott_pump dumpfile=scott_data_only content=data_only job_name=data_onlyEstimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 4.187 MB. . exported "SCOTT"."T_COMMISSION_BALANCE" 2.212 MB 25298 rows. . exported "SCOTT"."DEPT" 5.953 KB 5 rows. . exported "SCOTT"."EMP" 8.578 KB 14 rows. . exported "SCOTT"."SALGRADE" 5.937 KB 10 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "SCOTT"."DATA_ONLY" successfully loaded/unloaded******************************************************************************Dump file set for SCOTT.DATA_ONLY is: D:\SCOTT_PUMP\SCOTT_DATA_ONLY.DMPJob "SCOTT"."DATA_ONLY" successfully completed at 10:46:08
6)预估导出数据文件的大小,使用参数estimate_only来指定;
D:\>expdp scott/scott directory=scott_pump estimate_only=y estimate=statisticsExport: Release 11.2.0.1.0 - Production on Wed Jun 28 10:55:18 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=scott_pump estimate_only=y estimate=statisticsEstimate in progress using STATISTICS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. estimated "SCOTT"."T_COMMISSION_BALANCE" 2.130 MB. estimated "SCOTT"."EMP" 8.203 KB. estimated "SCOTT"."SALGRADE" 5.906 KB. estimated "SCOTT"."DEPT" 5.896 KB. estimated "SCOTT"."BONUS" 0 KBTotal estimation using STATISTICS method: 2.150 MBJob "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:55:21
IMPDP导入技术演示
IMPDP参数
D:\>impdp help=yImport: Release 11.2.0.1.0 - Production on Wed Jun 28 10:57:43 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.The Data Pump Import utility provides a mechanism for transferring data objectsbetween Oracle databases. The utility is invoked with the following command: Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmpYou can control how Import runs by entering the 'impdp' command followedby 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.dmpUSERID must be the first parameter on the command line.------------------------------------------------------------------------------The available keywords and their descriptions follow. Default values are listed within square brackets.ATTACHAttach to an existing job.For example, ATTACH=job_name.CONTENTSpecifies data to load.Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.DATA_OPTIONSData layer option flags.Valid keywords are: SKIP_CONSTRAINT_ERRORS.DIRECTORYDirectory object to be used for dump, log and sql files.DUMPFILEList of dumpfiles to import from [expdat.dmp].For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.ENCRYPTION_PASSWORDPassword key for accessing encrypted data within a dump file.Not valid for network import jobs.ESTIMATECalculate job estimates.Valid keywords are: [BLOCKS] and STATISTICS.EXCLUDEExclude specific object types.For example, EXCLUDE=SCHEMA:"='HR'".FLASHBACK_SCNSCN used to reset session snapshot.FLASHBACK_TIMETime used to find the closest corresponding SCN value.FULLImport everything from source [Y].HELPDisplay help messages [N].INCLUDEInclude specific object types.For example, INCLUDE=TABLE_DATA.JOB_NAMEName of import job to create.LOGFILELog file name [import.log].NETWORK_LINKName of remote database link to the source system.NOLOGFILEDo not write log file [N].PARALLELChange the number of active workers for current job.PARFILESpecify parameter file.PARTITION_OPTIONSSpecify how partitions should be transformed.Valid keywords are: DEPARTITION, MERGE and [NONE].QUERYPredicate clause used to import a subset of a table.For example, QUERY=employees:"WHERE department_id > 10".REMAP_DATASpecify a data conversion function.For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.REMAP_DATAFILERedefine datafile references in all DDL statements.REMAP_SCHEMAObjects from one schema are loaded into another schema.REMAP_TABLETable names are remapped to another table.For example, REMAP_TABLE=EMP.EMPNO:REMAPPKG.EMPNO.REMAP_TABLESPACETablespace object are remapped to another tablespace.REUSE_DATAFILESTablespace will be initialized if it already exists [N].SCHEMASList of schemas to import.SKIP_UNUSABLE_INDEXESSkip indexes that were set to the Index Unusable state.SOURCE_EDITIONEdition to be used for extracting metadata.SQLFILEWrite all the SQL DDL to a specified file.STATUSFrequency (secs) job status is to be monitored wherethe default [0] will show new status when available.STREAMS_CONFIGURATIONEnable the loading of Streams metadataTABLE_EXISTS_ACTIONAction to take if imported object already exists.Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.TABLESIdentifies a list of tables to import.For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.TABLESPACESIdentifies a list of tablespaces to import.TARGET_EDITIONEdition to be used for loading metadata.TRANSFORMMetadata transform to apply to applicable objects.Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.TRANSPORTABLEOptions for choosing transportable data movement.Valid keywords are: ALWAYS and [NEVER].Only valid in NETWORK_LINK mode import operations.TRANSPORT_DATAFILESList of datafiles to be imported by transportable mode.TRANSPORT_FULL_CHECKVerify storage segments of all tables [N].TRANSPORT_TABLESPACESList of tablespaces from which metadata will be loaded.Only valid in NETWORK_LINK mode import operations.VERSIONVersion of objects to import.Valid keywords are: [COMPATIBLE], LATEST or any valid database version.Only valid for NETWORK_LINK and SQLFILE.------------------------------------------------------------------------------The following commands are valid while in interactive mode.Note: abbreviations are allowed.CONTINUE_CLIENTReturn to logging mode. Job will be restarted if idle.EXIT_CLIENTQuit client session and leave job running.HELPSummarize interactive commands.KILL_JOBDetach and delete job.PARALLELChange the number of active workers for current job.START_JOBStart or resume current job.Valid keywords are: SKIP_CURRENT.STATUSFrequency (secs) job status is to be monitored wherethe default [0] will show new status when available.STOP_JOBOrderly shutdown of job execution and exits the client.Valid keywords are: IMMEDIATE.
1)导入指定的表空间
D:\scott_pump>impdp directory=sys_pump dumpfile=users.dat tablespaces=users table_exists_action=replaceImport: Release 11.2.0.1.0 - Production on Wed Jun 28 12:32:38 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Username: sys as sysdbaPassword:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_TABLESPACE_01": sys/******** AS SYSDBA directory=sys_pump dumpfile=users.dat tablespaces=users table_exists_action=replacProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "ALEN"."T_COMMISSION_BALANCE" 2.212 MB 25298 rows. . imported "SCOTT"."T_COMMISSION_BALANCE" 2.212 MB 25298 rows. . imported "OE"."PURCHASEORDER" 243.9 KB 132 rows. . imported "ALEN"."DEPT" 5.953 KB 5 rows. . imported "ALEN"."EMP" 8.570 KB 14 rows. . imported "ALEN"."SALGRADE" 5.929 KB 10 rows. . imported "ALEN"."T1" 6.75 KB 100 rows. . imported "ALEN"."TEST" 5.421 KB 2 rows. . imported "ALEN"."T_FLASH" 6.757 KB 100 rows. . imported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.50 KB 288 rows. . imported "OE"."CATEGORIES_TAB" 14.15 KB 22 rows. . imported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.585 KB 21 rows. . imported "SCOTT"."DEPT" 5.953 KB 5 rows. . imported "SCOTT"."EMP" 8.578 KB 14 rows. . imported "SCOTT"."SALGRADE" 5.937 KB 10 rows. . imported "ALEN"."BONUS" 0 KB 0 rows. . imported "SCOTT"."BONUS" 0 KB 0 rows. . imported "SH"."DIMENSION_EXCEPTIONS" 0 KB 0 rowsProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/COMMENTProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at 12:33:06
2)导入某个特定的表
D:\>impdp scott/scott directory=scott_pump tables=scott.emp,scott.dept dumpfile=scott.datImport: Release 11.2.0.1.0 - Production on Wed Jun 28 11:04:04 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=scott_pump tables=scott.emp,scott.dept dumpfile=scott.datProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "SCOTT"."DEPT" 5.953 KB 5 rows. . imported "SCOTT"."EMP" 8.578 KB 14 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 11:04:08
3)利用SQLFILE参数提取DDL语句
D:\>impdp scott/scott directory=scott_pump tables=emp,dept dumpfile=scott.dat sqlfile=scott.sqlImport: Release 11.2.0.1.0 - Production on Wed Jun 28 11:21:28 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SCOTT"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloadedStarting "SCOTT"."SYS_SQL_FILE_TABLE_01": scott/******** directory=scott_pump tables=emp,dept dumpfile=scott.dat sqlfile=scott.sqlProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SCOTT"."SYS_SQL_FILE_TABLE_01" successfully completed at 11:21:30
4)导入指定的数据库对象
D:\scott_pump>impdp scott/scott dumpfile=users.dat directory=sys_pump include=table table_exists_action=replaceImport: Release 11.2.0.1.0 - Production on Wed Jun 28 12:50:16 2017Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-39154: Objects from foreign schemas have been removed from importMaster table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SCOTT"."SYS_IMPORT_FULL_01": scott/******** dumpfile=users.dat directory=sys_pump include=table table_exists_action=replaceProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "SCOTT"."T_COMMISSION_BALANCE" 2.212 MB 25298 rows. . imported "SCOTT"."DEPT" 5.953 KB 5 rows. . imported "SCOTT"."EMP" 8.578 KB 14 rows. . imported "SCOTT"."SALGRADE" 5.937 KB 10 rows. . imported "SCOTT"."BONUS" 0 KB 0 rowsProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/COMMENTProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 12:50:22
阅读全文
0 0
- Oracle EXPDP和IMPDP指令详解
- Oracle Expdp Impdp 详解
- Oracle expdp/impdp详解
- ORACLE expdp/impdp详解
- oracle Expdp和impdp
- oracle expdp/impdp 用法详解
- oracle expdp/impdp 用法详解
- ORACLE EXPDP/IMPDP 参数详解
- oracle expdp/impdp 用法详解
- oracle expdp/impdp 用法详解
- oracle的expdp和impdp
- expdp和impdp使用详解
- EXPDP和IMPDP使用详解
- Oracle expdp/impdp工具使用详解
- oracle导入导出expdp impdp详解
- Oracle expdp/impdp工具使用详解
- expdp / impdp 用法详解(Oracle)
- Oracle expdp/impdp工具使用详解
- [BZOJ]1098: [POI2007]办公楼biu
- 最大间距
- Android 自定义控件之View的绘制流程
- U盘安装Win10,遇到“由于存在受损的安装文件 因此安装无法继续”
- 博客积分规则
- Oracle EXPDP和IMPDP指令详解
- 70后80后90后00后网民研究报告(2017年)
- 存储管理之基本的存储管理
- iOS计算NSMutableAttributedString、NSString指定宽度前提下的高度
- JavaScript 获取当前毫秒时间戳
- 应用程序错误记录windows错误日志
- Django 将数据输出到html后,怎么用?(QuerySet 和Set的区别?)
- 1013. 数素数 (20)
- node和npm安装