oracle direct-load,sqlldr direct方式导致索引失效实测

目的：验证sqlload的direct方式绕过唯一索引，可能引发索引unusable。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE T_NOPARTITION

2 ( "ID" NUMBER(10,0) CONSTRAINT id_pk PRIMARY

KEY,

3 "CREATED" DATE

4 )

5 TABLESPACE

"USERS"

6 ;

Table created

SQL> select

owner,CONSTRAINT_NAME,TABLE_NAME,STATUS,VALIDATED,INDEX_NAME from

USER_CONSTRAINTS where table_name='T_NOPARTITION';

OWNER CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED INDEX_NAME

------------ ------------------ ------------------- --------

------------- ----------

xxxxxx ID_PK T_NOPARTITION ENABLED VALIDATED ID_PK

SQL> select status from user_indexes a where

a.index_name='ID_PK';

STATUS

--------

VALID

说明：新建表，目前主键、索引状态都是正常的。

oracle@linux-119:~> cat

a.ctl

load data

infile '/home/oracle/a.txt'

append into table t_nopartition

fields terminated by '|'

(id,created)

oracle@linux-119:~> cat

a.txt

1|2013.01.01

2|2013.02.02

3|2013.03.03

oracle@linux-119:~>

sqlldr userid='xxxx/*****' control=a.ctl direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Wed Aug 14 12:06:36

2013

Copyright (c) 1982, 2009, Oracle and/or its

affiliates. All rights reserved.

Load completed - logical record count 3.

SQL> select * from t_nopartition;

ID CREATED

----------- -----------

1 2013/1/1 星期

2 2013/2/2 星期

3 2013/3/3 星期

SQL> select

owner,CONSTRAINT_NAME,TABLE_NAME,STATUS,VALIDATED,INDEX_NAME from

USER_CONSTRAINTS where table_name='T_NOPARTITION';

OWNER CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED INDEX_NAME

------------ ------------------ --------------- --------

------------- ----------

xxxxxx ID_PK T_NOPARTITION ENABLED VALIDATED ID_PK

SQL> select status from user_indexes a where

a.index_name='ID_PK';

STATUS

--------

VALID

说明：导入数据，数据并不违反约束，因此主键、索引状态均为正常。

oracle@linux-119:~>

sqlldr userid='xxxxxx/******' control=a.ctl direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Wed Aug 14 12:08:47

2013

Copyright (c) 1982, 2009, Oracle and/or its

affiliates. All rights reserved.

Load completed - logical record count 3.

SQL> select * from t_nopartition;

ID CREATED

----------- -----------

1 2013/1/1 星期

2 2013/2/2 星期

3 2013/3/3 星期

1 2013/1/1 星期

2 2013/2/2 星期

3 2013/3/3 星期

SQL> select

owner,CONSTRAINT_NAME,TABLE_NAME,STATUS,VALIDATED,INDEX_NAME from

USER_CONSTRAINTS where table_name='T_NOPARTITION';

OWNER CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED INDEX_NAME

------------ ------------------ ------------------------------

-------- ------------- ----------

xxxxxx ID_PK T_NOPARTITION ENABLED VALIDATED ID_PK

SQL> select status from user_indexes a where

a.index_name='ID_PK';

STATUS

--------

UNUSABLE

说明：direct方式可以导入违反约束的数据，只是导入后，索引的状态变为unusable，但主键状态依然是正常的。

oracle@linux-119:~> sqlldr

userid='xxxxxx/******' control=a.ctl direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Wed Aug 14

12:10:05 2013

Copyright (c) 1982, 2009, Oracle and/or its

affiliates. All rights reserved.

ORA-26026: unique index xxxxxx.ID_PK initially in unusable

state

SQL*Loader-2026: the load was aborted because SQL Loader cannot

continue.

Load completed - logical record count 3.

说明：此时再direct方式导入，报索引状态不对，不能正常导入。

说明：direct方式加载数据还是要检查索引状态的(好像之前的说法是direct方式先设置索引状态为unusable)，这个后续需要再验证一下。

SQL> insert into t_nopartition(id,created)

values(100,to_date('2013.03.03','YYYY.MM.DD'));

insert into t_nopartition(id,created)

values(100,to_date('2013.03.03','YYYY.MM.DD'))

ORA-01502: 索引 'xxxxxx.ID_PK' 或这类索引的分区处于不可用状态

说明：因为插入数据需要维护索引，目前索引状态不可用，因此正常插入数据也不可行。