SHRINK SPACE CHECK

利用SHRINK SPACE可以更好的对segment进行管理。但SHRINK SPACE功能在oracle的不同版本中也略有差异。

其实我们可以不用记住不同版本中SHRINK SPACE的限制条件。

因为oracle提供了‘ALTER TABLE ... SHRINK SPACE CHECK’的功能。

参考：What is the Meaning of SHRINK SPACE CHECK? [ID 1132163.1]

The "ALTER TABLE ... SHRINK SPACE CHECK" statement is used to to properly check for proper
segment type and segment attributes (e.g. row movement enabled) to allow shrink.
The statement performs the exact same verifications as the "ALTER TABLE ... SHRINK SPACE",
but it does not perform any actual shrinking on the segment.

用法：
SQL> alter table dept shrink space check;
alter table dept shrink space check
*
第 1 行出现错误:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table dept enable row movement;

表已更改。

SQL> alter table dept shrink space check;
alter table dept shrink space check
*
第 1 行出现错误:
ORA-10655: Segment can be shrunk

这其实不是个错误，而是告诉我们这个段可以进行shrink操作。

This is not an error message, but rather a confirmation message about the possibility of shrinking the segment

 

 

关于SEGMENT SHRINK在ORACLE不同版本中的限制条件:
参考:SEGMENT SHRINK and details. [ID 242090.1]
Restrictions on the shrink_clause, 10gR1
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any
   object with a LONG column.
2. Segment shrink is not supported for LOB segments even if CASCADE is
   specified.
3. Segment shrink is not supported for tables with function-based indexes.
4. This clause does not shrink mapping tables or overflow segments of
   index-organized tables, even if you specify CASCADE.
5. You cannot shrink a table that is the master table of an ON COMMIT
   materialized view. Rowid materialized views must be rebuilt after the
   shrink operation.
6. Table with a domain index is not supported.

Restrictions on the shrink_clause, 10gR2
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any
   object with a LONG column.
2. Segment shrink is not supported for tables with function-based indexes or
   bitmap join indexes.
3. This clause does not shrink mapping tables of index-organized tables,
   even if you specify CASCADE.
4. You cannot specify this clause for a compressed table.
5. You cannot shrink a table that is the master table of an ON COMMIT
   materialized view. Rowid materialized views must be rebuilt after the
   shrink operation.
6. Table with a domain index is not supported.

Restrictions on the shrink_clause, 11gR1
========================================
1. You cannot combine this clause with any other clauses in the same ALTER TABLE
   statement.
2. You cannot specify this clause for a cluster, a clustered table, or any
   object with a LONG column.
3. Segment shrink is not supported for tables with function-based indexes or
   bitmap join indexes.
4. This clause does not shrink mapping tables of index-organized tables, even if
   you specify CASCADE.
5. You cannot specify this clause for a compressed table.
6. You cannot shrink a table that is the master table of an ON COMMIT
   materialized view. Rowid materialized views must be rebuilt after the shrink
   operation.