 |
|
Oracle Tips by Burleson |
Easy Oracle Automation
Chapter 5 -
Automatic Storage Management
Automated Space Management in Oracle10g
When segments have unused space above and
below the high water mark (HWM), the database may suffer poor
performance during scans and DML operations. Since data is
spread across many data blocks, more I/Os are needed for data
retrieval. Row migration occurs and unused space becomes
unavailable to other database objects. In previous versions of Oracle database,
space allocated below HWM could be freed only by moving or
redefining the segment. In Oracle Database 10g,
the segments can be shrunk When a segment is shrunk, the
data is compacted and the HWM is pushed down to release unused
space. Sparsely populated segments get improved scan performance
and faster DML operations by shrinking.
A shrink operation
is an online operation that
does not take extra database space to
run. As a result of the shrink
operation, row migration may
or may not be
reduced
The segments must reside on Automatic
Segment Space Managed (ASSM) tablespaces for the shrink
operation to be successful. It will not work on segments managed
by free lists. Similarly, the following objects cannot be
shrunk:
-
Tables with on-commit or ROWID
based materialized views.
-
Tables in clusters or tables with
LONG columns.
-
LOB indexes and shared LOB
segments.
-
IOT mapping tables and IOT
overflow segments.
-
Temporary segments.
-
Undo segments.
The above text is
an excerpt from:
Easy Oracle Automation
Oracle10g Automatic
Storage, Memory and Diagnostic Features
ISBN 0-9745993-6-0
by Dr. Arun Kumar R.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
