Home » RDBMS Server » Server Utilities » Default Commit Count in SqlLoader
Default Commit Count in SqlLoader [message #72095] Thu, 06 March 2003 21:35 Go to next message
Deborrah
Messages: 8
Registered: July 2002
Junior Member
What is the default commit count? It seems to vary
with different tables and tables of different sizes.

TIA,
Debby

Report message to a moderator

Re: Default Commit Count in SqlLoader [message #72096 is a reply to message #72095] Fri, 07 March 2003 07:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 
-- i dont know about the defualt..
-- i believe u can manipulate that with rows and bindsize options...
Microsoft Windows 2000 [[Version 5.00.2195]]
(C) Copyright 1985-2000 Microsoft Corp.

C:>sqlldr userid=mag/mag control=test.ctl

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 7 08:55:04 2003

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4

C:>sqlldr userid=mag/mag control=test.ctl rows=1

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 7 08:55:13 2003

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 4

C:>sqlldr userid=mag/mag control=test.ctl rows=2

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 7 08:55:16 2003

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 4

C:>sqlldr userid=mag/mag control=test.ctl rows=6

SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 7 08:55:20 2003

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4

Report message to a moderator

Re: Default Commit Count in SqlLoader [message #72099 is a reply to message #72095] Fri, 07 March 2003 17:05 Go to previous message
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
Senior Member
Excerpts from Oracle on-line documentation:

"ROWS (rows per commit)
Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.

Conventional path loads only: ROWS specifies the number of rows in the bind array. See Bind Arrays and Conventional Path Loads.

Direct path loads only: ROWS identifies the number of rows you want to read from the datafile before a data save. The default is to read all rows and save data once at the end of the load. See Using Data Saves to Protect Against Data Loss.

Because the direct load is optimized for performance, it uses buffers that are the same size and format as the system's I/O blocks. Only full buffers are written to the database, so the value of ROWS is approximate."

"If you invoke SQL*Loader without specifying any parameters, SQL*Loader displays a help screen similar to the following. It lists the available parameters and their default values.

sqlldr
...
SQL*Loader: Release 9.2.0.1.0 - Production on Wed Feb 27 12:06:17 2002

(c) Copyright 2002 Oracle Corporation. All rights reserved.

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

Valid Keywords:

userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow (Default all)
skip -- Number of logical records to skip (Default 0)
load -- Number of logical records to load (Default all)
errors -- Number of errors to allow (Default 50)
rows -- Number of rows in conventional path bind array or between
direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- Size of conventional path bind array in bytes (Default 256000)
silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (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)
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)

PLEASE NOTE: Command-line parameters may be specified either by position or by keywords.
An example of the former case is 'sqlldr scott/tiger foo'; an example of the latter is 'sqlldr
control=foo userid=scott/tiger'. One may specify parameters by position before but not after
parameters specified by keywords. For example,'sqlldr scott/tiger control=foo logfile=log' is
allowed, but 'sqlldr scott/tiger control=foo log' is not, even though the position of the
parameter 'log' is correct."

Report message to a moderator

Previous Topic: Direct path
Next Topic: Oracle Export and Import
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 14 20:23:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.