"unique" violation problem

I created a table:

create table test_unique
(name VARCHAR2(20) unique NOT NULL,
 color VARCHAR2(20),
 temp VARCHAR2(20)
)
/

Then used SQL Loader to append data:

________Control file__________
options (direct = true)
load data
infile 'raw_data.file'
badfile 'raw_data.bad'
discardfile 'raw_data.dsc'
append into table test_unique
fields terminated by "#"
(name VARCHAR2(20) unique NOT NULL,
 color VARCHAR2(20),
 temp VARCHAR2(20)
)

________Data file_____________
Scott Tempo#red#north
Jeff Geo#green#east

• THE PROBLEM IS, I can load the SAME data twice into the table (as checked on SQL command line: select count(1) from test_unique) and the same names appear TWICE in the "name" field where "UNIQUE" was defined. NO ERROR was found on the second load. However, a third load gets an error saying:

"Error calling col array build, block formatter ORA-26031: index maintenance error, the load cannot continue ORA-26028: index ZHU.SYS_C00627 initially in unusable state"

Any hint what was going wrong?

Thanks!

Zhiliang Received on Wed Apr 04 2001 - 11:04:25 CDT