exp和imp\expdp和impdp

 

 

 

 

exp和imp，expdp和impdp 这4个工具属于逻辑备份。

exp常用选项

    ROWS=N 能够导出整个数据库的结构，不导出数据，只导出表结构

    一定要设置buffer，exp最好>64000，imp的buffer最好>100000

    查看帮助，exp help=y  显示中文设置 export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

    EXP：有3种主要的方式（完全、用户、表）

    1、完全：执行完全导出，必须具有特殊的权限

    exp system/system buffer=64000 file=/home/oracle/full.dmp full=y

    2、用户模式：这样用户scott的所有对象被输出到文件中

    exp system/system file=/home/oracle/scott.dmp owner=scott

    3、表模式：这样用户scott的表emp就被导出

    exp scott/scott file=/home/oracle/scott.dmp table=(emp)

实验：

    exp scott/tiger file=scott.dmp owner=scott    ---导出scott用户下的所有数据，包含表和视图，同义词，触发器等

    exp scott/tiger file=scott.dmp tables=DEPT,EMP    ---导出scott用户下的两张表，DEPT 和 EMP表

    exp scott/tiger file=scott.dmp tables=DEPT,EMP ROWS=N  ---导出表，只导出表结构，不导出内容

    exp scott/tiger file=scott.dmp tables=(DEPT,EMP)  ---带括号的Windows下使用的，在linux下带括号会报错

    exp sys/oracle buffer=64000 file=full.dmp full=y 

    *.dmp是可以用文本文件打开，看到一些基本的信息的。

imp

    导入表

        imp scott/tiger file=scott.dmp tables=DEPT,EMP   

        imp scott/tiger file=scott.dmp tables=DEPT,EMP ignore=y  ---如果表存在，会报错，加ignore=y，意思是oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违反了约束条件，比如主键约束，则出错的记录不会插入，但合法的记录会添加到表中。

        imp system/oracle file=scott.dmp tables=DEPT,EMP fromuser=scott touser=DEBC --scott用户下的两张表导到DEBC用户下

    导入用户

        imp system/oracle file=zz_jw.dmp fromuser=ZZ_JW touser=BEDC   ---将用户ZZ_JW下的所有数据导入用户BEDC下

    全库导入(导入数据库)

 

 

 

全库导出（不建议全库导出，建议只导出业务用户的数据）

    1、去掉统计信息的导出； 加参数 statistics=none   ---统计信息等导完了，再统计一遍就有了，不需要导出

    2、去掉系统默认用户、只导出业务用户的数据,需要验证系统用户里边是否含有业务数据表（看建表时间）

    3、select * from dba_users a where a.username like '%SYS%' order by  a.created desc   --查询用户，按照创建时间倒序排序

    4、查看系统失效的对象，业务表空间

        确定需要导出的用户在哪些表空间，及其初始化时占用的表空间大小

        确定需要导出的用户中有哪些无效的对象，及总共需要导出的对象数量（后续校验用）

    5、增加buffer参数，direct=y   （设buffer、设direct=y直接路径导出，都是增加导出的速度）

全库导入（不建议全库导入，建议只导入业务用户的数据）

  

1、创建相关的表空间、创建用户

    执行imp system/oracle file=expfull.dmp full=y log=impfull.log  show=y (show=y 显示创建命令信息，执行以下，看日志，在前几行就可以确定需要创建的表空间和用户，然后手动创建)

2、比如需要导入lhr和qiji两个用户的数据

imp system/oracle file=expfull.dmp fromuser=lhr touser=lhr log=impfull.log buffer=50000000 feeback=100000 ignore=y   （50000000B，/1024/1024≈50M）

imp system/oracle file=expfull.dmp fromuser=qiji touser=qiji log=impfull.log buffer=50000000 feeback=100000 ignore=y   （50000000B，/1024/1024≈50M）

3、导入完成后，需要校验相关的业务数据

imp导入常见问题处理

    1、数据库对象已经存在     ----建议加参数ignore=y，同时，导入数据前应该彻底删除目标数据下的表，序列，函数/过程，触发器等

    2、权限不够    ---用sys或者system用户来导入，或赋系统权限imp_full_database

    3、imp和exp使用的字符集不同   ---查看更改NLS_LANG相关信息

    4、imp和exp版本不能往上兼容    ---可以从低版本导入高版本，但不能从高版本导入低版本

从dmp文件可以获取哪些内容

    1、导出的版本、时间、导出的用户(strings zz_jw.dmp |head -10)

    [oracle@rac6n2 ~]$ strings zz_jw.dmp |head -10

    TEXPORT:V11.02.00   ====》版本号

    DZZ_JW   ====》使用zz_jw用户导出的

    RUSERS   ====》基于用户模式导出，RTABLES是基于表模式导出，RENTIRE是基于全库模式导出

    8192

                                            Mon Nov 9 20:16:49 2020zz_jw.dmp   ====》生产的时间和文件地址

    #G#G

    #G#G

    +00:00

    BYTE

    UNUSED

    2、获取dmp文件中的表信息strings zz_jw.dmp |grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'

    [oracle@rac6n2 ~]$ strings zz_jw.dmp |grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'|awk '{ if (FNR==1) print "tables="$1 ; else print ","$1}'

    tables=A

    ,ACT_EVT_LOG

    ,ACT_GE_BYTEARRAY

    ,ACT_GE_PROPERTY

    ,ACT_HI_ACTINST

    ,ACT_HI_ATTACHMENT

    ,ACT_HI_COMMENT

    ,ACT_HI_DETAIL

    ,ACT_HI_IDENTITYLINK

    ,ACT_HI_PROCINST

    ,ACT_HI_TASKINST

    3、如何查看dmp文件的字符集 imp \'/ as sysdba\' table=xxx.xx file=zz_jw.dmp

    [oracle@rac6n2 ~]$ imp \'/ as sysdba\' tables=xxx.xx file=zz_jw.dmp

    Import: Release 11.2.0.3.0 - Production on Tue Nov 10 00:10:12 2020

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

    Data Mining and Real Application Tes

    Export file created by EXPORT:V11.02.00 via conventional path

    Warning: the objects were exported by ZZ_JW, not by you

    import done in US7ASCII character set and AL16UTF16 NCHAR character set

    import server uses ZHS16GBK character set (possible charset conversion)   ====> 当前数据库的字符集

    export client uses ZHS16GBK character set (possible charset conversion)   ====> dmp文件中的字符集

    IMP-00029: cannot qualify table name by owner (xxx.xx), use FROMUSER parameter

    IMP-00000: Import terminated unsuccessfully

    4、如果将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中，那么还需要根据文件修改第4行的第3-4个字节，22#视频<01:04:09>位置

    5、oracle 的 exp工具有一个query参数，可以指定一个where条件来有条件的导出记录，可以配合parfile参数一起使用，用法如下。

    exp scott/tiger file=xxx.dmp tables=emp query=\"where job=\'SALEMAN\' and sal\<1600\" statistics=none  ---这样可以

    exp \'/ as sysdba\' file=xxx.dmp parfile=/tmp/scottfile.par log=/tmp/exp001.log   ----也可以把条件写入scottfile.par文件 用parfile参数

    6、延时段创建,创建空表时不会立即分配段，只有在第一行插入时才会分配段。参数deferred_segment_creation默认是true的。详见22#<01:10:40>

    7、在11gr2中，采用exp导出数据时，不能导出空表，(expdp是可以的)所以要先判断数据库中有没有空表，当有空表时，先设置参数deferred_segment_creation为false，然后使用ALLOCATE EXTENT为每一个空表分配段，最后再导出所需要的数据。

    8、用此语句查出用户下的空表，在执行结果给空表分配段。 SELECT 'ALTER TABLE'||D.OWNER||'.'||D.TABLE_NAME||'ALLOCATE EXTENT;' EXEC_SQL FROM DBA_TABLES D WHERE D.SEGMENT_CREATED='NO' AND D.OWNER IN ('LHR','ABC')   注意修改用户名

 

 

expdp\impdp 

1、帮助 expdp help=y 

2、为数据泵指定文件位置时，需要用到目录对象（数据泵数据文件导出导入只能放在目录对象里）

    查询数据库目录对象 select * from dba_directories;查到的任意一个目录对象都可以用来指定文件位置。常用的是DATA_PUMP_DIR

    可以用sys用户也可以用lhr用户创建数据库目录对象，不管用什么用户创建的，数据库目录对象只属于sys用户

    create directory d2 as '/home/oracle';

    grant all on directory d2 to public;

3、expdp \'/ as sysdba\'  DIRECTORY=DATA_PUMP_DIR  DUMPFILE=jw_zz.dmp FULL=y ESTIMATE_ONLY=y   ----ESTIMATE_ONLY=y只看导出后文件的大小，不执行导出操作

4、exp导出的文件不能用impdp导入    

5、按照用户导出,导入 

        expdp scott/tiger DIRECTORY=D2 dumpfile=SCOTT_expdp.dmp schemas=scott     ---用scott或者system、sys用户都可以

        impdp scott/tiger DIRECTORY=D2 dumpfile=SCOTT_expdp.dmp schemas=scott

6、按照表名进行导出

        expdp system/oracle DIRECTORY=D2 dumpfile=SCOTT_expdp_emp.dmp TABLES=SCOTT.emp,QIJI.A   ---注意用system用户时，导出表，表的写法

7、导出表，加查询条件

        expdp system/oracle DIRECTORY=D2 dumpfile=SCOTT_expdp_emp.dmp TABLES=SCOTT.emp,QIJI.A query='WHERE deptno=20'

8、按表空间导出，导入

        expdp system/oracle DIRECTORY=D2 dumpfile=expdp_tablespace.dmp TABLESPACES=BEDC_DATA

        impdp system/oracle DIRECTORY=D2 dumpfile=expdp_tablespace.dmp TABLESPACES=BEDC_DATA

9、导出整个数据库，导入+

        expdp system/oracle DIRECTORY=D2  full=y ESTIMATE_ONLY=y                    -------预估导出的文件大小

        expdp system/oracle DIRECTORY=D2 dumpfile=expdp_full.dmp full=y 

        impdp system/oracle DIRECTORY=D2 dumpfile=expdp_full.dmp full=y

10、导入表，但是改用户，将scott用户下的表，导入到zz_jw下

        impdp system/oracle DIRECTORY=D2 dumpfile=SCOTT_expdp.dmp TABLES=SCOTT.emp REMAP_SCHEMA=SCOTT:QIJI

如何彻底停止expdp进程？

    使用expdp导出时，不小心按了crtl+C,有输入exit命令，或者网络中断等异常情况，导致expdp进程不存在，但oracle数据库的会话仍存在，dmp文件也一直在增长，这种情况需要彻底停止expdp进程。

    1、检查expdp进程是否存在 ps -ef|grep expdp，若存在，则用kill -9 process 杀掉expdp进程

    2、检查会话，把表drop掉

    SYS@hx1>SELECT * FROM DBA_DATAPUMP_SESSIONS;  ------检查会话

        OWNER_NAME                     JOB_NAME                          INST_ID SADDR            SESSION_TYPE

    ------------------------------ ------------------------------ ---------- ---------------- --------------

    SYSTEM                         SYS_EXPORT_FULL_01                      1 000000008C3A8A08 MASTER

    SYSTEM                         SYS_EXPORT_FULL_01                      1 000000008C45DB50 WORKER

    SYS@hx1> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01 PURGE;        -----把表drop掉

 

导出过滤

导出过滤query、include、exclude

1、之前学的query

2、exclude、include

expdp scott/tiger directory= dumpfile= logfile= schemas=scott exclude=TABLE:\"not like \'b$%\'\"; -----不导出以b$开头的表；

导出job

expdp system/oracle directory=D2 dumpfile=a.dmp full=y include=JBO

impdp system/oracle directory=D2 dumpfile=a.dmp full=y include=JBO SQLFILE=expddl.sql ----看job的创建语句

导出dblink

expdp system/oracle directory=D2 dumpfile=a.dmp full=y include=DBLINK

impdp system/oracle directory=D2 dumpfile=a.dmp full=y include=DBLINK SQLFILE=expddlee.sql ----看job的创建语句

 :

实用总结1：

如何查看dmp文件的ddl语句，就是说在导入之前，看看需要创建哪些表空间、创建哪些用户，imp和impdp不同。执行下面的命令，不会真正执行导入操作，只会看ddl语句

imp system/oracle file=expfull.dmp full=y log=impfull.log show=y

impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhr1202.dmp logfile=imp_test.log sqlfile=xxx.sql

实用总结2：

1、exp和imp导出导入更多的模式是用户模式

导出：exp system/oracle file=expfull.dmp owner=scott

导入：imp system/oracle file=expfull.dmp fromuser=lhr touser=lhr log=impfull.log buffer=50000000 feeback=100000 ignore=y

2、全库导出更多用来获取dmp文件中的表空间和用户

imp system/oracle file=expfull.dmp full=y log=impfull.log  show=y

3、表模式更多用于通过dmp文件查看导出导入数据库的字符集

imp \'/ as sysdba\'  file=zz_jw.dmp table=xxx.xx