Bug with 8.0.4 in sql*loader?

From: David Shi <dshi_at_magpage.com>
Date: 24 Jun 1998 00:18:03 GMT
Message-ID: <6mpgjr$ojl$0@204.179.92.138>

Environment:
Sun Enterprise 6000
Solaris 2.5.1
Oracle 8.0.4.1 (just migrated and patch 8.0.4.1.0 applied) and patch 675239 applied

Problem: Oracle fail when use sqlload to load data of about 300MB into Oracle table.

Description:

datafile in tablespace is setup to auto-grow as:

  '/oradata1/dbms/crdt_attr_98_05_1.dbf' size 1m

       autoextend on next 100m maxsize 2001m, (this is a litter faster up front cause I have 33 datafiles to create)

The Oracle table is like:

  initial 100m
  next 100m
  pctincrease 0

when it tried to create the second extend, the following error occured:

  Error calling col array build, block formatter   ORA-27044: unable to write the header block of file   SVR4 Error: 25: Inappropriate ioctl for device   Additional information: 3
  ...
  Segmentation Fault - core dumped

I thought this might be related to "autoextend", so I resize the data file to 1GB and re-run it, Oracle instance crashed after 3 (100m) extents with the following message:

  Error calling col array build, block formatter   ORA-03113: end-of-file on communication channel  

  Error calling once/data file finishing   ORA-03114: not connected to ORACLE  

  Error opening cursor: [102]
  ORA-03114: not connected to ORACLE  

  ORA-24323: value not allowed
  SQL*Loader-523: SQL*Loader-523: SQL*Loader-523: SQL*Loader-523:   ...

This is what in the alert_x.log file:

  ORA-1653: unable to extend table DBMS.CRDT984_0 by 6400 in tablespace CRDT_ATTR_98_05
  Tue Jun 23 13:41:31 1998
  ORA-1653: unable to extend table DBMS.CRDT984_0 by 6400 in tablespace CRDT_ATTR_98_05
  Tue Jun 23 13:43:55 1998
  alter database datafile '/oradata2/dbms/crdt_attr_98_05_14.dbf' resize 1001m   Tue Jun 23 13:44:47 1998
  Completed: alter database datafile '/oradata2/dbms/crdt_attr_   Tue Jun 23 14:15:47 1998
  Errors in file /appl/oracle/admin/dbms/bdump/dbms_pmon_23124.trc:   ORA-07445: exception encountered: core dump [] [SIGBUS] [Invalid address alignment] [825504062] [] []
  Tue Jun 23 14:15:53 1998
  Instance terminating due to error 472   Instance terminated by LGWR, pid = 23130

Anyone has encountered similar problem?

David

The script to create tablespace:

create tablespace crdt_attr_98_05
datafile
'/oradata1/dbms/crdt_attr_98_05_1.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata2/dbms/crdt_attr_98_05_2.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata3/dbms/crdt_attr_98_05_3.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata4/dbms/crdt_attr_98_05_4.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata5/dbms/crdt_attr_98_05_5.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata6/dbms/crdt_attr_98_05_6.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata1/dbms/crdt_attr_98_05_7.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata2/dbms/crdt_attr_98_05_8.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata3/dbms/crdt_attr_98_05_9.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata4/dbms/crdt_attr_98_05_10.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata5/dbms/crdt_attr_98_05_11.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata6/dbms/crdt_attr_98_05_12.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata1/dbms/crdt_attr_98_05_13.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata2/dbms/crdt_attr_98_05_14.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata3/dbms/crdt_attr_98_05_15.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata4/dbms/crdt_attr_98_05_16.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata5/dbms/crdt_attr_98_05_17.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata6/dbms/crdt_attr_98_05_18.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata1/dbms/crdt_attr_98_05_19.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata2/dbms/crdt_attr_98_05_20.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata3/dbms/crdt_attr_98_05_21.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata4/dbms/crdt_attr_98_05_22.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata5/dbms/crdt_attr_98_05_23.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata6/dbms/crdt_attr_98_05_24.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_25.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_26.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_27.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_28.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_29.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_30.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_31.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_32.dbf' size 1m autoextend on next 100m maxsize
2001m,
'/oradata7/dbms/crdt_attr_98_05_33.dbf' size 1m autoextend on next 100m maxsize
2001m
nologging
default storage (
  initial 100m
  next 100m
  pctincrease 0
)

• The script to create table:

create table dbms.CRDT984_0
(
accno varchar2(19),

att_001 number,
att_002 number,
att_003 number,
att_004 number,
att_005 number,
att_006 number,
att_007 number,
att_008 number,
att_009 number,
att_010 number,
att_011 number,
att_012 number,

...
)
storage (
  initial 100m
  next 100m
  pctincrease 0
)
tablespace CRDT_ATTR_98_05
pctfree 2
;

• The sqlload control file:

options (rows=100000)
unrecoverable
load data
infile piper.dat
truncate into table dbms.CRDT984_0 fields terminated by "|" TRAILING NULLCOLS (
accno,

att_001 nullif att_001=blanks,
att_002 nullif att_002=blanks,
att_003 nullif att_003=blanks,
att_004 nullif att_004=blanks,
att_005 nullif att_005=blanks,
att_006 nullif att_006=blanks,

...
) Received on Tue Jun 23 1998 - 19:18:03 CDT