[SQL ] Oracle SQL*Loader

看板Database (資料庫)作者ppanerai (好想要隻小沛)時間18年前 (2006/07/17 10:26)推噓1(1推 0噓 0→)

留言1則, 1人參與討論串1/3 (看更多)

今天就來大概說一下ORACLE提出的 Utility - SQL*Loader 這裡以 Oracle 9i 為例說明... 以下參考自 ORACLE9i DBA Fundamentals 2-II Oracle9i Database Utilities Release 2 (9.2) ( 10g 有些新的功能 - datapump 講起來有點複雜耶~~以後再說 = =") 1. SQL*Loader 概念將外部的 file (external file) 之data load到Oracle database的table中 SQL*Loader 有著很高的彈性, 幾乎文字檔都可以不錯的效率load入table SQL*Loader 有以下的功能在同一個 session load 多個file或 load data到多個table 文字檔可以是固定或變動長度文字檔內容可以是 character, binary... 任何format 指定data2的 character set 選擇性load data (就是過濾嚕) 使用SQL function 處理data再load入table 指定某欄位產生 unique sequential key 可由disk, tape, named pipe load data 產生error reports 使用Secondary datafiles (SDFs) loading LOBs and collections 可選擇 conventional (彈性高)/direct(效率好) path loading 2. SQL*Loader 之使用如教材上直接舉例會比較好了解 $ sqlldr hr/hr control=case1.ctl log=case1.log direct=Y control 指 control file (不是 database的control file) 就是sqlldr的參數控制檔 log當然是 log file direct=Y 使用 direct path ; =N 使用conventional path 3. Control file 別與 database 的control file 搞混了喔功能: 告訴 loader 去哪找資料load data 的 format configuration - Memory Management, Record Rejection, Interrupted load handling details 如何處理data syntax: Free Format, case insensitive, " -- " 為註解該行, CONSTANT 為保留字例子: 將 data及控制全寫在 control file中 ulcase1.ctl ----------------------- ulcase1.ctl --------------------------------- LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC) BEGINDATA 12,RESEARCH,"SARATOGA" 10,"ACCOUNTING",CLEVELAND 11,"ART",SALEM 13,FINANCE,"BOSTON" 21,"SALES",PHILA. 22,"SALES",ROCHESTER 42,"INT'L","SAN FRAN" ----------------------- END of ulcase1.ctl ---------------------------- data (ulcase2.dat) 與 control file(ulcase2.ctl) 分開 ulcase2.ctl ----------------------- ulcase2.ctl --------------------------------- LOAD DATA INFILE 'ulcase2.dat' INTO TABLE EMP ( EMPNO POSITION(01:04) INTEGER EXTERNAL, ENAME POSITION(06:15) CHAR, JOB POSITION(17:25) CHAR, MGR POSITION(27:30) INTEGER EXTERNAL, SAL POSITION(32:39) DECIMAL EXTERNAL, COMM POSITION(41:48) DECIMAL EXTERNAL, DEPTNO POSITION(50:51) INTEGER EXTERNAL) ----------------------- END of ulcase2.ctl -------------------------- ulcase2.dat ----------------------- ulcase2.dat --------------------------------- 7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10 7566 JONES MANAGER 7839 3123.75 20 7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20 ----------------------- END of ulcase2.dat -------------------------- 將data與control file寫在一起, 資料以"," 為分隔, 在 " 中為同一欄位(並分大小寫) ulcase3.ctl ----------------------- ulcase3.ctl --------------------------------- LOAD DATA INFILE * APPEND INTO TABLE EMP FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (empno, ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY", sal, comm, deptno CHAR TERMINATED BY ':', projno, loadseq SEQUENCE(MAX,1) ) BEGINDATA 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101 7839, "King", "President", , 17-November-1981, 5500.00,, 10:102 7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102 7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101 7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103 7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103 7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101 ----------------------- END of ulcase3.ctl --------------------------- 還有其他的例子可以到 ?/rdbms/demo 中去找(上面也是抄出來的 XD ) 4. Conventional Path and Direct Path Conventional COMMITs to Make Changes 總是產生Redo logs 所有Constraints均作用觸發Insert triggers Can Load into Cluster Tables Other Users 可對該table做異動 Direct Data Saves 只有特定情況產生Redo PK, U-Key, Not Null 才作用不觸發Insert triggers Can't Load into Cluster Tables Other Users 不可對該table做異動以上只說到最簡單的 data loading 當然如果需要 load LOB 亦可其他的變化或是參數等, 請大家找 Manual的Utilities(每個版本應該都是放在這一本吧)翻一翻 XD ... = =" 我承認我懶.... ( ￣ c￣)y▂ξ -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 202.145.230.2