[20160910]12c sqlldr express.txt

[20160910]12c sqlldr express.txt

--Oracle 12c introduces Sql*Loader Express features, which allow users to run sqlldr with minimum configuration.  
--透過例子來說明:

SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

D:\tmp>cat emp1.dat
cat emp1.dat
7369,SMITH,CLERK,7902,1980/12/17,800,,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,,20
7839,KING,PRESIDENT,,1981/11/17,5000,,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,,20
7900,JAMES,CLERK,7698,1981/12/3,950,,30
7902,FORD,ANALYST,7566,1981/12/3,3000,,20
7934,MILLER,CLERK,7782,1982/1/23,1300,,10

SCOTT@test01p> create table emp1 as select * from emp where 1=0;
Table created.

D:\tmp>sqlldr scott/btbtms@test01p table=emp1
sqlldr scott/btbtms@test01p table=emp1
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Sep 9 21:28:44 2016
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: EMP1
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table EMP1:
  14 Rows successfully loaded.

Check the log files:
  emp1.log
  emp1_%p.log_xt
for more information about the load.

--只要檔案命名為表名.dat,格式使用逗號分割,也就是csv模式.就ok了.

SCOTT@test01p> select * from emp1 where rownum<=2;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30