2

A customer received this error while importing serveral files. The import routine (VB.NET) is using SQL*Loader (Release 11.2.0.3.0) via Process on each table separately (64 in total) and is called weekly for many years now. Last week following error was thrown:

value used for ROWS parameter changed from 1024 to 795 (all tables are imported with ROWS=1024, no BINDARRAY parameter specified)

SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table x ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","modification ")

I'd googled the ORA-4031 and determined the current memory informations:

  • 3,08 GB (shared pool) (select sum(bytes), pool from v$sgastat GROUP BY pool),
  • 922MB free memory (select * from v$sgastat where name = 'free memory';) and
  • 1,18 GB shareable memory (select sum(SHARABLE_MEM) from v$db_object_cache;)

To resolve the issue several posts pointed out to look at the views v$SGA_TARGET, v$PGA_TARGET, v$SGA_TARGET_ADVICE, v$PGA_TARGET_ADVICE, v$SGA_DYNAMIC_COMPONENTS and so on, but i have no dba privilege. (table or view does not exist).

Thus i'd like to know whether i can resolve the issue only with SQL*Loader settings or must a DBA investigate into this?

EDIT:
control file: LOAD DATA INFILE '<filepath>' INTO TABLE x TRUNCATE(<columns>)
call: sqlldr.exe userid=... log=... ctl=... bad=... rows=1024

Frederick
  • 23
  • 3

1 Answers1

2

If this was a one-time error, just try running SQL*Loader again.

If this happens repeatedly, you will need the help of the DBA.

The above views barely contain anything informative related to this issue. (Also there is no such view as V$SGA_TARGET or V$PGA_TARGET.) When this error occurs, the database instance creates a trace file on the database server (alert and file location can be found in the alert log), that is what you will need for further investigation.

Balazs Papp
  • 41,488
  • 2
  • 28
  • 47