SQL*Loader direct loading: ORA-04031 (shm problems)

From: Gerhard Moeller <Gerhard.Moeller_at_OFFIS.Uni-Oldenburg.de>
Date: 1997/06/26
Message-ID: <5otbfj$37d_at_news.Informatik.Uni-Oldenburg.DE>#1/1

Hi,

I try to load data with the SQL*Loader in several tables (with long types in it). This works perfectly fine with conventional load, but fails badly with direct load.

The details:

(i) System: DEC alpha OSF 3.2, Oracle RDBMS 7.3.2
(ii) Tables: 5 tables, most of them with long types.
(iii) SQL*Loader control file: I use CHAR(5000) to convert the long
data types (5000 bytes allocation space is enough for me), APPEND to the table, the rows are not pre-sorted.
(iv) Yes, I have enabled direct loading, the SAME script used to WORK
a few days ago!!! (Although with slightly other data)
(v) Yes, I also tried to load an EMPTY datafile -- same result!

(vi) This is the shell output of sqlldr:

---------------8< -------------------

gemoe_at_gerhard 63> sqlldr userid=xxx/yyy control=soif.ctl log=soif.log bad=soif.bad data=bla.dat discard=soif.dsc direct=true

SQL*Loader: Release 7.3.2.3.0 - Production on Thu Jun 26 10:51:39 1997

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.

Error calling once/data file initialization ORA-04031: unable to allocate 210768 bytes of shared memory ("unknown object","session heap","field char. conversion buffer")

SQL*Loader-925: Error while logging off
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

---------------8< -------------------

(vii) Yes, I *think* I have enough shared memory:

• I have recompiled the kernel with "shmmax 32000000"
• I have started Oracle with 18 MByte SGA size.
• the output of ipcs -a: ---------------8< ------------------- gemoe_at_gerhard 64> ipcs -a

Message Queues:
T ID KEY MODE OWNER GROUP CREATOR CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME

q       0 1090519470 --rw-------      root   system     root   system
0     0  16384     0     0 no-entry no-entry 12:10:22

Shared Memory:
T      ID     KEY      MODE        OWNER    GROUP  CREATOR   CGROUP
NATTCH     SEGSZ  CPID  LPID   ATIME    DTIME    CTIME 
m       0 844348508 --rw-rw-rw-      root   system     root   system
2       648   391   426 12:10:51 12:10:51 12:10:51
m       4 126610827 --rw-r-----    oracle      dba   oracle      dba
9       784   595  1349 10:51:46 10:52:46 12:11:43
m       5 193719691 --rw-r-----    oracle      dba   oracle      dba

9 18866176 595 1349 10:51:46 10:52:46 12:11:43

Semaphores:
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
s 0 1090519470 --ra------- root system root system 1 12:10:22 12:10:22

---------------8< -------------------

• the output of show sga SVRMGR> show sga Total System Global Area 18838568 bytes Fixed Size 52424 bytes Variable Size 12199776 bytes Database Buffers 6553600 bytes Redo Buffers 32768 bytes ---------------8< -------------------

(viii) Yes, I also tried it without any index beeing defined!

----> What the hell is my problem? Why did this script work before? Why does it not now? Why even not with empty data and no index? Why does it work in conventional mode (with a really huge bindarray...)? What does the error of sqlldr mean?

HELP, I'm totally stuck!

                Gerhard.

-- 
 Dipl. Inform. Gerhard Möller -- Gerhard.Moeller_at_OFFIS.Uni-Oldenburg.DE

[Quoted] OFFIS                   | | | | | |       Tel.:   0441/9722-122
Escherweg 2             | | | | | |       Sekr.:  0441/9722-113 oder -101
D-26121 Oldenburg       |O|F|F|I|S|       Fax:    0441/9722-102

Received on Thu Jun 26 1997 - 00:00:00 CEST