Oracle 11.2.0.3 can still throw a curveball when it comes to LOB columns, notably with the SQL*Loader utility. Under the right conditions SQL*Loader can fail to load a table, throwing an ORA-08102. Let’s look at the issue to see what configuration can create that result.
LOB columns are interesting all by themselves due to the storage and redo mechanisms employed; add a function-based index to the mix and it could be a recipe for failure. The following example illustrates what can go wrong. First, a table is created that contains a LOB column:
SQL> --
SQL> -- Create test table
SQL> --
SQL> -- Include LOB column
SQL> --
SQL> create table lob_idx_tst(
2 id number,
3 lobdesc varchar2(40),
4 lobdata clob,
5 ldate date);
Table created.
SQL>
SQL> --
SQL> -- Create Function-based index
SQL> -- on LOB column
SQL> --
SQL> create index lob_dat_idx
2 on lob_idx_tst(id, dbms_lob.getlength(lobdata));
Index created.
SQL>
Now the fun begins: use SQL*Loader to load the table in conventional mode:
C:UsersDocumentsOracle_stuffexamples>sqlldr bing/###########@smedley lob_idx_tst.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 20 21:07:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-462: error inserting LOB into column LDATA, row 2, table
LOB_IDX_TST
ORA-8102: index key not found, obj# , file , block ()
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
Interestingly using a lower version of SQL*Loader into the same table in the same database succeeds:
C:UsersDocumentsOracle_stuffexamples>sqlldr bing/###########@smedley lob_idx_tst.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on Sun Dec 20 21:09:58 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 9
As another interesting ‘wrinkle’ using direct mode with the 11.2.0.3 utility also succeeds:
C:UsersDocumentsOracle_stuffexamples>sqlldr bing/###########@smedley lob_idx_tst.ctl direct=true
SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 20 21:17:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 9.
And if the function-based index is dropped the load runs without error:
SQL> drop index lob_dat_idx;
Index dropped.
SQL> $sqlldr bing/###########@smedley lob_idx_tst.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Sun Dec 20 21:22:47 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 9
This is a perplexing issue that is apparently restricted to Oracle 11.2.0.3; no earlier or later versions exhibit this behavior. Oracle Support has no information other than the workaround to use DIRECT=TRUE or, obviously, drop the function-based index. At the very least this isn’t a ‘show stopper’ as a workaround does exist.
Bugs can and do rear their ugly heads when software is modified to provide a new release; some may be regression bugs (where previously fixed behavior reappears) but this doesn’t appear to be in that category. What is especially puzzling is that only one code path is affected; conventional-path loads are the only ones throwing the ORA-08102 error. Given that direct-path loads can generate large amounts of empty space (as can direct load inserts using the /*+ APPEND */ hint) it comes down to a choice between dropping a function-based index before executing a data load and incurring the cost of recreating that index after the load is complete or using DIRECT=TRUE and possibly putting considerable amounts of free space into a table. Time or space, that is the choice to be made with 11.2.0.3 in this situation. Of course another possibility is to upgrade the database, presuming third-party software vendors support Oracle releases greater than 11.2.0.3, which shouldn’t be an issue for current vendors. The upgrade problem lies in older software, from vendors who have stopped supporting Oracle in their latest releases or vendors who have gone out of business and can provide no updates or support for releases newer than the existing software can support. Lest you think that doesn’t happen I know of at least two major enterprises still using software that can’t ‘talk’ to any Oracle release newer than 8i.
SQL*Loader is an excellent utility which can reduce the time it takes to load large volumes of data from flat-file sources. This appears to be the only major stumbling block in Oracle 11.2.0.3 for that utility. Knowing there are workarounds other than a database upgrade can make it easier to deal with. The key is to understand that choosing wisely may mean having to choose again.
