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 user_name.table_name ENABLE ROW MOVEMENT;
2. Shrink table but don't want to shrink HWM (High Water Mark).
SQL> ALTER TABLE user_name.table_name SHRINK SPACE COMPACT;
3. Shrink table and HWM too.
SQL> ALTER TABLE user_name.table_name SHRINK SPACE;
4. Shrink table and all dependent index too.
SQL> ALTER TABLE user_name.table_name 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 primary 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 primary 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 primary table of an ON COMMIT
materialized view. Rowid materialized views must be rebuilt after the shrink
operation.
Restrictions on the shrink_clause, 11gR2
========================================
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, domain 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 primary table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.
Restrictions on the shrink_clause, 12cR1
========================================
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, domain indexes, or bitmap join indexes.
4. This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
5. You can specify this clause for a table with Advanced Row Compression enabled (ROW STORE COMPRESS ADVANCED). You cannot specify this clause for a table with any other type of table compression enabled.
6. You cannot shrink a table that is the primary table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.
Restrictions on the shrink_clause, 12cR2
========================================
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, domain indexes, or bitmap join indexes.
4. This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
5. You can specify this clause for a table with Advanced Row Compression enabled (ROW STORE COMPRESS ADVANCED). You cannot specify this clause for a table with any other type of table compression enabled.
6. You cannot shrink a table that is the primary 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.
During segment shrink, the segmentâs incarnation number is changed in the bitmap
blocks and segment header when the segment HWM is adjusted. Subsequent data block
changes happen at this newer incarnation number.
Queries that span this phase can die with an external error "10632 - invalid rowid"
if
1) They read the updated bitmap blocks (that have new inc#). Note that this failure
happens if the space is not reused
2) The space got reused for some other object or the same object.
Limitations on Online Segment Shrink, 10gR2:
============================================
Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
- IOT mapping tables
- Tables with rowid based materialized views
- Tables with function-based indexes
Dependency Maintenance and Restrictions on Shrink
=================================================
The only dependency that will be taken care of during segment shrink is the index.
The indexes will not be in an unusable state after shrink.
The compaction phase of segment shrink will be done as insert/delete pairs. The
DML triggers will not be fired during data movement phase. Since the data does
not change, it is not required to fire the triggers.
ROWID based triggers should be disabled before issuing a shrink since it will not
fire during shrink.
Segment shrink cannot be done on objects with on-commit materialized views.
Materialized views that are based on primary key need not be refreshed or rebuilt
after shrink.
However, it is the DBAâs responsibility to refresh/rebuild the materialized views
that are rowid based.
Availability
============
Segment shrink is done online, thereby it increases the availability of the object.
While conventional DML operations can coexist with segment shrink, parallel DMLs
cannot.
During segment shrink, data will be moved as part of the compaction phase. During
compaction locks will be held on individual rows and/or blocks containing the data.
This will cause the concurrent DMLs like updates and deletes to serialize on the
locks. The compaction will be done in units of smaller transactions, so the availability
of the object will not be impacted significantly.
However during certain phases of segment shrink (when the HWM is adjusted), the segment
will have to be locked in exclusive mode.
This phase is for a very short duration and should impact the availability of the
object less significantly.
Security
========
The privileges required to execute segment shrink on an object will be the same
as that for ALTER object.
Detail Example
===============
SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 1
FS3 Blocks = 1
FS4 Blocks = 3
Full Blocks = 0
PL/SQL procedure successfully completed.
SQL> alter table t_shrink shrink space compact;
alter table t_shrink shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> ALTER TABLE t_shrink ENABLE ROW MOVEMENT;
Table altered.
SQL> ALTER TABLE t_shrink SHRINK SPACE COMPACT;
Table altered.
SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 2
Full Blocks = 1
PL/SQL procedure successfully completed.
SQL> ALTER TABLE t_shrink SHRINK SPACE;
Table altered.
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 1
PL/SQL procedure successfully completed.
SQL>
========
To monitor the progress of a long running SHRINK operation you may execute the pl/sql block in this note. You should see a change in the number of blocks. And it is not possible to verify how much time it still need to take for now.
For specific details on the output of the pl/sql blocks in this Note please reference the following documentation:
For 9i:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_space2.htm#1002701
For 10gR2:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#sthref6026
For 11gR1:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_space.htm#sthref7301
For 11gR2:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#ARPLS68113
For 12cR1:
http://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68113
For 12cR2:
http://docs.oracle.com/database/122/ARPLS/DBMS_SPACE.htm#ARPLS68113
Can I cancel shrink operation? :
====================
Shrinking space of a large segment can take a lot of time, e.g. tens of hours, and can generate lots of redo.
Therefore, it is not advised to interrupt a shrink statement.
Best approach would be:
1. Initially, only use:
SQL> alter table shrink space compact;
This also takes long but should have little impact because of only short dml locks
2. Only when convenient, issue
SQL> alter table shrink space;
Before running in production, it is advised to test for possible impact in your test environment.
RELATED DOCUMENTS
-----------------
SQL Reference Manual, 10.1 and 10.2
Oracle® Database Administrator's Guide10g Release 1 and 2
Note:149516.1 BMB versus Freelist Segment: DBMS_SPACE.UNUSED_SPACE and DBA_TABLES.EMPTY_BLOCKS
