Oracle数据库的导入导出

it2022-05-05 121

文章目录

1. exp 命令2. imp 命令1.expdp 命令2.impdp 命令帮助文档exp 帮助文档：imp 帮助文档expdp 帮助文档impdp 帮助文档

1. exp 命令

作用：用于数据导出格式：

本地数据库导出： exp 用户名/密码 file=数据导出的路径 log=数据导出的日志文件存放路径 tables=(该用户下的表名多个用逗号隔开) 例如：导出本地数据库scott用户下的dept表： exp scott/scott file=/home/oracle/dept.dmp log=/home/oracle/dept.log tables=dept

远程数据库导出： exp 用户名/密码@远程数据库ip地址:端口/数据库实例名（sid）例如：导出远程（192.168.1.25）数据库scott用户的emp，dept表 exp scott@192.168.1.25:1521/orcl file=/home/oracle/scott.dmp log=/home/oracle/scott.log tables=(emp,dept)

2. imp 命令

作用：用于数据库导入格式：

用户相同导入数据 imp 用户名/密码(导入用户要和dmp包原用户名字权限相同) file=dmp包存放路径 log=导入过程日志文件路径 data_only=y(只导入数据) tables=(表1，表2，…) 例如：向test用户导入emp，dept表的数据 imp test/test file=/home/oracle/scott.dmp tables=(emp,dept)不同用户之间导入数据 imp 用户名/密码@数据库监听名（数据库ip地址） fromuser=dmp包中原用户名 touser=现数据库用户名 file=dmp包存放位置例如： imp scott@ORCL_192.168.1.2 fromuser=scott touser=test file=/home/oracle/scott.dmp

1.expdp 命令

作用：用于数据库导出格式： expdp 用户名/密码@ip:port/数据库实例名 dumpfile=scott.dmp(导出文件名) directory=目录名（导出文件所在的目录名，目录是oracle中的directory对象） SCHEMAS=用户名 tables=(表名,…) logfile=日志文件名例如：导出scott用户下的所有表和数据 expdp scott/scott dumpfile=scott.dmp direcotry=filepath schemas=scott

2.impdp 命令

作用：用于数据库导入格式： impdp 用户/密码@ip:port/数据库实例名 dumpfile=导出文件名 directory=目录名 schemas=(用户,…) tables=(tab1,tab2…) remap_schema=用户1：用户2(将用户1的对象导入到用户2下) logfile=日志文件名例如：将scott用户下的dept表导入到system用户下 impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system

帮助文档

exp 帮助文档：

Export: Release 11.2.0.1.0 - Production on Wed Jul 17 23:27:29 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. You can let Export prompt you for parameters by entering the EXP command followed by your username/password: Example: EXP SCOTT/TIGER Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords: Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR) or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output files (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) DIRECT direct path (N) TRIGGERS export triggers (Y) LOG log file of screen output STATISTICS analyze objects (ESTIMATE) ROWS export data rows (Y) PARFILE parameter filename CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y) OBJECT_CONSISTENT transaction set to read only during object export (N) FEEDBACK display progress every x rows (0) FILESIZE maximum size of each dump file FLASHBACK_SCN SCN used to set session snapshot back to FLASHBACK_TIME time used to get the SCN closest to the specified time QUERY select clause used to export a subset of a table RESUMABLE suspend when a space related error is encountered(N) RESUMABLE_NAME text string used to identify resumable statement RESUMABLE_TIMEOUT wait time for RESUMABLE TTS_FULL_CHECK perform full or partial dependency check for TTS VOLSIZE number of bytes to write to each tape volume TABLESPACES list of tablespaces to export TRANSPORT_TABLESPACE export transportable tablespace metadata (N) TEMPLATE template name which invokes iAS mode export

imp 帮助文档

Import: Release 11.2.0.1.0 - Production on Wed Jul 17 23:54:27 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. You can let Import prompt you for parameters by entering the IMP command followed by your username/password: Example: IMP SCOTT/TIGER Or, you can control how Import runs by entering the IMP command followed by various arguments. To specify parameters, you use keywords: Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL import entire file (N) BUFFER size of data buffer FROMUSER list of owner usernames FILE input files (EXPDAT.DMP) TOUSER list of usernames SHOW just list file contents (N) TABLES list of table names IGNORE ignore create errors (N) RECORDLENGTH length of IO record GRANTS import grants (Y) INCTYPE incremental import type INDEXES import indexes (Y) COMMIT commit array insert (N) ROWS import data rows (Y) PARFILE parameter filename LOG log file of screen output CONSTRAINTS import constraints (Y) DESTROY overwrite tablespace data file (N) INDEXFILE write table/index info to specified file SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N) FEEDBACK display progress every x rows(0) TOID_NOVALIDATE skip validation of specified type ids FILESIZE maximum size of each dump file STATISTICS import precomputed statistics (always) RESUMABLE suspend when a space related error is encountered(N) RESUMABLE_NAME text string used to identify resumable statement RESUMABLE_TIMEOUT wait time for RESUMABLE COMPILE compile procedures, packages, and functions (Y) STREAMS_CONFIGURATION import streams general metadata (Y) STREAMS_INSTANTIATION import streams instantiation metadata (N) DATA_ONLY import only data (N) VOLSIZE number of bytes in file on each volume of a file on tape The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE import transportable tablespace metadata (N) TABLESPACES tablespaces to be transported into database DATAFILES datafiles to be transported into database TTS_OWNERS users that own data in the transportable tablespace set

expdp 帮助文档

Export: Release 11.2.0.1.0 - Production on Thu Jul 18 00:11:20 2019 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 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. ATTACH Attach to an existing job. For example, ATTACH=job_name. COMPRESSION Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE. 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. ESTIMATE Calculate job estimates. Valid keyword values are: [BLOCKS] and STATISTICS. ESTIMATE_ONLY Calculate job estimates without performing the export. 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 [N]. HELP Display Help messages [N]. INCLUDE Include specific object types. For example, INCLUDE=TABLE_DATA. JOB_NAME Name of export job to create. LOGFILE Specify log file name [export.log]. NETWORK_LINK Name of remote database link to the source system. NOLOGFILE Do not write log file [N]. 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 [N]. SAMPLE Percentage of data to be exported. SCHEMAS List of schemas to export [login schema]. 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 [N]. 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. ------------------------------------------------------------------------------ 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 [N]. 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.

impdp 帮助文档

Import: Release 11.2.0.1.0 - Production on Thu Jul 18 00:13:14 2019 Copyright (c) 1982, 2009, 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. ATTACH Attach to an existing job. For example, ATTACH=job_name. CONTENT Specifies data to load. Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY. DATA_OPTIONS Data layer option flags. Valid keywords are: SKIP_CONSTRAINT_ERRORS. DIRECTORY Directory object to be used for dump, log and sql files. DUMPFILE List of dumpfiles 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. ESTIMATE Calculate 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 [Y]. HELP Display help messages [N]. INCLUDE Include specific object types. For example, INCLUDE=TABLE_DATA. JOB_NAME Name of import job to create. LOGFILE Log file name [import.log]. NETWORK_LINK Name of remote database link to the source system. NOLOGFILE Do not write log file [N]. 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 datafile 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=EMP.EMPNO:REMAPPKG.EMPNO. REMAP_TABLESPACE Tablespace object are remapped to another tablespace. REUSE_DATAFILES Tablespace will be initialized if it already exists [N]. SCHEMAS List of schemas to import. 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 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: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE. 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 datafiles to be imported by transportable mode. TRANSPORT_FULL_CHECK Verify storage segments of all tables [N]. 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. ------------------------------------------------------------------------------ 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.

专利

最新回复(0)