Categories
DATABASE Software System/Linux/Unix*

import data file into Oracle database, using SQL*Loader utility … sqlldr … sql loader

use exp

For example, in the following example, benefits table has a column called v_status. This exp command will export only the rows that contains “INVALID” as value in the v_status column.

$ exp hradmin/mypassword TABLES=benefits query=\"where v_status=\'INVALID\'\"
..
About to export specified tables via Conventional Path ...
. . exporting table BENEFITS 20783 rows exported


 

SQL*Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.

螢幕快照 2014 09 03 上午10 42 12

Install Oracle Database 11gR2 on Ubuntu 12.04

The minimum resolution for Oracle Database 11g Release 2 (11.2) is 1024 x 768 or higher.
Operating System Requirements

LOAD DATA
infile 'C:\app\201510\20151004.csv'
badfile 'C:\app\err_log\10\20151004_bad_data.txt'
APPEND into table TBL_KEYWORD_TOKEN_DAY
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
XDATE date "yyyy-mm-dd hh24:mi:ss",
TAG1,
TAG2,
VAL
)


這是 SQL*Loader control file 的內容
指令: 

sqlldr userid=biduser@tsdb control=C:\app\control\10\ctl_append_20151004.ctl data=C:\app\201510\20151004.csv log=C:\app\log\10\log_20151004.log errors=100000

// —–

-------
LOAD DATA
INFILE dump_keyword_20140908
APPEND INTO TABLE TBL_KEYWORD_DAY
FIELDS TERMINATED BY ":::" 
(XDATE, XHOUR, TAG1, TAG2, VAL)