导入导出工具01–EXP/IMP和EXPDP/IMPDP工具的使用

Posted on by
EXP/IMP和EXPDP/IMPDP工具的使用
  1. exp/imp:
    1. 概念:
      1. 早期的oracle数据导入导出工具,效率比较低,可以用在小数据量的情况下;
      2. 可以在服务器端或者客户端操作,数据可以存放在服务器端或者客户端;
      3. 只能导出导出二进制的dump文件;
    2. 使用exp/imp工具的前提:
      1. 运行catexp.sql或者catalog.sql脚本;
      2. 保证有足够的空间;
      3. 保证有足够的权限;
        1. 对于exp:必须有CREATE SESSION权限,如果要导出其它用户下的表,还需要有EXP_FULL_DATABASE角色;
        2. 对于imp:必须有CREATE SESSION权限,如果要导入其它用户创建的dump文件,还需要有IMP_FULL_DATABASE角色;
    3. 调用exp/imp的方法:
      1. 命令行接口,常用;
      2. 交互模式;
      3. 使用参数文件方式,常用;
      4. 使用OEM调用;
    4. EXP工具:
      1. 用途:
        1. 归档历史数据;
        2. 保存表的结构信息;
        3. 可以在不同的服务器,不同的平台上传输数据;
        4. 传输表空间时使用;
      2. 导出的四种模式:
        1. Table Mode:包括表的定义,数据,用户拥有的表和索引,表上的约束;
        2. User Mode:用户下所有的对象;
        3. Tablespace Mode:某一个表空间中所有的对象,主要用于传输表空间;
        4. Full Database Mode:不推荐的方式,可以使用rman替代;
      3. 语法:exp keyword=value1,value2,…,valuen;
        1. USERID=user/pwd:用户名和密码;
        2. BUFFER:指定数据缓冲区的大小;
        3. FILE:导出文件的名称;
        4. FILESIZE:每个导出文件的最大大小,如果导出文件很大,可以限制每个文件的大小,使用多个文件来保存数据;
        5. COMPRESS:是否使用压缩,默认为Y;
        6. GRANTS:是否导出用户的权限,默认为Y;
        7. ROWS:是否导出表的记录,默认为Y;
        8. INDEXES:是否导出索引,默认为Y;
        9. TRIGGERS:是否导出触发器,默认为Y;
        10. CONSTRAINTS:是否导出表上的约束,默认为Y;
        11. DIRECT:是否使用直接路径导出,默认为N,只能使用命令行方式,BUFFER参数无效;
        12. LOG:指定日志文件;
        13. PARFILE:指定参数文件;
        14. FULL:导出整个数据库,全库导出时使用,不常用;
        15. OWNER:列出要导出的用户,导出用户模式;
        16. TABLES:列出要导出的表的名称(schema.table:partition),支持通配符,导出表模式;
        17. TABLESPACES:列出要导出的表空间,导出表空间模式;
        18. TRANSPORT_TABLESPACE:是否导出表空间的元信息,默认为N,传输表空间时使用;
        19. FEEDBACK=n:显示导出的进度,n条记录打一个点;
        20. QUERY:使用查询语句来过滤要导出的记录,eg:exp scott/tiger TABLES=emp QUERY=\”WHERE job=\’SALESMAN\’ and sal \<1600\”;
        21. CONSISTENT:是否在导出开始时设置SET TRANSACTION READ ONLY语句来保证整个导出期间的数据一致性,默认为N,推荐为Y;
        22. OBJECT_CONSISTENT:保证导出某个对象期间,这个对象是只读的,默认为N,推荐为Y;
        23. FLASHBACK_SCN:从指定的SCN开始导出数据;
        24. FLASHBACK_TIME:从指定的时间开始导出数据,内部会转化为SCN号码,可以使用TO_TIMESTAMP函数来转化字符串格式的时间;
        25. STATISTICS:当导出的对象被导入时,是否分析对象,默认为ESTIMATE;
        26. RESUMABLE:当空间不足时是否会挂起,等待分配空间后继续导出,默认是N;
        27. RESUMABLE_NAME:指定挂起语句的标识符;
        28. RESUMABLE_TIMEOUT:等待的时间;
        29. RECORDLENGTH:记录的长度;
        30. INCTYPE:增量导出类型;
        31. RECORD:跟踪增量导出,默认为Y;
    5. IMP工具:
      1. 用途:
        1. 可以执行完全导入或者累计导入;
        2. 主要用于恢复用户错误;
      2. 导入介绍:
        1. 先创建表的定义;
        2. 然后从exp的dmp文件中抽取数据;
        3. 创建索引;
        4. 导入触发器;
        5. 开启数据完整性约束检查;
        6. 创建位图索引,序列等对象;
      3. 导入的模式:
        1. 表模式;
        2. 用户模式;
        3. 表空间模式,用户可传输表空间;
        4. 全库模式,不常用;
      4. 语法:imp keyword=value1,value2,…,valuen;
        1. USERID=user/pwd:用户名和密码;
        2. BUFFER:指定数据缓冲区的大小;
        3. FILE:要导入的文件的名称;
        4. FILESIZE:每个导入文件的最大大小;
        5. SHOW:查看要导入的dmp文件中的内容,并不进行导入操作;
        6. IGNORE:是否忽略创建对象时的错误,默认为N,表示出错时输出错误日志;
        7. GRANTS:是否导入用户的权限,默认为Y;
        8. ROWS:是否导入表的记录,默认为Y;
        9. INDEXES:是否导入索引,默认为Y;
        10. TRIGGERS:是否导入触发器,默认为Y;
        11. CONSTRAINTS:是否导入表上的约束,默认为Y;
        12. LOG:指定日志文件;
        13. PARFILE:指定参数文件;
        14. FULL:导出整个数据库,全库导出时使用,不常用;
        15. FROMUSER:在用户模式下使用,指定要导入的用户;
        16. TOUSER:与FROMUSER连用,指定要转变的用户列表,如fromuser=u1 touser=u2,即把u1下的对象创建在u2下;这个用户必须预先存在,否则会报错;
        17. TABLES:列出要导入的表的名称(schema.table:partition);
        18. TABLESPACES:列出要导入的表空间,导出表空间模式;
        19. TRANSPORT_TABLESPACE:是否导入表空间的元信息,默认为N,传输表空间时使用;
        20. FEEDBACK=n:显示导入的进度,n条记录打一个点;
        21. STATISTICS:导入对象时,是否分析对象,默认为AWAYS;
        22. RESUMABLE:当空间不足时是否会挂起,等待分配空间后继续导出,默认是N;
        23. RESUMABLE_NAME:指定挂起语句的标识符;
        24. RESUMABLE_TIMEOUT:等待的时间;
        25. RECORDLENGTH:记录的长度;
        26. INCTYPE:增量导入类型;
        27. COMMIT:指定是否导入一组数据后就提交,默认为N,表示每导入一个表提交一次;
        28. DESTROY:是否覆盖表空间的数据文件,默认为N;
        29. INDEXFILE:把表和索引写入指定的文件中;
        30. SKIP_UNUSABLE_INDEXES:跳过不可用索引维护;
        31. TOID_NOVALIDATE:跳过指定类型ID的验证;
        32. COMPILE:是否马上编译导入的包,存储过程和函数,默认为Y;如果为N,表示第一次使用时编译;
        33. STREAMS_CONFIGURATION:导入stream的一般元数据,默认为Y;
        34. STREAMS_INSTANTIATION:导入stream的实例元数据,默认为N;
        35. DATAFILES:当TRANSPORT_TABLESPACE=Y时,使用此参数传输导入与数据库的数据文件;
    6. 字符集转换:
      1. 数据从源数据库导出,传输到目标服务器(ftp/scp/tape),然后导入到目标数据库一共有四个环境的字符集:
        1. 源数据库的字符集和NCHAR字符集;
        2. 源数据库服务器导出时的字符集(通过NLS_LANG环境变量设置);
        3. 目标数据库服务器导入时的字符集(通过NLS_LANG环境变量设置);
        4. 目标数据库的字符集和NCHAR字符集;
        5. 源数据库和目标数据库的字符集相同,可能会发生3次字符集转换(导出,传输,导入),如果不相同,一定会发生转换或者导入错误;
      2. 查看数据库的字符集:select * from nls_database_parameters,可以看得出语言是AMERICAN,区域是AMERICA,字符集是WE8ISO8859P1,NCHAR是AL16UTF16;            
      3. 设置服务器的环境变量:export NLS_LANG=NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET;                    
      4. 查看现有的dmp文件的字符集:首先设置当前服务器的字符集与数据库字符集一致,然后使用imp show=y命令,可以查看到导出时的字符集;                      
      5. 字符集换换的拓扑图;                                                      
    7. 传输表空间的原理:
      1. 如果把数据导出再导出效率太低;
      2. 传输表空间的做法是:只导出表空间的元信息,应用到目标数据库,表空间的数据使用数据块拷贝的方式;
  2. expdp/impdp:
    1. 概念:
      1. 带有数据泵的oracle数据导入导出工具,比较高效;
      2. 只能在服务器端操作,因为需要使用DIRECTORY,数据只能存放在服务器;
      3. 只能导出导出二进制的dump文件;
      4. 主要是调用DBMS_DATAPUMP的包;
    2. expdp:
      1. 语法:expdp user/pwd key=value;
        1. COMPRESSION:压缩数据;
          1. METADATA_ONLY:默认值,只压缩元数据;
          2. NONE:不压缩;
        2. CONTENT:指定要导出的内容;
          1. ALL:导出对象的定义和数据;
          2. DATA_ONLY:导出对象的数据;
          3. METADATA_ONLY:导出对象的定义;
        3. DIRECTORY:用于存放dump文件和日志文件的目录对象;
        4. DUMPFILE:导出到dump文件的列表,格式为:[DIRECTORY:]file_name,如果不指定目录,则使用DIRECTORY参数指定的目录对象;
        5. FILESIZE:导出文件的最大大小,如果导出对象比较大,可以分成多个文件保存;
        6. LOGFILE:指定日志文件的名称;
        7. NOLOGFILE:默认为N,禁止生成日志文件;
        8. PARFILE:指定参数文件;
        9. ENCRYPTION_PASSWORD:指定加密列的密码;
        10. ESTIMATE:指定估算被导出表所占磁盘空间的方法;
          1. BLOCKS:默认值,按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间;
          2. STATISTICS:据最近统计值估算对象占用空间;
        11. ESTIMATE_ONLY:默认值为N,如果为Y,则只估算导出作业占用的磁盘空间而不会执行;
        12. INCLUDE:指定要包含的对象类型和对象,格式:object_type[:name_clause];
          1. name_clause:必须是用双引号引起来的,因为里面的每个对象需要使用单引号;
          2. 使用LIKE子句:include=TABLE:”LIKE ‘B%'”,但是不能使用NOT LIKE;
          3. 使用IN子句:include=TABLE:”IN (‘T1’, ‘T2’)”;
          4. 使用<>子句:include=TABLE:”<> (‘T1’)”,但是不能使用[=];
          5. 只导出存储过程:indlude=PROCEDURE;
        13. EXCLUDE:排除指定的对象类型或者对象,格式与INCLUDE相似;
        14. FLASHBACK_SCN:指定SCN以后的数据;
        15. FLASHBACK_TIME:指定某个时间以后的数据,内部转化为最近的SCN号;
        16. JOB_NAME:把导出的任务创建一个JOB,指定作业的名称;
        17. ATTACH:使当前会话与已存在的导出作业建立联系,然后执行交互命令,eg:expdp user/pwd ATTACH=[schema_name.]job_name;
        18. NETWORK_LINK:指定目标数据库到源数据库的Database Link,用于将远程数据对象导出到本地的情况;
          1. 因为expdp是服务器端工具,如果expdp user/pwd@net_name的话导出的文件存放在服务器端;
          2. 如果expdp user/pwd NETWORK_LINK=dblink_name的话,则数据导出到本地;
          3. 导出的日志文件不能放到ASM上,只能存放数据文件;
        19. PARALLEL:指定导出的并行度,默认为1,推荐并行度等于给定的文件个数的整数倍,而且要小于CPU个数*2;
        20. QUERY:指定过滤导出数据的条件,格式:[schmea].[table_name:]query_clause;
        21. SAMPLE:导出数据的百分比;
        22. FULL:默认为N,数据库全库导出;
        23. SCHEMAS:导出数据的schema列表,默认为当前schema;
        24. TABLES:指定要导出的表的列表,格式为:[schema_name.]table_name[:partition_name];
        25. TABLESPACES:指定要导出的表空间;
        26. TRANSPORT_FULL_CHECK:默认为Y,检查要传输的表空间是否是自包含的;
        27. TRANSPORT_TABLESPACES:指定要要导出元数据的表空间的列表,主要用于传输表空间的情况;
        28. VERSION:指定导出对象的数据库的版本;
          1. COMPATIBLE:默认值;
          2. LATEST:会根据数据库的实际版本生成对象元数据;
          3. version_string用于指定数据库版本字符串;
        29. STATUS:用于指定显示当前导出作业执行状态的间隔,eg:STATUS[=interval],默认为0;
      2. 交互式命令:
        1. ADD_FILE:添加一个导出文件;
        2. FILESIZE:指定添加文件的文件大小;
        3. HELP:帮助;
        4. PARALLEL:改变导出作业的并行度,PARALLEL=<number of workers>;
        5. 进入expdp的作业会话:expdp user/pwd attch=schema.job_name;
        6. START_JOB:开始/恢复作业的执行;
        7. STOP_JOB:暂停作业,使用STOP_JOB=IMMEDIATE表示立即停止;
        8. EXIT_CLIENT(CTRL+C):退出当前session,让作业继续运行;
        9. CONTINUE_CLIENT:切换到日志模式,作业在空闲时会重新运行;
        10. KILL_JOB:断开与作业的连接,并停止作业,同时删除dmp文件;
        11. STATUS:用于指定显示当前导出作业执行状态的间隔,eg:STATUS[=interval],默认为0;
      3. 导出的步骤:
        1. 创建目录对象:CREATE DIRECTORY ext_tab_dir as ‘/u01/datafiles/’;                          
        2. 授权:GRANT READ, WRITE ON DIRECTORY ext_tab_dir TO hr;                                   
        3. 执行导出:expdp hr/hr directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;(如果不指定JOB_NAME的话,会生成一个系统的名称,推荐指定;)                 
      4. 导出的各种场景:
        1. 按表模式导出:expdp hr/hr directory=ext_tab_dir tables=jobs,countries dumpfile=expdp_table1.dmp,expdp_table2.dmp logfile=expdp_table.log parallel=2 job_name=’job_expdp_table’;
        2. 按查询条件导出:expdp hr/hr directory=ext_tab_dir tables=jobs query='”where rownum<11″‘ dumpfile=expdp_hr_jobs.dmp logfile=expdp_hr_jobs.log job_name=’job_expdp_hr_jobs’;
        3. 按表空间导出:expdp system/oracle directory=ext_tab_dir tablespaces=USERS dumpfile=expdp_ts_users1.dmp,expdp_ts_users2.dmp logfile=expdp_ts_users.log parallel=2 job_name=’job_expdp_ts_users’;
        4. 导出方案:expdp hr/hr directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;
        5. 导出整个数据库:expdp hr/hr directory=ext_tab_dir dumpfile=full.dmp full=y logfile=full.log job_name=’job_expdp_full’;
    3. impdp:
      1. 语法:impdp user/pwd key=value;
        1. REMAP_DATAFILE:重新定义DDL语句中引用的数据文件,用于将源数据文件转变为目标数据文件名,在不同平台之间搬移表空间时使用,eg:REMAP_DATAFIEL=source_datafie:target_datafile;
        2. REUSE_DATAFILES:默认是N,指定是否覆盖已存在的数据文件;
        3. REMAP_SCHEMA:将源schema的对象转换到目标schema中,eg:REMAP_SCHEMA=source_schema:target_schema;
        4. REMAP_TABLESPACE:将源表空间的对象导入到目标表空间中,eg:REMAP_TABLESPACE=source_tablespace:target_tablespace;
        5. SKIP_UNUSABLE_INDEXES:跳过不可使用的索引,默认为N;
        6. SQLFILE:把导出的dump文件中所有对象的DDL语句写到sql脚本中,eg:SQLFILE=[directory_object:]file_name;
        7. STREAMS_CONFIGURATION:是否导入流元数据库,默认是Y;
        8. TABLE_EXISTS_ACTION:当表已经存在时,导入作业要执行的操作;
          1. SKIP:默认跳过;
          2. APPEND:追加数据;
          3. REPLACE:删除表重建,然后再插入;
          4. TRUNCATE:截断;
        9. TRANSFORM:指定是否修改建立对象的DDL语句,eg:TRANSFORM=transform_name:value[:object_type];
          1. Transform_name:用于指定转换名:
            1. SEGMENT_ATTRIBUTES用于标识段属性(物理属性,存储属性,表空间,日志等信息);
            2. STORAGE用于标识段存储属性;
            3. OID;
            4. PCTSPACE;
          2. value:用于指定是否包含段属性或段存储属性;
          3. object_type用于指定对象类型;
        10. TRANSPORT_DATAFILES:用户指定传输表空间时被导入表空间对应的数据文件;
        11. TRANSPORT_TABLESPACES:列出传输表空间的元数据的表空间的列表,只是在NETWORK_LINK模式下操作;
      2. 交互式命令:与expdp一致;
    4. 导入的各种场景:
      1. 按表导入,将hr用户下俩个表导入到scott用户下:impdp hr/hr directory=ext_tab_dir tables=jobs,countries dumpfile=expdp_table1.dmp,expdp_table2.dmp remap_schema=hr:scott logfile=impdp_table.log parallel=2 job_name=’job_impdp_table’;
      2. 按用户导入(如果用户存在则直接导入,要有目录对象的权限,如果不存在使用dba导入):impdp system/oracle directory=ext_tab_dir schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=impdp_hr.log parallel=2 job_name=’job_impdp_hr’;
      3. 在客户端执行expdp命令把导出文件存放在服务器端;
      4. 在客户端执行expdp命令把导入文件存放在客户端;
      5. 源数据库的数据直接导入到目标数据库,中间不产生任何的dump文件;
——————————— 查看DIRECTORY对象的权限 ———————————
SELECT d.directory_name, d.directory_path, p.grantee, p.privilege
FROM user_tab_privs p
INNER JOIN dba_directories d ON p.table_name = d.directory_name AND
d.directory_name = ‘EXT_TAB_DIR’;
——————————— 查看DIRECTORY对象的权限 ———————————
——————————— exp的例子 ———————————
— 导出scott用户下的所有对象:exp scott/tiger owner=scott file=/u01/exp/exp_scott.dmp log=exp_scott.log feedback=1000;(导出的字符集和当前环境的字符集不同,可能发生字符集转换;)
— 导出hr用户下的jobs,countries表:exp hr/hr tables=jobs,countries file=/u01/exp/exp_hr_tables.dmp log=/u01/exp/exp_hr_tables.log feedback=100;(导出的过程中出现警告,其实是因为字符集的问题;)
— 设置字符集导出,没有提示字符集可能发送转换,也没有警告;
——————————— exp的例子 ———————————
——————————— imp的例子 ———————————
— 把某个用户下的数据导入;
— 首先删除用户下所有的对象,然后创建用户(导入时此用户必须存在):drop user scott cascade;;
— 导入数据:imp scott/tiger file=/u01/exp/exp_scott.dmp fromuser=scott touser=scott;
— 把hr用户下的exp_jobs表导入到scott用户下(必须使用dba用户才可以完成):imp system/oracle file=/u01/exp/exp_jobs.dmp fromuser=hr touser=scott tables=exp_jobs feedback=100;
——————————— imp的例子 ———————————
——————————— NETWORK_LINK参数的测试 ———————————
— 1.配置两台数据库的tnsnames.ora,其中orcl_source指向源数据库,orcl_target指向目标数据库;
— 2.在源数据库添加一个目录对象dir_source,并授权给hr用户;
CREATE DIRECTORY dir_source AS ‘/u01/dump_source’;
GRANT READ, WRITE ON DIRECTORY dir_source TO hr;
— 3.使用网络名在客户端调用expdp命令,导出的文件存放在服务器端(用户必须要有CONNECT, RESOURCE, EXP_FULL_DATABASE角色和目录对象的读写权限即可);
expdp hr/hr@orcl_source directory=dir_source schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;
— 4.在源数据库添加一个目录对象dir_target,并授权给hr用户;
CREATE DIRECTORY dir_target AS ‘/u01/dump_target’;
GRANT READ, WRITE ON DIRECTORY dir_target TO hr;
— 6.在目标端创建服务器端的Database Link;
CREATE PUBLIC DATABASE LINK dblink_source CONNECT TO hr identified by hr USING ‘ORCL_SOURCE’;
— 5.在目标服务器使用expdp命令,把导出的数据放到目标端;
expdp hr/hr network_link=dblink_source directory=dir_target schemas=hr dumpfile=expdp_hr1.dmp,expdp_hr2.dmp logfile=expdp_hr.log parallel=2 job_name=’job_expdp_hr’;
— 直接从源数据库导入到目标数据库,中间不生成任何的dump文件;
impdp system/oracle network_link=dblink_source directory=dir_target schemas=hr logfile=exp2imp.log parallel=2 job_name=’job_exp2imp’
——————————— NETWORK_LINK参数的测试 ———————————
——————————— INCLUDE/SQLFILE参数的测试 ———————————
— 导出HR用户下所有的PROCEDURE,SEQUENCE,TABLE对象;
expdp hr/hr directory=dir_source schemas=hr include=PROCEDURE,SEQUENCE,TABLE dumpfile=expdp_hr_part_objects.dmp logfile=expdp_hr_part_objects.log job_name=’job_expdp_hr_objects’;
— 导出dump文件中包含对象的DDL语句;
impdp system/oracle directory=dir_source dumpfile=expdp_hr_part_objects.dmp sqlfile=hr_objects.sql;
— 查看DDL语句:less /u01/dump_source/hr_objects.sql;
——————————— INCLUDE/SQLFILE参数的测试 ———————————

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注