您好,登錄后才能下訂單哦!
這篇文章主要介紹“Oracle導(dǎo)出文本文件的方法有哪些”,在日常操作中,相信很多人在Oracle導(dǎo)出文本文件的方法有哪些問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”O(jiān)racle導(dǎo)出文本文件的方法有哪些”的疑惑有所幫助!接下來,請跟著小編一起來學(xué)習(xí)吧!
為了構(gòu)建導(dǎo)出文本文件,先做點準備工作
1、擴充表空間
ALTER TABLESPACE DAMS_DATA ADD DATAFILE 'C:\Oracle\oradata\orcl\DAMADATA2.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE 6000M;
2、創(chuàng)建一張10萬記錄和50萬記錄的數(shù)據(jù)表
首先為了快速創(chuàng)建表數(shù)據(jù)用了CONNECT BY方法,再次為了把表存儲搞大,每個字段長度都是1000字節(jié),一條記錄平均4000字節(jié)左右,數(shù)據(jù)庫的db_block_size=8192字節(jié),由于block還包括其他信息,所以一個塊只能存儲一條記錄,10萬記錄大概在800M左右,50萬記錄為4G
CREATE TABLE record10w ( id INT, data1 CHAR(1000), data2 CHAR(1000), data3 CHAR(1000), data4 CHAR(1000) ); INSERT INTO record10w SELECT a.rn, DBMS_RANDOM.STRING ('u', 5), --大寫字母隨機 DBMS_RANDOM.STRING ('l', 5), --小寫字母隨機 DBMS_RANDOM.STRING ('a', 5), --混合字母隨機 DBMS_RANDOM.STRING ('x', 5) --字符串數(shù)字隨機 --DBMS_RANDOM.STRING ('p', 5) --鍵盤字符隨機 FROM (SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=100000) a; --27 seconds COMMIT; CREATE TABLE record50w ( id INT, data1 CHAR(1000), data2 CHAR(1000), data3 CHAR(1000), data4 CHAR(1000) ); INSERT INTO record50w SELECT a.rn, DBMS_RANDOM.STRING ('u', 5), --大寫字母隨機 DBMS_RANDOM.STRING ('l', 5), --小寫字母隨機 DBMS_RANDOM.STRING ('a', 5), --混合字母隨機 DBMS_RANDOM.STRING ('x', 5) --字符串數(shù)字隨機 --DBMS_RANDOM.STRING ('p', 5) --鍵盤字符隨機 FROM (SELECT level,ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=500000) a; --164 seconds COMMIT;
3、簡單做一下表分析
ANALYZE TABLE RECORD10W COMPUTE STATISTICS; ANALYZE TABLE RECORD50W COMPUTE STATISTICS;
4、查看一下表的統(tǒng)計信息
SELECT A.OWNER,A.TABLE_NAME,A.TABLESPACE_NAME,A.NUM_ROWS,A.BLOCKS,A.EMPTY_BLOCKS,A.AVG_ROW_LEN FROM ALL_TABLES A WHERE OWNER='METADATA' AND TABLE_NAME IN ('RECORD10W','RECORD50W')
定義spool10w.sql用來導(dǎo)出record10w記錄
@C:\software\sqluldr2\spool10w.sql
SPOOL C:\software\sqluldr2\data\record10wspool.txt SET ECHO OFF --不顯示腳本中正在執(zhí)行的SQL語句 SET FEEDBACK OFF --不顯示sql查詢或修改行數(shù) SET TERM OFF --不在屏幕上顯示 SET HEADING OFF --不顯示列 SET LINESIZE 1000; //設(shè)置行寬,根據(jù)需要設(shè)置,默認100 select id||','||data1|| ',' ||data2 FROM record10w; --需要導(dǎo)出的數(shù)據(jù)查詢sql SPOOL OFF
定義spool50w.sql用來導(dǎo)出record50w記錄
@C:\software\sqluldr2\spool50w.sql
SPOOL C:\software\sqluldr2\data\record10wspool.txt SET ECHO OFF --不顯示腳本中正在執(zhí)行的SQL語句 SET FEEDBACK OFF --不顯示sql查詢或修改行數(shù) SET TERM OFF --不在屏幕上顯示 SET HEADING OFF --不顯示列 SET LINESIZE 1000; //設(shè)置行寬,根據(jù)需要設(shè)置,默認100 select id||','||data1|| ',' ||data2 FROM record50w; --需要導(dǎo)出的數(shù)據(jù)查詢sql SPOOL OFF
在Oracle Command窗口中執(zhí)行命令
SQL> set time on; 18:09:32 SQL> @C:\software\sqluldr2\spool10w.sql Started spooling to C:\software\sqluldr2\data\record10wspool.txt --20秒 18:09:51 SQL> @C:\software\sqluldr2\spool50w.sql 18:10:52 SQL> --1分1秒
補充
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; /
這個包很久之前用過,好像效率也不錯,在此不想嘗試了,有興趣的朋友可以試一下性能。
UTL_FILE.FOPEN打開文件
UTL_FILE.PUT_LINE寫入記錄
UTL_FILE.FCLOSE關(guān)閉文件
UTL_FILE.FOPEN第一個參數(shù)為文件路徑,不能直接指定絕對路徑,需要建立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; /
說實在的Oracle對大批量大規(guī)模數(shù)據(jù)的導(dǎo)出做的很不友好,大概是基于某種自信吧,spool的效率一般很低,很多開源ETL工具都是通過JDBC連接導(dǎo)出的,效率也好不到那里去
sqluldr2的作者是樓方鑫,Oracle的大牛,原來淘寶的大神,有過幾面之緣,是基于OCI底層接口開發(fā)的文本導(dǎo)出工具。
sqluldr2小巧方便,使用方法類似于Oracle自帶的exp,支持自定義SQL、本地和客戶端的導(dǎo)出,速度快,效率高。
sqluldr2有幾個版本,面向linux和windows的,有32位和64位的,可自行找鏈接下載。
c:\software\sqluldr2>sqluldr264 SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1 (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved. License: Free for non-commercial useage, else 100 USD per server. Usage: SQLULDR2 keyword=value [,keyword=value,...] Valid Keywords: user = username/password@tnsname #連接用戶/密碼@tns名稱 sql = SQL file name #指定SQL文件名 query = select statement #指定SQL語句 field = separator string between fields #指定字段分隔符 record = separator string between records #指定記錄換行符 rows = print progress for every given rows (default, 1000000) #輸出導(dǎo)出記錄日志 file = output file name(default: uldrdata.txt) #導(dǎo)出數(shù)據(jù)文件名 log = log file name, prefix with + to append mode #導(dǎo)出日志文件名 fast = auto tuning the session level parameters(YES) #快速導(dǎo)出參數(shù) text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH). #導(dǎo)出類型 charset = character set name of the target database. #設(shè)置目標數(shù)據(jù)庫字符集 ncharset= national character set name of the target database. parfile = read command option from parameter file for field and record, you can use '0x' to specify hex character code, \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27
#設(shè)置查詢條件為select * from record50w,導(dǎo)出文件頭,導(dǎo)出文件名為record50wsqluldr2.csv,日志文件名為record50wsqluldr2.log,控制文件名為record50w_sqlldr.ctl
sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record50w" head=yes file=C:\software\sqluldr2\data\record50wsqluldr2.csv log=C:\software\sqluldr2\log\record50wsqluldr2.log table=record50w
sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record10w" head=yes file=C:\software\sqluldr2\data\record10wsqluldr2.csv log=C:\software\sqluldr2\log\record10wsqluldr2.log table=record10w
具體執(zhí)行見下面:
c:\software\sqluldr2>time 當前時間: 18:14:07.92 c:\software\sqluldr2>sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record50w" head=yes file=C:\software\sqluldr2\data\record50wsqluldr2.csv log=C:\software\sqluldr2\log\record50wsqluldr2.log table=record50w c:\software\sqluldr2>time 當前時間: 18:14:26.40 --19秒 c:\software\sqluldr2>time 當前時間: 18:14:36.83 c:\software\sqluldr2>sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record10w" head=yes file=C:\software\sqluldr2\data\record10wsqluldr2.csv log=C:\software\sqluldr2\log\record10wsqluldr2.log table=record10w c:\software\sqluldr2>time 當前時間: 18:14:43.05 --7秒
到此,關(guān)于“Oracle導(dǎo)出文本文件的方法有哪些”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識,請繼續(xù)關(guān)注億速云網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。