shrink没有让rowid重新排序

xuanfeng0908

11.2.0.3的database，非集群，使用alter table shrink回缩表发现rowid没有发生移动，按帮助文档说法：
Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.
应该会让rowid重新排序才对。
操作过程如下：
SQL> create table t2 as select * from t1 where rownum<20;

Table created.

SQL> select rowid from t2;

ROWID
------------------
AAAuIHAAEAAAAErAAA
AAAuIHAAEAAAAErAAB
AAAuIHAAEAAAAErAAC
AAAuIHAAEAAAAErAAD
AAAuIHAAEAAAAErAAE
AAAuIHAAEAAAAErAAF
AAAuIHAAEAAAAErAAG
AAAuIHAAEAAAAErAAH
AAAuIHAAEAAAAErAAI
AAAuIHAAEAAAAErAAJ
AAAuIHAAEAAAAErAAK

ROWID
------------------
AAAuIHAAEAAAAErAAL
AAAuIHAAEAAAAErAAM
AAAuIHAAEAAAAErAAN
AAAuIHAAEAAAAErAAO
AAAuIHAAEAAAAErAAP
AAAuIHAAEAAAAErAAQ
AAAuIHAAEAAAAErAAR
AAAuIHAAEAAAAEsAAA

19 rows selected.

手动删除表t2中的几条不连续记录后继续查询

SQL>  select rowid from t2;

ROWID
------------------
AAAuIHAAEAAAAErAAA
AAAuIHAAEAAAAErAAE
AAAuIHAAEAAAAErAAH
AAAuIHAAEAAAAErAAK
AAAuIHAAEAAAAErAAN
AAAuIHAAEAAAAErAAP
AAAuIHAAEAAAAErAAR
AAAuIHAAEAAAAEsAAA

8 rows selected.

SQL> alter table t2 enable row movement;

Table altered.

SQL> alter table  t2  shrink SPACE;

Table altered.

SQL> select rowid from t2;

ROWID
------------------
AAAuIHAAEAAAAErAAA
AAAuIHAAEAAAAErAAB
AAAuIHAAEAAAAErAAE
AAAuIHAAEAAAAErAAH
AAAuIHAAEAAAAErAAK
AAAuIHAAEAAAAErAAN
AAAuIHAAEAAAAErAAP
AAAuIHAAEAAAAErAAR

8 rows selected.

Maclean Liu(刘相兵

你的表 只有一个小的extent  8k * 8 再shrink又如何能缩小呢？

Maclean Liu(刘相兵

见下面测试：




select banner from v$version where rownum=1;

SQL> create table maclean_shrink_test1 tablespace users as select rownum t1,'MACLEAN' t2 from dual connect by level<=900000;

表已创建。

SQL>
SQL> select max(t1) from maclean_shrink_test1;

   MAX(T1)
----------
    900000

SQL> select rowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from maclean_shrink_test1 where t1=900000;

ROWID              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
AAAcg9AAGAABpzpACy                               433385
                                   6

                                                                  
SQL> select count(*) from dba_extents where segment_name='MACLEAN_SHRINK_TEST1';

  COUNT(*)
----------
        33


SQL> alter table maclean_shrink_test1 enable row movement;

表已更改。


SQL> delete maclean_shrink_test1 where t1<900000;

已删除 899999 行。

SQL> commit;

提交完成。

SQL> select rowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from maclean_shrink_test1 where t1=900000;

ROWID              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
AAAcg9AAGAABpzpACy                               433385
                                   6
                                                                  
                                                                  
SQL> alter table maclean_shrink_test1 shrink space;

表已更改。

SQL> select rowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from maclean_shrink_test1 where t1=900000;

ROWID              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
AAAcg9AAGAABoxbAAA                               429147
                                   6
                                                                  
                                                                  

xuanfeng0908

ok，明白了，就是说如果只有一个extent的情况下shrink操作可以认为是无效的了，要使用shrink至少需要2个以上的extent了

xuanfeng0908

差点忘了，非常感谢