SEGMENT SHRINK and Details. (Doc ID 242090.1)

PURPOSE
========
In Oracle10g and onwards, we have the option to shrink a segment, which will help DBAs to manage the space in better way.
This feature also help for better performance for query.

SCOPE & APPLICATION
===================
Information on new Oracle10g feature.

Oracle 10g Segment shrink
=========================
Mandatory
=========
Init.ora parameter 'Compatible' must be >=10.0
Shrink operations can be performed only on segments in locally managed
tablespaces with automatic segment space management (ASSM).

How it works
============

1. Enable row movement for the table.
SQL>  ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

2. Shrink table but don't want to shrink HWM (High Water Mark).
SQL>  ALTER TABLE scott.emp SHRINK SPACE COMPACT;

3. Shrink table and HWM too.
SQL>  ALTER TABLE scott.emp SHRINK SPACE;

4. Shrink table and all dependent index too.
SQL>  ALTER TABLE scott.emp SHRINK SPACE CASCADE;

5. Shrink table under MView.
SQL>  ALTER TABLE <table name> SHRINK SPACE;

6. Shrink Index only.
SQL>  ALTER INDEX <index nam> SHRINK SPACE;

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.

Query/DML Concurrency
=======================
The online phase of segment shrink is done with DML-compatible locks. Hence DMLs
can coexist during this phase. During the space-release/HWM adjustment phase,
incompatible locks will be acquired on the table, hence, DMLs will block on
shrink.

There are no user visible errors that shrink will cause on DMLs.
Queries cache the segment HWM. Oracle guarantees that the HWM always moves forward,
hence CR (consistent read) is not required on segment header and extent map blocks. The only operations
that cause the segment HWM to move backward are drop and truncate.

We allow queries to coexist with drop/truncate DDLs since queries do not acquire locks.
If after the drop/truncate, the space gets reused in some other segment, then the
queries get "8103 - object does not exist" external error message.