Oracle数据导出

 

方案一：使用SQLPlus的Spool命令。Spool是SQLPlus的命令，不是SQL语句。通过Spool可以将SQL语句(导出的话主要是使用Select)的执行结果导出到指定的文件中，即“所见即所得”：使用Spool命令后，在SQLPlus中执行SQL语句打印在屏幕上的结果会原样导出到文件中。为了控制导出数据的格式，Spool提供了一系列Set命令来设置导出的格式，当然也可以在Select语句中直接设置格式。

 

使用Spool导出数据的示例（在命令行执行）：

 

SQL> spool d:\data.cvs                【指定导出文件，导出开始】
SQL> set echo off;                       【不显示执行的SQL命令】
SQL> set feedback off;                【关闭“已选择XX行”的提示】
SQL> set heading off;                  【去掉select结果的字段名，只显示数据】
SQL> set termout off;                  【关闭屏幕上的SQL执行结果显示】
SQL> set trimspool on;                【去除重定向（Spool）输出时每行的拖尾空格】
SQL> set pagesize 0;                   【输出每页行数，缺省为24，为了避免分页，可设定为0】
SQL> select 'This is a test data file for Spool' from dual;
This is a test data file for Spool
SQL> select no,class,name from database;
20110001 1101 Mysql
20110002 1101 Oracle
20110003 1102 DB2
20110004 1103 SQL Server
SQL> select no||'-'||class||'-'||name from database;         【在Select中设置格式】
20110001-1101-Mysql
20110002-1101-Oracle
20110003-1102-DB2
20110004-1103-SQL Server
SQL> spool off;                          【导出结束】

   

 

查看导出文件d:\data.cvs，内容如下：

SQL> set echo off;

SQL> set feedback off;

SQL> set heading off;

SQL> set termout off;

SQL> set trimspool on;

SQL> set pagesize 0;

SQL> select 'This is a test data file for Spool' from dual;

This is a test data file for Spool

SQL> select no,class,name from database;

20110001 1101 Mysql

20110002 1101 Oracle

20110003 1102 DB2

20110004 1103 SQL Server

SQL> select no||'-'||class||'-'||name from database;

20110001-1101-Mysql

20110002-1101-Oracle

20110003-1102-DB2

20110004-1103-SQL Server

SQL> spool off;

 

这并不是我们想要的结果，我们只想输出数据，而不想记录SQL命令。

SQL> set echo off;                      【不显示执行的SQL命令】

SQL> set termout off;                 【关闭屏幕上的SQL执行结果显示】

 

这两个设置似乎并没有生效。前面说过Spool是“所见即所得”，因为上述命令是在SQLPlus命令行中一句句输入的，因此Spool中记录了你输入的所有内容。如何才能得到只有数据的文件呢？解决办法就是把这些命令（包括Set和Select）写在一个.sql的脚本文件中，如下：

spool.sql内容

 

spool d:\data.csv
set echo off;
set feedback off;
set heading off;
set termout off;
set trimspool on;
set pagesize 0;
select 'This is a test data file for Spool' from dual;
select no,class,name from database;
select no||'-'||class||'-'||name from database;
spool off;

 

在SQLPlus中直接执行这个脚本

SQL> @d:\spool.sql

 

查看导出文件d:\data.cvs，内容如下：

This is a test data file for Spool

20110001 1101 Mysql

20110002 1101 Oracle

20110003 1102 DB2

20110004 1103 SQL Server

20110001-1101-Mysql

20110002-1101-Oracle

20110003-1102-DB2

20110004-1103-SQL Server

 

Spool提供了一系列Set命令来设置导出的格式，更多Spool Set命令请参考《SQLPlus Spool备忘》。

 

方案二：使用Oracle自带的exp工具。exp/imp是Oracle自带的一对数据导出/导入工具，常用于数据恢复与备份，使用exp/imp工具可以实现单表导出、用户导出、数据库导出三个级别的导出操作。exp/imp分交互式、非交互式两种处理方式。关于imp导入请参考《Oracle数据导入》。

 

使用交互式exp的示例：

（交互式导出时大多数参数都有缺省值，如果选用缺省值，直接回车即可。）

$exp test/test123@appdb

Enter array fetch buffer size: 4096 >   【buffer大小】

Export file: expdat.dmp > m.dmp              【导出的文件名】

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3   【导出模式】

Export table data (yes/no): yes >         【是否导出数据，NO表示只导出表结构】

Compress extents (yes/no): yes >         【是否压缩】

Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set     【字符集设置】

About to export specified tables via Conventional Path ...

Table(T) or Partition(T:P) to be exported: (RETURN to quit) > cmamenu   【要导出的表名】

. . exporting table                         CMAMENU        4336 rows exported

Table(T) or Partition(T:P) to be exported: (RETURN to quit) >         【回车退出】

Export terminated successfully without warnings.

 

使用非交互式exp的示例：

把scott用户里两个表emp,dept导出到文件/directory/scott.dmp

$exp scott/tiger tables=(emp,dept) file=/directory/scott.dmp grants=y

 

将数据库中system用户与sys用户的表导出

exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

 

将数据库TEST完全导出到D:\daochu.dmp中

exp system/manager@TEST file=d:\daochu.dmp full=y

 

在exp里面加上导出emp的查询条件job='salesman' and sal<1600

【用的比较少，把满足条件的记录生成临时表后再exp会方便一些】

$exp scott/tiger tables=emp query=\"where job=\'salesman\' and sal\<1600\" file=/directory/scott2.dmp

 

username.par为导出工具exp用的参数文件，里面具体参数可以根据需要去修改。

$exp parfile=username.par file=/directory1/username_1.dmp,/directory1/username_2.dmp filesize=2000M log=/directory2/username_exp.log

参数文件username.par内容

userid=username/userpassword

buffer=8192000

compress=n

grants=y

 

导出全库数据。file参数可以设置多个文件，导出到多个文件中，每个文件最大2GB，exp只生成最少的导出文件，譬如导出的数据不足2GB，exp只生成/tmp/2004020601.dmp。如果file列表中的文件太少，能存放所有的导出数据，exp会提示用户输入文件名。出过程记录日志。filesize限制文件大小，因为有的文件系统的文件有大小限制。

exp erid=cams/cams@cams ll=y file=(/tmp/2004020601.dmp,/tmp/2004020602.dmp, /tmp/2004020603.dmp)  filesize=2GB log=/tmp/20040206.log

 

 

方案三：使用Pro*C。Pro*C程序实际是内嵌有SQL语句或PL/SQL块的C程序，在Pro*C程序中可以嵌入SQL语句来完成动态地建立、修改、删除数据库中的表，查询、插入、修改和删除数据库表中的记录，还可以实现事务的提交和回滚。在Pro*C程序中还可以嵌入PL/SQL块，以改进应用程序的性能，特别是在网络环境下，可以减少网络传输和处理的总开销。使用Pro*C进行数据导出其实是将SQL、结构体、文件读写结合使用，将数据库表的数据以结构体的形式导出到文件中。

 

因为Pro*C语法比较杂，通常出错都出在SQL语句错误、C语句错误、结构体字段与表结构不对应等。

 

关于Pro*C的示例待续……

 

 

方案四：使用可视化工具TOAD，PL/SQL ，SQL Developer，SQL Navigator等等。