Oracle database backup and recovery (2): SQL * Loader

I. Basic Knowledge

Oracle SQL * LOADER can load externally formatted text data to database tables. It is usually used in combination with the SPOOL Method for exporting text data.

1. Command Format

SQLLDR keyword = value [, keyword = value,…]

Example: $ sqlldr user/pwd control = emp. ctl data = emp. dat bad = emp. bad log = emp. log

2. Control File

SQL * LOADER can find the data to be loaded based on the control file. Analyze and interpret the data.

The control file consists of three parts. For details about the parameters, refer to the help documentation: 1. global Options, rows, number of skipped records, etc.; 2. input data specified by the INFILE clause; 3. data features.

Comment: -- comment

Example:

Load data infile *

Append -- in addition to append, there are insert, replace, truncate, and other methods

Into table emp fields terminated B y' |'

(

No float external, name char (20 ),

Age integer external,

Duty char (1), salary float external,

Upd_ts date (14) 'yyyymmddhh24mis ')

Begindata

100000000003 | Mulder | 000020 | 1 | 000000005000 | 20020101000000

100000000004 | Scully | 000025 | 2 | 000000008000 | 20020101235959

The infile option in the control file has the same meaning as the data option in the sqlldr command line. If infile * is used, the data is in the region where the control file starts with begin data. Some options: fields terminated by whitespace fields terminated by x09 FILLER_1 FILLER. // a specified column will not be loaded.

DEPTNO position (), DNAME position (*: 16), // specify the position of the column seqno recnum // load the row number

SKIP n // specify the number of rows of data that can be skipped during import

3. Data Files

The data row set defined by the data format of the control file,

Example:

100000000001 | Tom | 000020 | 1 | 000000005000 | 20020101000000

100000000002 | Jerry | 000025 | 2 | 000000008000 | 20020101235959

Fixed format, variable format, and stream record format:

Fixed format:

When the data is in a fixed format (the same length) and is obtained in the file, use INFILE "fix n"

Load data

Infile example. dat "fix 11"

Into table example

Fields terminated B y, optionally enclosed"

(Col1 char (5), col2 char (7) example. dat:

001, cd, 0002, fghi,

00003, lmn,

1, "pqrs ",

0005, uvwx,

Variable format:

When the data is in a variable format (different lengths) and is obtained in a file, use INFILE "var n". For example:

Load data

Infile example. dat "var 3"

Into table example

Fields terminated B y, optionally enclosed"

(Col1 char (5), col2 char (7) example. dat:

009 hello, cd, 010 world, im,

012my, name is,

Stream record format: // Stream-recored format: load data infile xx. dat "str |"

Into table xx field terminated B y, optionally enclosed"

(Col1 char (5), col2 char (7 ))

Example. dat:

Hello, ccd, |

World, bb, |

4. Bad files

Bad = emp. bad file contains records rejected by SQL * Loader. Rejected records may be non-conforming records.

5. log files and log information

Log = emp. log when SQL * Loader starts execution, it automatically creates a log file. The log file package contains the total knot of the load and the loading error information.

Ii. Advanced options

1. Conventional Path Load and Direct Path Load

Conventional-path Load: Upload through Conventional channels.

Features: commit, always gen redo logs, enforce all constraints, fire insert triggers, can load into cluster, other user can make change

Rows: number of records submitted each time

Bindsize: buffer for each submission record

Readsize: Used in pairs with bindsize. Smaller ones are automatically adjusted to larger ones.

Sqlldr first calculates the length of a single record, multiplied by rows. If it is smaller than bindsize, it will not try to expand. rows will fill the bindsize. If it is exceeded, bindsize will prevail. Command:

$ Sqlldr dbuser/oracle control = emp. ctl log = emp. log rows = 10000 bindsize = 8192000

Direct-Path Load:

You can skip the database-related logic by directly uploading data to a data file without SQL parsing.

Features: save, conditionly gen redo logs, enforce pk uk nn, not fire triggers, can not load into cluster, other user can not make change command:

$ Sqlldr dbuser/oracle control = emp. ctl log = emp. log direct = true

2. How to export text data using SPOOL

The imported data file can be generated using the SPOOL export text data method.

SQL * PLUS Environment Settings

Set newpage none heading off space 0

PAGESIZE 0 set trimout on trimspool on linesize 2500

Note: The LINESIZE should be slightly larger to avoid data truncation. It should be used with the corresponding TRIMSPOOL to prevent too many trailing spaces in the exported text.

However, if the LINESIZE is too large, the export speed will be greatly reduced. In addition, it is recommended that you do not export data using PLSQL in WINDOWS, which is slow, run the SQLPLUS command in the smallest window of COMMEND directly. If a field contains many line breaks, filter them to form a regular text file.

In general, we use the SPOOL method to export the tables in the database as text files, as shown below:

Set trimspool on

Set linesize 120 pagesize 2000 newpage 1 heading off term off spool path + file name

Select col1 |, | col2 |, | col3 |, | col4 | ...... From tablename;

Spool off

Iii. Script

1. Export data records in the table as field values using separators | separated. Dat file

#! /Bin/ksh

######################################## ##########################

# Name: unloadtable

# Function: This shell is used to export data records in a table.

# Use separators to export field values | separated. Dat file

# Editor:

# Date: 2006.03.18

######################################## ##########################

If [$ #-ne 3]

Then echo "usage: unloadtable tablename username password ."

Exit 0

Fi

# Preparations

Echo "set heading off">/tmp/$ 1.col

Echo "set pagesize 0">/tmp/$ 1.col

Echo "set linesize 800">/tmp/$ 1.col

Echo "set feedback off">/tmp/$ 1.col

Echo "set tab off">/tmp/$ 1.col

Echo "select column_name |, from user_tab_columns where lower (table_name) = $1 order

Column_id; ">/tmp/$ 1.col

# Generate a select statement

Echo "set heading off">/tmp/$ 1.sel

Echo "set pagesize 0">/tmp/$ 1.sel

Echo "set linesize 800">/tmp/$ 1.sel

Echo "set feedback off">/tmp/$ 1.sel

Echo "set tab off">/tmp/$ 1.sel

Echo "select">/tmp/$ 1.sel

Echo 'sqlplus-s $2/$3 </tmp/$1. col '| sed "s /, /|/g "| sed" s/| $/g "| sed" s/date/"date"/g"

>/Tmp/$ 1.sel

# Generate a dat file

# Echo "from $1;/">/tmp/$ 1.sel execute select once more due/

Echo "from $1;">/tmp/$ 1.sel

Sqlplus-s $2/$3 </tmp/$ 1.sel> $ export tmp.dat

# Awk {if (FNR! = 1) print $0} $ export tmp.dat> $ 1.dat