OS:Red Hat Enterprise Linux Server release 5.5 _64 虚拟机
数据库：oracle10.2.0.4.0 - 64bit

之前创建了大表target_test
SQL> select count(*) from target_test order by owner;
COUNT(*)
----------
92037120
后来删除了部分数据
SQL> select count(*) from target_test;
COUNT(*)
----------
46276608

SQL> alter table target_test enable row movement;
SQL> alter table target_test shrink space;
报错：ORA-10631: SHRINK clause should not be specified for this object
一查是因为该表上创建了函数索引，
SQL> SELECT table_name,index_name,index_type FROM User_Indexes WHERE index_type LIKE 'FUNCTION-BASED%' and table_name='TARGET_TEST';

TABLE_NAME INDEX_NAME    INDEX_TYPE
------------------------------ ------------------------------     ---------------------------
TARGET_TEST V_TARGET_TEST_2   FUNCTION-BASED NORMAL

SQL> select index_name,table_name,column_name from user_ind_columns;

INDEX_NAME TABLE_NAME   COLUMN_NAME
------------------------------ ------------------------------   --------------------------------------------------------------------------------
PK_DEPT DEPT DEPTNO

PK_EMP EMP   EMPNO

V_TARGET_TEST TARGET_TEST OBJECT_NAME

V_TARGET_TEST TARGET_TEST OWNER

V_TARGET_TEST_2 TARGET_TEST SYS_NC00014$

删除该索引
SQL> drop index v_target_test_2;
Index dropped.

SQL> alter table target_test shrink space;
执行成功。
可以通过下面语句进行对比
select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TARGET_TEST';
select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TARGET_TEST';

下面是网上找到的一篇讲alter table move 与shrink space的区别
http://blog.csdn.net/zhjxixi/article/details/7661378

ITPub博客

ORA-10631: SHRINK clause should not be specified for this object

最新文章