表移表空间,索引居然不失效,晕倒了

显示全部楼层 · 2007-5-10 01:02:02

我晕,今天我只是做个实验，看看失效的索引所在的表会如何处理数据，没想到结果表MOVE到自己的表空间，系统表空间和其他用户表空间三种情况都不能导致原来的索引失效,晕倒,怎么回事啊?
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table aaa
2(id int, a1 varchar2(10));
表已创建。

SQL> create unique index un_idx on aaa(id);
索引已创建。
SQL> select index_name,table_owner,table_name,status from user_indexes ;
INDEX_NAME
TABLE_OWNER
TABLE_NAME

STATUS
------------------------------ ------------------------------ ------------------
------------ --------
UN_IDX
ABC
AAA

VALID
SQL> alter table aaa move
2;
表已更改。
SQL> select index_name,table_owner,table_name,status from user_indexes ;
INDEX_NAME
TABLE_OWNER
TABLE_NAME

STATUS
------------------------------ ------------------------------ ------------------
------------ --------
UN_IDX
ABC
AAA

VALID
SQL> alter table aaa move TABLESPACE SYSTEM
2;
表已更改。
SQL> select index_name,table_owner,table_name,status from user_indexes ;
INDEX_NAME
TABLE_OWNER
TABLE_NAME

STATUS
------------------------------ ------------------------------ ------------------
------------ --------
UN_IDX
ABC
AAA

VALID

SQL> alter table aaa move tablespace USERS;
表已更改。
SQL> select index_name,table_owner,table_name,status from user_indexes ;
INDEX_NAME
TABLE_OWNER
TABLE_NAME

STATUS
------------------------------ ------------------------------ ------------------
------------ --------
UN_IDX
ABC
AAA

VALID

SQL> create tablespace Adatafile 'C:\oracle\product\10.2.0\oradata\xuelian\a.d
bf' size 10M autoextend on next 10M MAXSIZE 100M;
表空间已创建。
SQL> alter table aaa move tablespace A;
表已更改。
SQL> select index_name,table_owner,table_name,status from user_indexes ;
INDEX_NAME
TABLE_OWNER
TABLE_NAME

STATUS
------------------------------ ------------------------------ ------------------
------------ --------
UN_IDX
ABC
AAA

VALID
SQL>

千问 · 2007-5-10 01:02:02

往表里插入1条数据

千问 · 2007-5-10 01:02:02

最初由 yangtingkun 发布
[B]往表里插入1条数据 [/B]
谢谢大师指点,一定要有数据在里面索引才会失效啊
SQL> insert into aaa values (1,'a');
已创建 1 行。
SQL> commit
2;
提交完成。
SQL> select index_name,table_name,status from user_indexes;
INDEX_NAME
TABLE_NAME
STATUS
------------------------------ ------------------------------ --------
UN_IDX
AAA
VALID

SQL> alter table aaa move;
表已更改。
SQL> select index_name,table_name,status from user_indexes;
INDEX_NAME
TABLE_NAME
STATUS
------------------------------ ------------------------------ --------
UN_IDX
AAA
UNUSABLE
SQL> insert into aaa values (1,'a');
insert into aaa values (1,'a')
*
第 1 行出现错误:
ORA-01502: 索引 'ABC.UN_IDX' 或这类索引的分区处于不可用状态

千问 · 2007-5-10 01:02:02

造成索引失效的原因是由于table被move后，表中的数据存储位置发生了变化，也就是rowid发生了变化，索引中存储的rowid就不再是正确的了。
如果根本没有数据，那么索引有什么可失效的？

千问 · 2007-5-10 01:02:02

最初由 yangtingkun 发布
[B]造成索引失效的原因是由于table被move后，表中的数据存储位置发生了变化，也就是rowid发生了变化，索引中存储的rowid就不再是正确的了。
如果根本没有数据，那么索引有什么可失效的？ [/B]
谢谢,那失效后为什么对表的删除和插入,修改等操作都会不成功呢?
我老是想索引失效了就相当于没索引了，没索引的数据库怎么不让动呢?
但是对表的列添加删除等却又可以,奇怪啊，为什么呢???
SQL> insert into aaa values (1,'a');
insert into aaa values (1,'a')
*
第 1 行出现错误:
ORA-01502: 索引 'ABC.UN_IDX' 或这类索引的分区处于不可用状态

SQL> delete from aaa;
delete from aaa
*
第 1 行出现错误:
ORA-01502: 索引 'ABC.UN_IDX' 或这类索引的分区处于不可用状态

SQL> update aaa set id=2 where id=1;
update aaa set id=2 where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'ABC.UN_IDX' 或这类索引的分区处于不可用状态

SQL> alter table aaa adda2 varchar2(10);
表已更改。
SQL> alter table aaa drop columna2 ;
表已更改。
SQL> alter table aaa drop column a1;
表已更改。
SQL> alter table aaa drop column id;
alter table aaa drop column id
*
第 1 行出现错误:
ORA-12983: 无法删除表的全部列

SQL> alter table aaa adda1 varchar2(10);
表已更改。
SQL> select * from aaa;
ID A1
---------- ----------
1
SQL> alter table aaa drop column id;
表已更改。

千问 · 2007-5-10 01:02:02

索引失效和没有索引是完全不同的两个概念。
DML报错是因为在DML的时候，Oracle需要同步更新索引，而目前索引不可用，当然报错了。
你执行的DDL操作和索引的维护有什么关系？为什么要报错？

千问 · 2007-5-10 01:02:02

最初由 Kamus 发布
[B]正好，我这里也有个问题
一个分区表中的local索引，在没有进行分区move的操作情况下，还有什么别的原因会造成local索引成为unusable状态？ [/B]

splid分区，add分区（hash分区），merge分区，coalesce分区以及exchange分区都可能造成索引状态改变

千问 · 2007-5-10 01:02:02

天啊，晚上12点半还在讨论技术问题！！！
实在是佩服！

千问 · 2007-5-10 01:02:02

大师们无论技术水平还是进取心都是让我景仰的,佩服!

千问 · 2007-5-10 01:02:02

学习了！