Oracle: Import CSV file

Tram Ho

Today I need to import a csv file with nearly 5M row, at first I tried to import with Toad and Sql Developer tool, the tools show tps 123 and the time to import all my files is 10h. I tried using the SQLLoader utility feature, the results were unexpected.

  1. Create control file bl_20191118.ctl LOAD DATA infile ‘/home/oracle/bl18112019.csv’ REPLACE INTO TABLE bl_20191118 fields terminated by ‘,’ optionally enclosed by ‘”(msisdn)
  2. run SQL * Loader from the command line sqlldr xxx / xxx control = / home / oracle / bl_20191118.ctl log = / home / oracle / bl_20191118.log

Result: it took me less than 1 minute to load 4938456 row to table bl_20191118 [ [email protected] ~] $ cat bl_20191118.log

SQL * Loader: Release – Production on Mon Nov 25 23:19:50 2019

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

Control File: /home/oracle/bl_20191118.ctl Data File: /home/oracle/bl18112019.csv Bad File: /home/oracle/bl18112019.bad Discard File: none specified

(Allow all discards)

Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional

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

Column Name Position Len Term Encl Datatype


Table BL_20191118: 4938456 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.

Space allocated for bind array: 16512 bytes (64 rows) Read buffer bytes: 1048576

Total logical records skipped: 0 Total logical records read: 4938456 Total logical records rejected: 0 Total logical records discarded: 0

Run ended on Mon Nov 25 23:19:50 2019 Run ended on Mon Nov 25 23:20:44 2019

Elapsed time was: 00: 00: 53.77 CPU time was: 00: 00: 05.36

Chia sẻ bài viết ngay

Nguồn bài viết : Viblo