数据库导入导出详解

1、数据库导入导出：

1、传统方式：exp（导出）和 imp（导入）

2、数据泵方式：expdp（导出）和 impdp（导入）

3、第三方工具：PL/sql Developer

2、三种导入导出方式优缺点比较

2.1、exp&imp

优点：代码书写简单易，从本地即可直接导入，不用在服务器中操作，降低难度，减少服务器上的操作也就保证了服务器上数据文件的安全性。

缺点：这种导入导出的速度相对较慢，合适数据库数据较少的时候。如果文件超过几个G，大众性能的电脑，至少需要4~5个小时左右。

2.2、expdp&impdp

优点：导入导出速度相对较快,几个G的数据文件一般在1~2小时左右。

缺点：代码相对不易理解，要想实现导入导出的操作，必须在服务器上创建逻辑目录(不是真正的目录)。我们都知道数据库服务器的重要性，所以在上面的操作必须慎重。所以这种方式一般由专业的程序人员来完成。

2.3、PL/sql Develpoer

优点：封装了导入导出命令，无需每次都手动输入命令。方便快捷，提高效率。

缺点：长时间应用会对其产生依赖，降低对代码执行原理的理解。

3、注意

目标数据库：数据即将导入的数据库。

源数据库：数据导出的数据库。

1、目标数据库要与源数据库有着名称相同的表空间。

2、目标数据在进行导入时，用户名尽量相同(这样保证用户的权限级别相同)。

3、目标数据库每次在进行数据导入前，应做好数据备份，以防数据丢失。4、使用数据泵时,一定要现在服务器端建立可用的逻辑目录，并检查是否可用。

5、弄清是导入导出到相同版本还是不同版本(oracle10g版本与oracle11g版本)。

6、目标数据导入前,弄清楚是数据覆盖(替换),还是仅插入新数据或替换部分数据表。

7、确定目标数据库磁盘空间是否足够容纳新数据，是否需要扩充表空间。

8、导入导出时注意字符集是否相同，一般Oracle数据库的字符集只有一个，并且固定，一般不改变。

9、导出格式介绍：

Dmp格式：.dmp是二进制文件，可跨平台，还能包含权限，效率好；

Sql格式：.sql格式的文件，可用文本编辑器查看，通用性比较好，效率不如第一种，适合小数据量导入导出。尤其注意的是表中不能有大字（blob,clob,long），如果有，会报错；

Pde格式：.pde格式的文件，.pde为PL/SQL Developer自有的文件格式，只能用PL/SQL Developer工具导入导出，不能用文本编辑器查看；

10、确定操作者的账号权限。

4、三者的导入导出方法

4.1、传统方法

1、导入导出语法

exp(imp)  username/password@servicename:1521 file="e:\temp.dmp" full = y
exp(imp)  username/password@ip:端口/orcl file="e:\temp.dmp" full = y
1
2

2、数据库导出举例

exp stg/stg@127.0.0.1:1521/orcl file="e:\stg.dmp" full = y;
1

3、参数说明

exp：导出命令，导出时必写。
imp：导入命令，导入时必写。
username：导出数据的用户名，必写。
password：导出数据的密码，必写。
@：地址符号，必写。
servicename：oracle的服务名，必写。
1521：端口号，1521是默认的可以不写，非默认要写。
file="e:\stg.dmp"：文件存放路径地址，必写。
full=y 表示全库导出。可以不写，则默认为no,则只导出用户下的对象。
1
2
3
4
5
6
7
8
9

4、方法细分

1、完全导入导出：

exp(imp)  username/password@servicename:1521 file="e:\temp.dmp" full = y;
1

2、部分用户表table导入导出：

exp(imp)  username/password@servicename:1521 file="e:\temp.dmp" tabels= (table1,table2,table3,...);
1

3、表空间tablespaces导入导出：

exp(imp)  username/password@servicename:1521 file="e:\temp.dmp" tablespaces=(tablespace1,tablespace2,tablespace3,...);
1

一个数据库实例可以有N个表空间(tablespace)，一个表空间下可以有N张表(table)。

4、用户名username对象导入导出：

exp(imp)  username/password@servicename:1521 file="e:\temp.dmp" owner(username1,username2,username3);
1

5、按用户导出：

exp username/password@servicename:1521 file=C:\person.dmp owner=用户名
1

6、按用户导入：

imp username/password@servicename:1521 file=C:\stg.dmp fromuser=[old_username] touser=[username]
1

4.2、数据泵方法

创建directory

expdp(impdp) username/password@servicename:1521 schemas=username dumpfile=file1.dmp logfile=file1.log directory=testdata1 remap_schema=test:test;
1

数据库导出举例

expdp xinxiaoyong/123456@127.0.0.1:1521 schemas=xinxiaoyong dumpfile=test.dmp logfile=test.log directory=testdata1;
1

参数说明

exp:导出命令，导出时必写。
imp:导入命令，导入时必写,每次操作，二者只能选择一个执行。
username:导出数据的用户名，必写;
password:导出数据的密码，必写;
@:地址符号，必写;
SERVICENAME:Oracle的服务名，必写;
1521:端口号，1521是默认的可以不写,非默认要写;
schemas：导出操作的用户名;
dumpfile：导出的文件;
logfile:导出的日志文件,可以不写；
directory:创建的文件夹名称;
remap_schema=源数据库用户名:目标数据库用户名：二者不同时必写，相同可以省略;
1
2
3
4
5
6
7
8
9
10
11
12

查看表空间

select * from dba_tablespaces;
1

查看管理员目录

同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错

select * from dba_directories;
1

创建逻辑目录

该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

create directory testdata1 as 'd:\test\dump';
1

授权

给 stg 用户赋予在指定目录的操作权限，最好以system等管理员赋予。

grant read,write on directory testdata1 to stg;
1

导出数据

1、按用户导

expdp xinxiaoyong/123456@orcl schemas=xinxiaoyong dumpfile=expdp.dmp directory=testdata1;
1

2、并行进程parallel

expdp xinxiaoyong/123456@orcl directory=testdata1 dumpfile=xinxiaoyong3.dmp parallel=40 job_name=xinxiaoyong3
1

3、按表名导

expdp xinxiaoyong/123456@orcl tables=emp,dept dumpfile=expdp.dmp directory=testdata1;
1

4、按查询条件导

expdp xinxiaoyong/123456@orcl directory=testdata1 dumpfile=expdp.dmp tables=emp query='WHERE deptno=20';
1

5、按表空间导

expdp system/manager directory=testdata1 dumpfile=tablespace.dmp tablespaces=temp,example;
1

6、导整个数据库

expdp system/manager directory=testdata1 dumpfile=full.dmp FULL=y;
1

导入数据

1、导到指定用户下

impdp stg/stg directory=testdata1 dumpfile=stg.dmp schemas=stg;
1

2、改变表的 owner

impdp dw/dw directory=testdata1 dumpfile=dw.dmp tables=xinxiaoyong.dept remap_schema =stg:dw;
1

3、导入表空间

impdp system/manager directory=testdata1 dumpfile=tablespace.dmp tablespaces=example;
1

4、导入数据库

impdb system/manager directory=dump_dir dumpfile=full.dmp FULL=y;
1

5、追加数据

impdp system/manager directory=testdata1 dumpfile=expdp.dmp schemas=system  table_exists_action;
1

4.3、PLSQL方法

登录plsql工具，所使用用户为源数据库有导出权限（exp_full_database，dba等）的用户。

导出建表语句（包括存储结构）

导出步骤：工具，导出表，选好表以后，下方有Oracle导出是对应的dmp文件，SQL插入是对应的SQL文件。