在数据迁过程中需求需要将oracle数据库数据导出程txt格式然后再导入db2库中,经查询实验汇总三种方法:
1.plsqldev 里面有一个选项可以把表以excel格式到时
2.使用spool
sqlplus / as sysdba
set linesize 1000
set pagesize 0
set echo off
set termout off
set heading off
set feedback off
SET trims ON
set term off
SET trimspool ON
SET trimout ON
spool '/archlog/exp/test.txt';
select OWNER||' , '||SEGMENT_NAME||' , '||PARTITION_NAME||' , ' from dba_segments where rownum<10000;
spool off;
/
3.使用UTL_FILE程序包
UTL_FILE.FOPEN第一个参数为文件路径,不能直接指定绝对路径,需要建立directory,然后指定我们建立的directory
sqlplus / as sysdba
create directory MY_DIR as ‘/home/oracle/’;
grant read,write on directory dir_dump to HR;##也可以直接建立一个public directory
CREATE OR REPLACE PROCEDURE test
IS
testjiao_handle UTL_FILE.file_type;
BEGIN
test_handle := UTL_FILE.FOPEN('MY_DIR','test.txt','w');
FOR x IN (SELECT * FROM TESTJIAO) LOOP
UTL_FILE.PUT_LINE(test_handle,x.ID || ',' || x.RQ ||',');
END LOOP;
UTL_FILE.FCLOSE(test_handle);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
END;
/