Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to import and export large text data to the database". In daily operation, I believe that many people have doubts about how to import and export large text data to the database. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to import and export large text data to the database". Next, please follow the editor to study!

First, use sql load

Create a tabl

Create table ext_gv_tmp_amazon_sku

(

Skucode varchar2 (255char)

Eancode varchar2 (255char)

)

Configuration control file

More input.ctl

Load data

Infile input.txt

Badfile t.bad

Discardfile t.dsc

Append into table ext_gv_tmp_amazon_sku-imported tables

Fields terminated by "|"-separator

Trailing nullcols-allow columns to have null values

(skucode,eancode)-imported fields

Import command

Set the import client character set first, otherwise the Chinese will be garbled.

Export NLS_LANG=AMERICAN_AMERICA.UTF8

At the same time, see if the character set of the system is set correctly.

[oracle@rac-test1 pandump] $cat / etc/sysconfig/i18n

LANG= "en_US.UTF-8"

SYSFONT= "latarcyrheb-sun16"

Finally import the data

[oracle@rac-test1 pandump] $sqlldr panhf/oracle control=input.ctl

SQL > select count (*) from ext_gv_tmp_amazon_sku

COUNT (*)

-

2409530

Second, use external tables

Import data using external tables

1. Create an oracle directory and authorize it to relevant accounts

CREATE OR REPLACE DIRECTORY pandump

AS'/ oradata/pandump'

Grant read,write on DIRECTORY pandump to panhf

2. Create an external table. Pay attention to the character set setting here. If you do not set the Chinese text, you will not be able to read it, resulting in data loss.

Create table ext_gv_tmp_amazon_sku

(

Skucode varchar2 (255char)

Eancode varchar2 (255char)

)

Organization external (

Type oracle_loader

Default directory pandump

Access parameters

(

Records delimited by newline

Logfile pandump:'ext_gv_tmp_amazon_sku.log'

Badfile pandump:'ext_gv_tmp_amazon_sku.bad'

Discardfile pandump:'ext_gv_tmp_amazon_sku.disc'

Characterset 'AL32UTF8'

Fields terminated by "|" lrtrim

Missing field values are null

(

Skucode

Eancode

)

)

Location ('ext_ids_1.txt')

)

Reject limit unlimited

/

3, copy the data cts to another table

Select count (*) from ext_gv_tmp_amazon_sku

COUNT (*)

-

2409530

Create table panhf.ext_gv_tmp_amazon_sku as select * from ext_gv_tmp_amazon_sku

Table created.

Third, data export text

Cat exp.sql

Set echo off

Set feedback off

Set newpage none

Set verify off

Set pagesize 0

Set term off

Set trims on

Set linesize 600

Set heading off

Set timing off

Set numwidth 40

Spool / home/oracle/empInfor.txt

Select skucode | |'|'| | eancode from panhf.ext_gv_tmp_amazon_sku

Spool off

Execute script

@ exp.sql

At this point, the study on "how to import and export large text data to the database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.