oracle学习笔记----数据库导入导出

一、导入和导出  

    Oracle的备份是oracle操作中常见的工作，常见的备份方案包括有：

        1、逻辑备份（IMP&EXP命令进行备份）

            有如下四种模式：

               （1）表空间备份（tablespace）

               （2）表备份（table）

               （3）用户备份（user）

               （4）完全备份（full）

        2、物理文件备份（脱机及联机备份

        3、利用RMAN（Recovery Manager）的增量物理文件系统备份

    oracle的逻辑备份是使用IMP&EXP命令进行数据导入导出操作。使用EXP命令导出或者使用IMP命令导入时，需要Create Session系统权限，但是如果要导出其他的表，必须拥有权限：EXP_FULL_DATABASE.

    调用导入导出命令时，首先要估计所需的空间。EXP命令导出的文件时二进制文件（*.dmp）只能由对应的IMP命令进行读取恢复。导入导出的用途是：

        ·备份与恢复

        ·Oracle平台更换：可以在相同版本之间进行备份与恢复，Oracle较低版本的export数据文件可以import到高版本的Oracle数据库中，但是Oracle的版本只能是相邻的，不能跨版本。

        ·重组表

        ·在不同数据库用户间移动数据

        ·在不同数据库之间移动数据表空间

        ·将表的定义存入二进制的操作系统文件以防止用户操作失误造成数据丢失。

    需要指出的是逻辑备份是不能对数据库进行完全恢复的，即数据的丢失时在所难免的。那么逻辑备份会丢失多少数据呢？从导出开始到导入为止，这段时间之内的数据将全部丢失，这对银行、证券交易所和电信等行业的数据库是绝对不能接受的。所以对于绝大多数真正的生产或商业数据库，逻辑备份永远是不能作为备份和恢复策略的基石，它们必须要有物理备份以保证全恢复，而逻辑备份只能作为辅助手段。

二、EXP导出数据

    EXP命令可以在交互环境下导出数据库中的数据，也可以在非交互环境下执行命令。交互环境下的命令执行，是一步一步执行的过程。

    1.交互式导出

    图中一些代码的解析：（1）Exp是导出命令，该命令后面紧跟“用户名/密码@服务器网络连接”
    （2）Exp程序导出时使用的缓冲区大小，缓冲区越大，导出速度越快。直接回车代表使用默认值4096B。

    （3）Exp命令会把所有要导出的数据导出到一个Dmp文件中，该步骤是Exp询问导出的数据文件名称。

    （4）Exp程序询问导出整个用户还是导出某个表。默认导出整个用户。

    （5）Exp程序询问是否导出每张表的访问权限。默认导出访问权限。

    （6）Exp程序询问是否导出表中的数据。默认导出数据库表中的数据。

    （7）Oracle表中的数据可能来自不同的分区中的数据块，默认导出时会把所有的数据压缩在一个数据块上，IMP导入时，如果不存在连续一个大数据块，则会导入失败。

    2.非交互式导出

    也可以在使用Exp命令时，一次性设置好各种参数，是准备就绪的Exp命令不需要与用户交互，按照参数的要求，Exp命令会一次性执行导出工作。要指定参数，您可以使用关键字

    EXP KEYWORD=value 或 KEYWORD=（value1,value2,...valueN）

    例如：EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

    参数名                                        说明

    USERID                    表示“用户名/密码”

    BUFFER                    数据缓冲区大小。以字节为单位，一般在64000以上

    FILE                      指定输出文件的路径和文件名，一般以.dmp为后缀名，注意该文件包

                              括完整路径，但是路径必须存在，导出命令不能自动创建路径。

    COMPRESS                  是否压缩导出，默认yes

    GRANTS                    是否导出权限，默认yes

    INDEXES                   是否导出索引，默认yes

    DIRECT                    是否直接导出，默认情况，数据先经过Oracle的数据缓冲区，然后再

                              导出数据。

    LOG                       指定导出命令的日志所在的日志文件的位置

    ROWS                      是否导出数据行，默认导出所有数据

    CONSTRAINTS               是否导出表的约束条件，默认yes

    PARFILE                   可以把各种参数配置为一个文本键值形式的文件，该参数可以指定参                                数文件的位置

    TRIGGERS                  是否导出触发器，默认值是yes

    TABLES                    表的名称列表，导出多个表可以使用逗号隔开

    TABLESPACES               导出某个表空间的数据

    Owner                     导出某一个用户的数据

    Full                      导出数据库的所有数据。默认值是no

    QUERY                     把查询的结果导出

三、导入数据

    IMP程序导入就是把exp导出的文件重新导入到数据库的过程。导入时也有一些重要的参数。

    >>>Fromuser:指出导出时dmp文件中记载的用户信息。

    >>>Touser:dmp文件导入到什么目标用户中。

    >>>Commit:默认是N,在缓冲区满时是否需要commit，如果设为N，需要较大的回滚段。

    >>>Ignore：Oracle在恢复数据的过程中，当恢复某个表时，该表已经存在，就要根据ignore参数的设置来觉得如何操作。若ignore=y,Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，比如主键约束，则出错的记录不会插入，但合法记录会添加到表中。如ignore=n,Oracle不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续回复下一个表。

    D:\>imp system/manager file=employee.dmp fromuser=scott touser=employee commit=y

四、常见问题

    ·数据对象已经存在

    一般情况，导入数据前应该彻底删除目标数据下的表，序列，函数/过程，触发器等。

    数据库对象已经存在，按缺省的imp参数，则会导入失败。

    如果用了参数ignore=y,会把exp文件内的数据内容导入。

    如果表没有唯一关键字的约束条件，将引起记录重复。

    ·数据库对象有主外键约束

    不符合主外键约束时，数据会导入失败。

    解决办法：先导入主表，再导入依存表。

    disable目标导入对象的主外键约束，导入数据后，再enable他们。

    ·权限不够

    如果要把A用户的数据导入B用户下，A用户需要由imp_full_datebase权限。

    ·导入大表（大于80M）时，存储分配失败

    默认的EXP时，compress=Y，也就是把所有的数据压缩在一个数据块上。

    导入时，如果不存在连续一个大数据块，则会导入失败。

    导出80M以上的大表时，记得compress=N，则不会引起这种错误。

    ·imp和exp使用的字符集不同

    如果字符集不同，导入会失败，可以改变unix环境变量或者NT注册表里NLS_LANG相关信息。

    ·imp和exp版本上不能往上兼容

    imp可以成功导入低版本exp生成的文件，不能导入高版本exp生成的文件根据情况我们使用。

五、导入和导出实例   

set line 120;    //格式化显示，更清晰
set pagesize 30;    //格式化显示，更清晰
create table emp_dump as select * from emp;    /创建名为emp_dump的表
create table dept_dump as select * from dept;    /创建名为emp_dump的表
select count(*) from emp_dump;    /验证
select count(*) from dept_dump;    /验证

 当确认这两个表创建成功后还应创建一个存放逻辑备份文件的目录f:\export，现在就可以开始做逻辑备份的实验了。在具体操作之前，先简单介绍实验的原理和目的。首先，对刚刚创建的两个表做一个逻辑备份，之后对其中的一个表进行DML操作并立即提交这些操作。接下来，将这两个表破坏掉。最后，再用所做的逻辑备份进行数据恢复以检验究竟能恢复多少数据。

exp scott/tiger file=f:\export\scott.dmp tables=(emp_dump,dept_dump)
select * from emp_dump;
update emp_dump set job='CEO';
commit;
select * from emp_dump;检查修改是否成功
drop table emp_dump;
drop table dept_dump;
select * from emp_dump;
select * from dept_dump; 此时出现错误
imp scott/tiger file=f:\export\scott.dmp;  导入操作
select * from dept_dump;发现职位并没有改变，仍是提职之前的。

六、数据泵介绍

    数据泵是Oracle 10g引入的一个新工具。它不但包括了所有的以前导入和导出工具的功能，而且还进行了不少扩充和加强。另外，其速度也更快，而且操作也更安全，总之好处多多。其功能与导入导出工具类似。

    数据泵工具也是一种对操作系统下的应用程序，包括expdp和impdp，其中expdp负责导出，而impdp负责导入。exp导出命令中，我们使用了物理文件的全路径（绝对路径），这样为了将来的管理和维护方便。但是在oracle 10g中，为了系统的安全，在expdpd和impdp应用程序中已经不允许使用绝对路径，取而代之的是在expdp和impdp应用程序中使用数据库的目录对象。目录对象一般是由DBA或有相应系统权限的用户创建，之后再将目录的读或写权限授予所需用户。为了简化问题，我们暂时不创建目录对象，而是使用Oracle数据库系统自动创建一个叫DATA——PUMP_DIR目录对象，该目录就是expdp和impdp应用程序默认的工作目录。

    那么如何才能找到该数据库目录对象所对象的操作系统目录呢？Oracle提供了一个叫dba_directories数据字典，利用它就可以方便地找到所需信息。但是，为了使显示输出清晰。首先是要SQL*Plus格式化命令（要以DBA用户登录数据库系统）。

set line 120;///我们工作常用200
col OWNER for a6;
col DIRECTORY_NAME for a20;
col DIRECTORY_PATH for a65;
select * from dba_directories;
GARNT READ,WRITE ON DIRECTORY DATA_PUMP_DIR to scott;//赋予scott用户对DATA_PUMP_DIR的权限
select * from emp_dump order by job,sql; 切换SCOTT用户后，查询相关信息

    参数介绍：

    ·DIRECTORY=DATA_PUMP_DIR,存放导出文件的目录为DATA_PUMP_DIR所定义的操作系统目录。

    ·tables=(emp_dump,dept_dump),要导出的表为emp_dump和dept_dump

    ·DUMPFILE=SCOTT.dmp,导出操作系统文件的名为SCOTT.dmp。

    ·QUERY=scott.emp_dump:"WHERE job<>'ANALYST' AND sal>1250",在scott的emp_dump表中只有job(职位)不是ANALYST（分析员），并且sal（工资）高于1250的数据才导出到SCOTT.dmp文件中。

    由于这个实力的单数很多，我么将上面的参数保存在一个名为scott_par.txt的正文参数文件中，之后expdp应用程序再调用这个正文参数文件。如果导出工作是经常的工作，这回极大地简化例行的操作。因此启动记事本程序并将上面所介绍的参数写入，最后以scott_par.txt为文件名存入F:\oracle\mgt目录中（该目录时自己创建，也可以使用其他的目录名）。

    启动DOS窗口，

F：\>cd oracle\mgt
F：\>cd oracle\mgt>dir
F：\>cd oracle\mgt>more scott_par.txt

DIRECTORY=DATA_PUMP_DIR
tables=(emp_dump,dept_dump)
DUMPFILE=SCOTT.dmp
QUERY=scott.emp_dump:"WHERE job<>'ANALYST' AND sql>1250"

F：\>cd oracle\mgt>expdp scott/tiger parfile=scott_par.txt 查看是否该文件创建生成

drop table emp_dump; 以scott用户
select * from emp_dump;//验证是否真的被删除

    现在就可以利用刚刚做的逻辑备份（用数据泵导出的文件）进行逻辑恢复了，可以使用如下命令将用数据泵将SCOTT.DMP文件中的数据重新导入回SCOTT用户中（逻辑恢复）。

F：\>cd oracle\mgt> impdp scott/tiger parfile=scott_par.txt

    显示输出标明，SCOTT用户下的DEPT_DUMP已经存在，这是因为之前只删除了emp_dump表，但是这并不影响逻辑恢复的结果，impdp程序只是跳过了已经存在的表。接下来，使用SQL语句检查emp_dump表是否已经被成功恢复。

    select * from emp_dump;

    上述语句，显示出的结果表明：impdp只恢复了满足条件（job不是ANALYST并且sal高于1250）的数据，即该表的子集。最后，使用语句检查一下dept_dump表是否完好无损。经查看dept_dump表中的数据确实完好无损，这也正是我们期望看到的结果到此为止可以确信所做的逻辑恢复已经成功。