SQL*Loader does not Truncate the Segments During Load (Doc ID 763483.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0.2 to 11.1.0.7
This problem can occur on any platform.

Symptoms

You started SQL*Loader to populate a table with given data and used the option TRUNCATE to reinitialize the extents. After load (despite only few records are inserted) the number of extents is identical with the number of extents used by table before load. Let's follow the next example to demonstrate this:

-- create the table and index
connect test/test

drop table tab001;
purge recyclebin;

create table tab001
(
   id   number,
   text varchar2(20)
);
create index tab001_ind on tab001 (id);

-- populate the table with 10000 rows
begin
  for i in 1..10000 loop
    insert into tab001 values (i, 'TEXT '||to_char (i));
  end loop;
  commit;
end;
/

-- select the number of used extents by table and index
connect / as sysdba
select owner,
       segment_name,
       segment_type,
       bytes,
       blocks,
       extents
from   dba_segments
where  segment_name like 'TAB001%' and
       owner = 'TEST'

OWNER SEGMENT_NAME SEGMENT_TYPE      BYTES     BLOCKS    EXTENTS
----- ------------ ------------ ---------- ---------- ----------
TEST  TAB001       TABLE            262144         32          4
TEST  TAB001_IND   INDEX            196608         24          3

Start SQL*Loader with control file:

load data
infile *
truncate <-- table is truncated
into table tab001
fields terminated by '|'
trailing nullcols
(
   id,
   text
)

begindata
1|TEXT 1 <-- insert only 1 row

The results from statement above after load are identical with the results obtained before load:

OWNER SEGMENT_NAME SEGMENT_TYPE      BYTES     BLOCKS    EXTENTS
----- ------------ ------------ ---------- ---------- ----------
TEST  TAB001       TABLE            262144         32          4
TEST  TAB001_IND   INDEX            196608         24          3

If you first perform:

truncate table test.tab001;

and then start the load, the results are the following:

OWNER SEGMENT_NAME SEGMENT_TYPE      BYTES     BLOCKS    EXTENTS
----- ------------ ------------ ---------- ---------- ----------
TEST  TAB001       TABLE             65536          8          1
TEST  TAB001_IND   INDEX             65536          8          1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms