在环境上把一个INDEX 处于unusable状态:
这是一个9IR2的环境
SQL> create table test as select * from dba_objects;
Table created.
SQL> insert into test select * from dba_objects;
34084 rows created.
SQL> create index i_test_1 on test(object_id);
Index created.
SQL> alter index i_test_1 unusable;
Index altered.
这时对TABLE 进行 dml操作时,如果dml语句没有对test.object_id 自动进行操作时,语句可以正常操作。
但如果包括了test.object_id 字段,sql会报错:
SQL> update test set OBJECT_ID = 2000;
update test set OBJECT_ID = 2000
*
ERROR at line 1:
ORA-01502: index 'SYS.I_TEST_1' or partition of such index is in unusable state
SQL> insert into test select * from dba_objects;
insert into test select * from dba_objects
*
ERROR at line 1:
ORA-01502: index 'SYS.I_TEST_1' or partition of such index is in unusable state
SQL> insert /*+ append */ into test select * from dba_objects;
2
SQL> insert /*+ append */ into test select * from dba_objects;
insert /*+ append */ into test select * from dba_objects
*
ERROR at line 1:
ORA-26028: index SYS.I_TEST_1 initially in unusable state
可以看到在9I下,如果对含有UNUSABLES INDEX 字段的table进行dml操作,ORACLE是不允许的。
要解决这个问题,那需要在当前session中更改SKIP_UNUSABLE_INDEXES 为true。
执行的脚本:
SQL> alter session set SKIP_UNUSABLE_INDEXES =true;
Session altered.
SQL> insert /*+ append */ into test select * from dba_objects;
34085 rows created.
SQL> select status from user_indexes where index_name='I_TEST_1';
STATUS
------------------------
UNUSABLE
SQL> alter index I_TEST_1 rebuild;
Index altered.
SQL> select status from user_indexes where index_name='I_TEST_1';
STATUS
------------------------
VALID
在10g下, oracle可以忽略DML 语句中含有unusable index的字段。原因是skip_unusable_indexes 参数从9I的会话级别的
上升到INSTANCE级别的。
而且默认值为TRUE.
SQL> select * from v$version where rownum < 2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
SQL> show parameter skip
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> create table test as select * from dba_objects;
Table created.
SQL> insert into test select * from dba_objects;
50008 rows created.
SQL> create index i_test_1 on test(object_id);
Index created.
SQL> alter index i_test_1 unusable;
Index altered.
SQL> insert /*+ append */ into test select * from dba_objects;
50009 rows created.
SQL> select status from user_indexes where index_name='I_TEST_1';
STATUS
--------
UNUSABLE
SQL> alter index I_TEST_1 rebuild;
Index altered.
SQL> select status from user_indexes where index_name='I_TEST_1';
STATUS
--------
VALID
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/40239/viewspace-573313/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/40239/viewspace-573313/