SQLLDR Data Migration--oracle TXT import and export
http://coupling001.blog.163.com/blog/static/174925389201262093959635/
First, SQLLDR import txt
1. Preparation
a). txt file
This is to be saved as unsigned UTF-8.
b). Oracle Build Table
2. Write the control file Input_test.ctl
LOAD DATA
CHARACTERSET ' UTF8 ' --Character Set settings
INFILE ' D:\input_test.txt '--The text data path to be imported, can write multiple
REPLACE into table Input_test--Empty the original data and Import mode append import with append into table T_name
Fields terminated by X ' 09 '--delimited by tabs
Trailing Nullcols --Allow empty column import
(col1,col2)
Note:
InFile ' D:\input_test.txt ' indicates the path of the data file that needs to be mounted
Append into table test data loaded tables:
(1) Append indicates that there is data in the table, plus the following
(2) INSERT indicates that empty tables are loaded and data is stopped. Default value
(3) REPLACE If there is data in the original table, it will be deleted
(4) TRUNCATE if the data to be loaded is the same as the current data, the loaded data replaces the existing data.
Fields terminated by ', '
The data used to represent tab tabs, which are delimited by ', ', with by X ' 09 ', that is, 16 binary "09", are used for the import of the data of the Tab tab file that Excel converts. Common delimiters and ' | '
Multilingual programmable Character Set encoding: CHARACTERSET ' UTF8 '
Execute under 3.DOS
Sqlldr System/[email protected], Control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_ Test.bad
Valid keywords:
UserID-ORACLE Username/password
control– Control files
Log files for log– records
bad– Bad Data file
data– Data files
discard– Discarded data file
discardmax– maximum value allowed to discard data (all default)
Skip--Number of logical records to skip (default 0)
Load--Number of logical records to load (all default)
errors– number of error records allowed (default 50)
Rows-number of rows in conventional path bind array or between direct path data saves (record per commit, default: Regular path 64, all direct paths)
Bindsize--Size of conventional path bind array in bytes (default 256000)
The size of the buffer for each commit record (in bytes, default 256000)
Silent--Suppress output information (header,feedback,errors,discards,partitions)
direct– Import using Passthrough path (default false)
Parfile--Parameter file:name of file that contains parameter specifications
Parallel--Parallel import (default false)
FILE--file to allocate extents from
Skip_unusable_indexes--Disallow/allow unusable indexes or index partitions (default false)
Skip_index_maintenance--Do not maintain indexes, mark affected indexes as unusable (default false)
ReadSize--Size of Read buffer (default 1048576)
Paired with Bindsize, the smaller ones will automatically adjust to the larger ones. Sqlldr calculates a single record length, multiplied by rows, such as less than bindsize, does not attempt to expand rows to fill the bindsize, or bindsize if exceeded.
External_table--Use external table for load; not_used, Generate_only, EXECUTE (default not_used)
Columnarrayrows--Number of rows for direct path column array (Default 5000)
Streamsize--Size of direct path stream buffer in bytes (default 256000)
Multithreading--use multithreading in direct path
Resumable-Enable or disable resumable for current session (default false)
Resumable_name--text string to help identify resumable statement
Resumable_timeout--wait time (in seconds) for resumable (default 7200)
Date_cache--size (in entries) of date conversion cache (default 1000)
4. write. bat Batch
The above 3 steps have completed the TXT import, under Windows can also write the Sqlldr command as a batch file, double-click execution.
@echo off
Echo input_test
Pause -paused, suggested to join, so as not to error double-click to execute
@rem
Sqlldr System/[email protected], Control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_ Test.bad
@rem
@rem Sqlldr System/[email protected], Control=c:\input\input_test.ctl rows=100000
Pause
Second, SQLLDR export txt
Export txt with spool
1. Write Output.sql
Sqlplus System/[email protected] as SYSDBA--Connect Oracle
CHARACTERSET Al32utf8--Set the encoding set
Set Trimspool on--open pool
Spool C:\output\output.txt--Pool output path
Set pagesize 0--page Setup
Set heading off--Close the table header
Set Linesize 32767--Maximum row display
Select ' # ' | | col1| | ' #,# ' | | col2| | ' #,# ' | | col3| | ' # '--Set the required column format. In this example, columns are separated by commas, and the column contents are caused by #.
From Test_data;
Exit --Exit Sqlplus
Spool off--close the pool
Pause
Note: The above command can be executed directly under DOS.
2. write. bat Batch
Then write the bat call above the Outputsql file, as follows:
cd/
Set nls_lang=. Al32utf8--setting character set UTF8
CHCP 65001--Conversion code page UTF8
@echo off
echo Data output
Pause
@rem
Sqlplus System/[email protected] as SYSDBA @c:\dmp_sql\output.sql
@rem
Pause
Batch processing SQLLDR Data Migration--oracle TXT Import export (GO)