SQL*Loader Express Mode

Oracle Database 12C中的SQL*Loader 新增加了Express Mode，借助这个特性，可以在最小化配置的情况下加载数据（比如无需要创建Control file)


SQL> conn test01/test01
Connected.
SQL> create table test
  2                ( region      char(3),
  3                  region_name varchar2(12),
  4                  bill_month  number(6),
  5                  fee         number(10,2)
  6                );
Table created.
SQL> 


[oracle@redhat76 ~]$ cat  test.dat
     530,HZ,200501,100.01
     530,HZ,200502,800.23
     531,JN,200501,5000.81
     531,JN,200502,5360.00
     532,QD,200501,20670.32
     532,QD,200502,22000.08
     533,ZB,200501,3050.56
     533,ZB,200502,3108.14
[oracle@redhat76 ~]$ 


[oracle@redhat76 ~]$ sqlldr  -help|grep degree_of_parallelism
degree_of_parallelism -- degree of parallelism for express mode load and external table load
[oracle@redhat76 ~]$ 


[oracle@redhat76 ~]$ sqlldr test01/test01 TABLE=test
SQL*Loader: Release 19.0.0.0.0 - Production on Tue Sep 3 11:26:07 2024
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Express Mode Load, Table: TEST
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
Table TEST:
  8 Rows successfully loaded.
Check the log files:
  test.log
  test_%p.log_xt
for more information about the load.
[oracle@redhat76 ~]$ 







[oracle@redhat76 ~]$ more test_30989.log_xt
 LOG file opened at 09/03/24 11:26:08
Total Number of Files=1
Data File: test.dat
Log File: test_30989.log_xt
 LOG file opened at 09/03/24 11:26:08
Bad File: test_30989.bad
Field Definitions for table SYS_SQLLDR_X_EXT_TEST
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

    REGION                          CHAR (255)
      Terminated by ","
      Trim whitespace from left and right
    REGION_NAME                     CHAR (255)
      Terminated by ","
      Trim whitespace from left and right
    BILL_MONTH                      CHAR (255)
      Terminated by ","
      Trim whitespace from left and right
    FEE                             CHAR (255)
      Terminated by ","
      Trim whitespace from left and right
[oracle@redhat76 ~]$ more test.log

SQL*Loader: Release 19.0.0.0.0 - Production on Tue Sep 3 11:26:07 2024
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: TEST
Data File:      test.dat
  Bad File:     test_%p.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table TEST, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
REGION                              FIRST     *   ,       CHARACTER            
REGION_NAME                          NEXT     *   ,       CHARACTER            
BILL_MONTH                           NEXT     *   ,       CHARACTER            
FEE                                  NEXT     *   ,       CHARACTER            

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'test'
APPEND
INTO TABLE TEST
FIELDS TERMINATED BY ","
(
  REGION,
  REGION_NAME,
  BILL_MONTH,
  FEE
)
End of generated control file for possible reuse.

created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_TEST"

CREATE TABLE "SYS_SQLLDR_X_EXT_TEST" 
(
  "REGION" CHAR(3),
  "REGION_NAME" VARCHAR2(12),
  "BILL_MONTH" NUMBER(6),
  "FEE" NUMBER(10,2)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test_%p.bad'
    LOGFILE 'test_%p.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "REGION" CHAR(255),
      "REGION_NAME" CHAR(255),
      "BILL_MONTH" CHAR(255),
      "FEE" CHAR(255)
    )
  )
  location 
  (
    'test.dat'
  )
)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table TEST

INSERT /*+ append parallel(auto) */ INTO TEST 
(
  REGION,
  REGION_NAME,
  BILL_MONTH,
  FEE
)
SELECT 
  "REGION",
  "REGION_NAME",
  "BILL_MONTH",
  "FEE"
FROM "SYS_SQLLDR_X_EXT_TEST"
dropping external table "SYS_SQLLDR_X_EXT_TEST"
Table TEST:
  8 Rows successfully loaded.
Run began on Tue Sep 03 11:26:07 2024
Run ended on Tue Sep 03 11:26:09 2024
Elapsed time was:     00:00:01.47
CPU time was:         00:00:00.02
[oracle@redhat76 ~]$