首页 > 数据库 >SQL*Loader Express Mode

SQL*Loader Express Mode

时间:2024-09-03 11:25:13浏览次数:11  
标签:REGION Express oracle Loader CHAR TEST Mode test redhat76

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

相关文章

  • Pifithrin-α hydrobromide 是一种 p53 抑制剂 |MedChemExpress (MCE)
    CAS:63208-82-2品牌:MedChemExpress(MCE)存储条件:4°C,sealedstorage,awayfrommoisture生物活性:Pifithrin-αhydrobromide是一种 p53 抑制剂,可阻断其转录活性并防止细胞凋亡。Pifithrin-αhydrobromide也是一种 arylhydrocarbonreceptor(AhR) 激动剂。IC5......
  • Sanguinarine (Sanguinarin) chloride 是一种苯并菲啶生物碱 |MedChemExpress (MCE)
    中文名:氯化血根碱CAS:5578-73-4品牌:MedChemExpress(MCE)存储条件:4°C,sealedstorage,awayfrommoistureandlight生物活性:Sanguinarine(Sanguinarin)chloride是一种苯并菲啶生物碱,来源于 SanguinariaCanadensis 的根部,可通过激活活性氧(ROS)的产生来刺激细胞......
  • 解决vs2022 工具箱中不显示 Devexpress控件的问题
    无效果的尝试1、在工具箱点右键选择RepairToolBox...无效果2、在工具箱中点右键重置工具箱无效果3、在工具箱中右键选择项浏览选择对应的dll,报错提示“面向无法动态枚举工具箱项目的平台”无效果4、删除整个vs配置文件夹:%LocalAppData%\Microsoft\VisualStudio<ver......
  • 【Intel Hackathon大赛】基于OpenVINO™ Model Optimizer + ChatGLM-6B + P-Tuning的A
    随着人工智能技术的飞速发展,自然语言处理(NLP)领域迎来了前所未有的变革,而大语言模型(LargeLanguageModel,LLM)作为这一变革的核心驱动力,正逐步成为连接人类语言与机器智能的桥梁。LLM通过海量文本数据的学习,掌握了丰富的语言知识、上下文理解能力以及生成高质量文本的能力,为智能教......
  • PBR-Book Ch8 Reflection Models
    PBR-BookCh8ReflectionModelsReflectionModels(pbr-book.org)球坐标系中,使用\((\theta,phi)\)\(\theta\)givendirectiontothe\(z\)axis\(\phi\)theangleformedwiththe\(x\)axisafterprojectionofdirectionontothe\(xy\)lane.......
  • DevExpress WinForms v24.1亮点- TreeList、折叠组件全新升级
    DevExpressWinForms拥有180+组件和UI库,能为WindowsForms平台创建具有影响力的业务解决方案。DevExpressWinForms能完美构建流畅、美观且易于使用的应用程序,无论是Office风格的界面,还是分析处理大批量的业务数据,它都能轻松胜任!DevExpressWinForms控件2024年第一个重大版本——......
  • 【类型转换】使用c#实现简易的类型转换(Emit,Expression,反射)
    引言哈喽。大家好,好久不见,最近遇到了一个场景,就是在FrameWork的asp.netmvc中,有个系统里面使用的是EntityFramework的框架,在这个框架里,提供了一个SqlQuery的方法,这个方法很好用啊,以至于在EFCORE8里面又添加了回来,不过不知道性能怎么样,我遇到的场景是通过SqlQuery查询的......
  • Modern C++——不准确“类型声明”引发的非必要性能损耗
    大纲案例代码地址C++是一种强类型语言。我们在编码时就需要明确指出每个变量的类型,进而让编译器可以正确的编译。看似C++编译器比其他弱类型语言的编译器要死板,实则它也做了很多“隐藏”的操作。它会在尝试针对一些非预期类型进行相应转换,以符合预期,比如《C++拾趣——......
  • Lecture 10 & 11 Real-time Physically-based Materials (surface model)
    Lecture10Real-timePhysically-basedMaterials(surfacemodelsandcont.)PBRandPBRMaterialsPhysically-BasedRendering(PBR)基于物理的渲染渲染内的任何事都应该是PBR的材质、光照、相机、光线传播等等不限于材质,但常常指材质PBRmaterialsinRTR......
  • PoLLMgraph: Unraveling Hallucinations in Large Language Models via State Transit
    本文是LLM系列文章,针对《PoLLMgraph:UnravelingHallucinationsinLargeLanguageModelsviaStateTransitionDynamics》的翻译。PoLLMgraph:通过状态转换动力学揭示大型语言模型中的幻觉摘要1引言2相关工作3PoLLMgraph4实验5结论局限性摘要尽管近......