【Oracle】oracle中把表导出为txt文件方法

herosrx

1.plsqldev 里面有一个选项可以把表以execl格式到时

2.使用spool

1. sqlplus / as sysdba
   
2. 
   
3. set linesize 1000
   
4. set pagesize 0
   
5. set echo off
   
6. set termout off
   
7. set heading off
   
8. set feedback off
   
9. SET trims ON
   
10. set term off
    
11. SET trimspool ON
    
12. SET trimout ON
    
13. spool '/archlog/exp/test.txt';
    
14. select OWNER||' , '||SEGMENT_NAME||' , '||PARTITION_NAME||' , ' from dba_segments where rownum<100;
    
15. spool off;

复制代码

#输出的test.txt文件头尾要编辑下
#set term off 只有在使用.sql脚本文件时才起作用，如上虽然指定了 set term off但是还是会把结果输出，也就是说set term off设置只对sql脚本有用

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

1. CREATE OR REPLACE PROCEDURE test
   
2. IS
   
3. testjiao_handle UTL_FILE.file_type;
   
4. BEGIN
   
5.   test_handle := UTL_FILE.FOPEN('MY_DIR','test.txt','w');
   
6.     FOR x IN (SELECT * FROM TESTJIAO) LOOP
   
7.       UTL_FILE.PUT_LINE(test_handle,x.ID || ',' || x.RQ ||',');
   
8.     END LOOP;
   
9.       UTL_FILE.FCLOSE(test_handle);
   
10. EXCEPTION WHEN OTHERS THEN
    
11.   DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
    
12. END;
    
13. /

复制代码