SQL*Loader direct loading: ORA-04031 (shm problems)
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 dba9 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-102Received on Thu Jun 26 1997 - 00:00:00 CEST