北极熊和企鹅 发表于 2011-12-23 01:07

oracle 10G 表空间迁移 索引需要重建

<DIV>
<P>昨天进行了表空间迁移的维护，维护后出现一个问题：MYTEST用户下的表表空间迁移后索引存在失效。<BR>针对该问题和其他DBA进行了一些沟通，大家理解并不统一。后通过实际测试发现，表空间迁移后没有数据的表索引正常，但有数据的表的相关索引确实会失效，<BR>解决的方法是REBUILD索引（今早rubuild MYTEST下的所有索引，通过select INDEX_NAME,status from user_indexes确认status均为VALID的正常状态）。</P>
<P>进一步查找资料发现：<BR>Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, <BR>and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. <BR>Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.<BR>移动表会导致行的rowid变化，导致该表上面的index不可用，即标记为UNUSABLE，当用DML来操作该表时用到该索引，会引发ORA-01502 error，因此索引必须drop或者rebuild。<BR>该表的统计信息也会失效，所以需要重新对该表进行统计分析，即analyze table *** compute statistics。）</P>
<P>但是针对ORA-01502，实际测试时并不存在<BR>测试步骤：<BR>SQL&gt; create table test_altertablespace (col1 number) tablespace USERS;------------------------------------------------------创建测试表，表空间为USERS<BR>Table created</P>
<P>SQL&gt; create index idx_test_altertablespace on test_altertablespace(col1);----------------------------------------------------创建索引<BR>Index created</P>
<P>SQL&gt; select tablespace_name,status from user_indexes where index_name = 'IDX_TEST_ALTERTABLESPACE';--确认索引的状态为VALID<BR>TABLESPACE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STATUS<BR>------------------------------ --------<BR>IN_MYTEST_DATA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;VALID</P>
<P>SQL&gt; alter table test_altertablespace move tablespace IN_MYTEST_DATA;--------------------------------------------------迁移表空间到IN_MYTEST_DATA<BR>Table altered</P>
<P>SQL&gt; select tablespace_name,status from user_indexes where index_name = 'IDX_TEST_ALTERTABLESPACE';--确认索引的状态为VALID<BR>TABLESPACE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STATUS<BR>------------------------------ --------<BR>IN_MYTEST_DATA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;VALID</P>
<P>SQL&gt; insert into test_altertablespace values(1);------------------------------------------------------------------------------------------插入数据<BR>1 row inserted<BR>SQL&gt; commit;<BR>Commit complete</P>
<P>SQL&gt; select tablespace_name,status from user_indexes where index_name = 'IDX_TEST_ALTERTABLESPACE';--确认索引的状态为VALID<BR>TABLESPACE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STATUS<BR>------------------------------ --------<BR>IN_MYTEST_DATA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALID</P>
<P>SQL&gt; alter table test_altertablespace move tablespace users;----------------------------------------------------------------------移动表空间到USERS<BR>Table altered</P>
<P>SQL&gt; select tablespace_name,status from user_indexes where index_name = 'IDX_TEST_ALTERTABLESPACE';--确认索引的状态为UNUSABLE<BR>TABLESPACE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; STATUS<BR>------------------------------ --------<BR>IN_MYTEST_DATA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNUSABLE<BR>&nbsp;</P>
<P>本文出自 “<a href="http://yunlongzheng.blog.51cto.com/" target="_blank">麦地坞</A>” 博客，请务必保留此出处<a href="http://yunlongzheng.blog.51cto.com/788996/397758" target="_blank">http://yunlongzheng.blog.51cto.com/788996/397758</A></P></DIV>
页: [1]
查看完整版本: oracle 10G 表空间迁移 索引需要重建