Oracle Commit frequency

[sheema]

Hi,

I am using Oracle Enterprise stage to Load table into a Oracle database using the below option

Write Method : Load

Write Mode :Append

I am using the below environment variable

$APT_ORACLE_LOAD_OPTIONS --> OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)

is there a way that i specify the Oracle to Commit all the rows at once.

Thanks
sheema

[ray.wurlod]

Bulk load is non-transactional, so COMMIT doesn't come into it.

[chulett]

For DIRECT=TRUE that would be correct. For FALSE it falls back to conventional inserts with commits. And you only get 'all rows at once' by setting the commit interval to a very high number AFAIK. Other options will affect the interval like read size and bind size. You'd really need to have a chat with your DBA to get all of the gory details.

[sheema]

Thanks for the reply.

Do we set the commit row interval using the below 2 environment variables, in this case(I am not sure as this is not Upsert Load Method)

$APT_ORAUPSERT_COMMIT_TIME_INTERVAL
$APT_ORAUPSERT_COMMIT_ROW_INTERVAL

please guide me ,how to set the commit intervals when I am using the Load Method.

$APT_ORACLE_LOAD_OPTIONS --> OPTIONS(DIRECT=FALSE,PARALLEL=TRUE)

[chulett]

No, they are only applicable to the Upsert method and are not used by a Load. You would need to expand on your "OPTIONS" clause to include ROWS and, as noted, you really should be discussing that subject with your DBA.

[sheema]

Thanks for the reply. I will discuss this with my dba.