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

This article introduces the relevant knowledge of "how to import and export data in ORACLE". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Brief introduction:

Sqluldr2: one of the professional tools for exporting large amounts of data, its efficiency is 70% faster than that of ordinary export. (Sqlldr: one of the professional tools for import, please note the difference between the two tools.) When in use, it is best to write to disk with fast speed, good network and fast network speed.

List of tools:

Use different export tools for different platforms:

Windows: sqluldr2.exe

Linux (32-bit): sqluldr2_linux32_10204.bin

Linux (64 bit): sqluldr2_linux64_10204.bin

Instructions for use (Windows platform):

To use sqluldr2:

1. Open the run à cmd to enter the current directory of sqluldr2.exe

two。 Parameter introduction

User= user / password @ tns

Query= "query statement"

Path to File= export

When Head= outputs information, yes means to want the header, and no means not to.

Note: to see more parameters, please enter sqluldr2 help=yes

C:\ Users\ meng\ Desktop\ sqluldr2 > sqluldr2.exe help=yes

SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1

Copyright Lou Fangxin (AnySQL.net) 2004-2010, all rights reserved.

License: Free for non-commercial useage, else 100 USD per server.

Usage: SQLULDR2 keyword=value [, keyword=value,...]

Valid Keywords:

User = username/password@tnsname

Sql = SQL file name

Query = select statement

Field = separator string between fields

Record = separator string between records

Rows = print progress for every given rows (default, 1000000)

File = output file name (default: uldrdata.txt)

Log = log file name, prefix with + to append mode

Fast = auto tuning the session level parameters (YES)

Text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).

Charset = character set name of the target database.

Ncharset= national character set name of the target database.

Parfile = read command option from parameter file

Read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level

Sort = set SORT_AREA_SIZE at session level (UNIT:MB)

Hash = set HASH_AREA_SIZE at session level (UNIT:MB)

Array = array fetch size

Head = print row header (Yes | No)

Batch = save to new file for every rows batch (Yes/No)

Size = maximum output file piece size (UNIB:MB)

Serial = set _ serial_direct_read to TRUE at session level

Trace = set event 10046 to given level at session level

Table = table name in the sqlldr control file

Control = sqlldr control file and path.

Mode = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE

Buffer = sqlldr READSIZE and BINDSIZE, default 16 (MB)

Long = maximum long field size

Width = customized max column width (w _ 1 / w _ 2)

Quote = optional quote string

Data = disable real data unload (NO, OFF)

Alter = alter session SQLs to be execute before unload

Safe = use large buffer to avoid ORA-24345 error (Yes | No)

Crypt = encrypted user information only (Yes | No)

Sedf/t = enable character translation function

Null = replace null with given value

Escape = escape character for special characters

Escf/t = escape from/to characters list

Format = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs.

Exec = the command to execute the SQLs.

Prehead = column name prefix for head line.

Rowpre = row prefix string for each line.

Rowsuf = row sufix string for each line.

Colsep = separator string between column name and value.

Presql = SQL or scripts to be executed before data unload.

Postsql = SQL or scripts to be executed after data unload.

Lob = extract lob values to single file (FILE).

Lobdir = subdirectory count to store lob files.

Split = table name for automatically parallelization.

Degree = parallelize data copy degree (2-128).

Hint = MySQL SQL hint for the Insert, for example IGNORE.

Unique = Unique Column List for the MySQL target table.

Update = Enable MySQL ON DUPLICATE SQL statement (YES/NO).

For field and record, you can use '0x' to specify hex character code

\ r=0x0d\ n=0x0a | = 0x7c, = 0x2c,\ t=0x09,: = 0x3a, # = 0x23, "= 0x22'= 0x27

C:\ Users\ meng\ Desktop\ sqluldr2 >

Code example 1:

Sqluldr2.exe USER= user / password @ tnsQUERY= "select / * + parallel (8) * / * from cs_XXX dt,cfg_XXX devdim105 wheredt.starttime > = to_date ('2012-06-27 00 purse 00mm hh34:mi:ss') and dt.starttime

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.