之前并不知道SKIP_UNUSABLE_INDEXES是一个参数，在imp/impdp中经常看到！通过以下试验发现即使SKIP_UNUSABLE_INDEXES=y，status＝UNUSABLE的index还是伴随着tt表的导出导入一起导出导入，只是SKIP_UNUSABLE_INDEXES=y时，当imp导入表时insert的数据不会同时再去update index key了，但是unique index例外 ，因为它扮演着constraint的作用！
SQL> DESC TT
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
SQL> SELECT * FROM TT;
ID
----------
1
2
SQL> CREATE INDEX IDX_TT ON TT(ID);
索引已创建。
SQL> alter table tt move tablespace dmt;
表已更改。
SQL> select index_name ,table_name , status from user_INDEXES where index_name='
IDX_TT';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
IDX_TT TT UNUSABLE
SQL> SHOW PARAMETER SKIP_UNUSABLE_INDEXES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> alter session set skip_unusable_indexes=false;
会话已更改。
SQL> select * from tt;
ID
----------
1
2
SQL> delete from tt;
delete from tt
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.IDX_TT' 或这类索引的分区处于不可用状态
SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.IDX_TT' 或这类索引的分区处于不可用状态
SQL> update tt set id=100;
update tt set id=100
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.IDX_TT' 或这类索引的分区处于不可用状态
SQL> select * from tt;
ID
----------
1
2
SQL> drop index idx_tt;
索引已删除。
SQL> create unique index idx_tt on tt(id);
索引已创建。
SQL> SHOW PARAMETER SKIP_UNUSABLE_INDEXES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean FALSE
SQL> alter table tt move tablespace dmt;
表已更改。
SQL> select index_name ,table_name , status from user_INDEXES where index_name='
IDX_TT';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
IDX_TT TT UNUSABLE
SQL> select * from tt;
ID
----------
1
2
SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.IDX_TT' 或这类索引的分区处于不可用状态
SQL> update tt set id=100;
update tt set id=100
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.IDX_TT' 或这类索引的分区处于不可用状态
SQL> delete from tt;
delete from tt
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.IDX_TT' 或这类索引的分区处于不可用状态
SQL> drop index idx_tt;
索引已删除。
SQL> alter table tt add constraint pk_tt primary key (id);
表已更改。
SQL> select index_name ,table_name , status from user_INDEXES where table_name='
TT';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_TT TT VALID
SQL> alter table tt move tablespace dmt;
表已更改。
SQL> select index_name ,table_name , status from user_INDEXES where table_name='
TT';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_TT TT UNUSABLE
--创建primary key时创建的index如果status＝UNUSABLE此时把select都报错！
SQL> select * from tt;
select * from tt
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.PK_TT' 或这类索引的分区处于不可用状态
SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.PK_TT' 或这类索引的分区处于不可用状态
SQL> update tt set id=100;
update tt set id=100
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.PK_TT' 或这类索引的分区处于不可用状态
SQL> delete from tt;
delete from tt
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.PK_TT' 或这类索引的分区处于不可用状态
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
PK_TT TT UNIQUE UNUSABLE
SQL> SHOW PARAMETER SKIP_UNUSABLE_INDEXES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean FALSE
我们清晰的看到下面skip_unusable_indexes＝true时对unique index不起作用，
因为unqiue index此时扮演着constraint的角色
SQL> alter session set SKIP_UNUSABLE_INDEXES=true;
会话已更改。
SQL> delete from tt;
delete from tt
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.PK_TT' 或这类索引的分区处于不可用状态
SQL> update tt set id=100;
update tt set id=100
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.PK_TT' 或这类索引的分区处于不可用状态
SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出现错误:
ORA-01502: 索引 'XYS.PK_TT' 或这类索引的分区处于不可用状态
SQL> select * from tt;
ID
----------
1
2
SQL> alter table tt drop constraint pk_tt;
表已更改。
SQL> create index idx_tt on tt(id);
索引已创建。
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
IDX_TT TT NONUNIQUE VALID
SQL> alter table tt move tablespace dmt;
表已更改。
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
IDX_TT TT NONUNIQUE UNUSABLE
SQL> show parameter SKIP_UNUSABLE_INDEX
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> select *from tt;
ID
----------
1
2
SQL> insert into tt values(3);
已创建 1 行。
SQL> commit;
提交完成。
SQL> update tt set id=100;
已更新3行。
SQL> rollback;
回退已完成。
SQL> delete from tt;
已删除3行。
SQL> rollback;
回退已完成。
SQL>
--=====================================================================================
SQL> host exp xys/manager@orcl file=e:expa.dmp tables=tt
Export: Release 10.2.0.1.0 - Production on 星期六 10月 13 21:43:57 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 TT导出了 3 行
成功终止导出, 没有出现警告。
SQL> host imp xys/manager@dup file=e:expa.dmp SKIP_UNUSABLE_INDEXES=y full=y
Import: Release 10.2.0.1.0 - Production on 星期六 10月 13 21:45:19 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 XYS 的对象导入到 XYS
. 正在将 XYS 的对象导入到 XYS
. . 正在导入表 "TT"导入了 3 行
成功终止导入, 没有出现警告。
SQL> connect xys/manager@dup
已连接。
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
IDX_TT TT NONUNIQUE UNUSABLE
--我们看到即使SKIP_UNUSABLE_INDEXES=y，status＝UNUSABLE的index还是伴随着tt表的导出导入
一起导出导入，只是
SKIP_UNUSABLE_INDEXES=y时，当imp导入表时insert的数据不会同时再去update index key了！
SQL> connect xys/manager@orcl
已连接。
SQL> drop index idx_tt;
索引已删除。
SQL> alter table tt add constraint pk_tt primary key (id);
表已更改。
SQL> alter table tt move tablespace dmt;
表已更改。
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
PK_TT TT UNIQUE UNUSABLE
SQL> host exp xys/manager@orcl file=e:expa.dmp tables=tt
Export: Release 10.2.0.1.0 - Production on 星期六 10月 13 22:27:09 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
. . 正在导出表 TT导出了 3 行
成功终止导出, 没有出现警告。
SQL> host imp xys/manager@dup file=e:expa.dmp SKIP_UNUSABLE_INDEXES=y full=y
Import: Release 10.2.0.1.0 - Production on 星期六 10月 13 22:27:37 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 XYS 的对象导入到 XYS
. 正在将 XYS 的对象导入到 XYS
. . 正在导入表 "TT"导入了 3 行
IMP-00017: 由于 ORACLE 错误 14063, 以下语句失败:
"ALTER TABLE "TT" ADD CONSTRAINT "PK_TT" PRIMARY KEY ("ID") USING INDEX PCT"
"FREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS"
" 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
"DEFAULT) TABLESPACE "DMT" LOGGING"
IMP-00003: 遇到 ORACLE 错误 14063
ORA-14063: 唯一/主约束条件关键字中存在无用索引
IMP-00017: 由于 ORACLE 错误 14048, 以下语句失败:
"ALTER INDEX "PK_TT" UNUSABLE ENABLE "
IMP-00003: 遇到 ORACLE 错误 14048
ORA-14048: 分区维护操作不能与其它操作组合
成功终止导入, 但出现警告。
SQL> connect xys/manager@dup
已连接。
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';
INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
PK_TT TT UNIQUE UNUSABLE
SQL>
通过以上操作我们看到SKIP_UNUSABLE_INDEXES=y对unique index不起作用，因为此时的unique index扮演者
constraint的作用所以在insert数据时index必须被更新
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-61743/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-61743/
330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
