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 ~]$
标签:REGION,Express,oracle,Loader,CHAR,TEST,Mode,test,redhat76 From: https://www.cnblogs.com/notonlydba/p/18394217