Redo Block Size

OS specific and coded into oracle code.

select max(lebsz) from sys.x$kccle;

Solaris, AIX, Windows NT/2000, Linux, Irix, DG/UX, OpenVMS, NetWare, UnixWare, DYNIX/ptx :- 512 bytes

HP-UX, Tru64 Unix :- 1024 bytes

SCO Unix, Reliant Unix :- 2048 bytes

MVS, MPE/ix :- 4096 bytes

Useful SQL Plus formats

set echo off -- suppress showing sql in result set
set feedback off -- eliminate row count message
set linesize 100 -- make line long enough to hold data
set pagesize 0 -- suppress headings and page breaks
set sqlprompt '' -- eliminate SQL*Plus prompt from output
set trimspool on -- eliminate trailing blanks

SQL Loader with Null columns

TRAILING NULLCOLS tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

load data
infile data.csv
replace
into table test_table
TRAILING NULLCOLS
(
col1 TERMINATED BY ',',
col2 TERMINATED BY ',',
col3 TERMINATED BY WHITESPACE
)


Loading string terminated by new line

control file construct

load data
infile product.csv "str '\n'"
append into table feature_8k
fields terminated by "," optionally enclosed by "'"
(col1,col2,col3,col4,col6)

data file in the format of

12503,'HABQ5FAI1','ROFA','28','N'
12503,'HABQ5FAI1','ROFA','29','N'
12503,'HABQ5FAI1','ROFA','30','N'
12503,'HABQ5FAI1','ROFA','31','Y'
12503,'HABQ5FAI1','ROFA','32','Y'

load with command

sqlldr asanga/asa@db1 control=controlp.ctl data=product.csv