SQL*Loader-926, ORA-04031 unable to allocate x bytes of shared memory
2
votes
1
answer
1105
views
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 '' INTO TABLE x TRUNCATE()
call: sqlldr.exe userid=... log=... ctl=... bad=... rows=1024
Asked by Frederick
(23 rep)
Oct 4, 2015, 06:30 PM
Last activity: Oct 5, 2015, 09:39 AM
Last activity: Oct 5, 2015, 09:39 AM