oracle 数据导出

来源：互联网发布：网络炒作bjiko 编辑：程序博客网时间：2024/06/05 10:52

方案一：使用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 filefor Spool' from dual;

This is a test data file for Spool

SQL> select no,class,name fromdatabase;

20110001 1101 Mysql

20110002 1101 Oracle

20110003 1102 DB2

20110004 1103 SQL Server

SQL> select no||'-'||class||'-'||namefrom 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命令请参考《SQLPlusSpool备忘》。

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

使用交互式exp的示例：

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

$exptest/test123@appdb

Enter arrayfetch buffer size: 4096 > 【buffer大小】

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

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

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

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

Export donein ZHS16GBK character set and ZHS16GBK NCHAR characterset 【字符集设置】

Aboutto export specified tables via Conventional Path ...

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

.. exporting table CMAMENU 4336 rows exported

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

Exportterminated 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.dmpowner=(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=\"wherejob=\'salesman\' and sal\<1600\" file=/directory/scott2.dmp

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

$expparfile=username.par file=/directory1/username_1.dmp,/directory1/username_2.dmpfilesize=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限制文件大小，因为有的文件系统的文件有大小限制。

experid=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 ，SQLDeveloper，SQL Navigator等等。