Automatic Segment Space Management (ASSM)

Automatic Segment Space anagement (ASSM) is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the pctused, freelists, and freelist groups storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.

If you have space issues on a tablespace you can shrink tables and move indexes while online. MMON will send the alert for any tablespace if thresholds have been exceeded. To shrink table segments you can use the below commands, cascade will shrink both table and indexes(only these objects are affected), compact will stop before moving the High Water Mark (HWM) thus not reclaiming space back. , this may be useful as moving the HWM locks the table thus this may impact users.

The benefits of a shrink operation are:

Note: it is not guaranteed that all chained rows will be fixed because not all blocks may be read in a segment shrink operation.

Determine the HWM

select blocks from dba_segments where owner = 'TEST01' and segment_name = 'EMPLOYEE';
analyze table test01.employee estimate statistics;
select empty_blocks from dba_tables where owner = 'TEST01' and table_name = 'EMPLOYEE';

-- HWM = (query1 - query 2) - 1

NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.

True statements of a shrink operation:

There are two phases in a segment shrink operation

Compaction phase During compaction the rows are compacted and moved towards the left side of the segment, the HWM remains the same so the free space is still not available. DML is still available while the object is being compacted
Adjustment of HWM
(release free space)
This adjustment is very short, oracle lowers the HWM and releases the free space. Oracle locks the object in an exclusive mode while the HMW is being lowered, meaning that no DML operations can take place.

A shrink could fail because the tablespace is not locally managed or do not have ASSM (automatic segment managed) enabled, the table has a column of long, row movement has not been enabled or it is a clustered table. There is no tool to display how often a segment is used but using the segment advisor in the OEM you can perform any of the below commands

Determine amount of free space declare
  l_fs1_bytes number;
  l_fs2_bytes number;
  l_fs3_bytes number;
  l_fs4_bytes number;
  l_fs1_blocks number;
  l_fs2_blocks number;
  l_fs3_blocks number;
  l_fs4_blocks number;
  l_full_bytes number;
  l_full_blocks number;
  l_unformatted_bytes number;
  l_unformatted_blocks number;
  segment_owner => user,
  segment_name => 'BOOKINGS',
  segment_type => 'TABLE',
  fs1_bytes => l_fs1_bytes,
  fs1_blocks => l_fs1_blocks,
  fs2_bytes => l_fs2_bytes,
  fs2_blocks => l_fs2_blocks,
  fs3_bytes => l_fs3_bytes,
  fs3_blocks => l_fs3_blocks,
  fs4_bytes => l_fs4_bytes,
  fs4_blocks => l_fs4_blocks,
  full_bytes => l_full_bytes,
  full_blocks => l_full_blocks,
  unformatted_blocks => l_unformatted_blocks,
  unformatted_bytes => l_unformatted_bytes
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);
Enable row movement

alter table <table> enable row movement;

Note: required to perform a shrink operation

recover space from table and indexes and amend the HWM

alter table <table> shrink space cascade;

Note: this will lock the table

recover space and don’t amend the HWM alter table <table> shrink space compact;
recover space table and indexes and don’t amend the HWM alter table <table> shrink space compact cascade;
deallocate unused extents from table alter table test03 deallocate unused;
alter table test03 deallocate unsed keep 10M;

Shrinking index segments is required as when an index entry is deleted the space remains assigned. First you need to analyze the index and then query the index_stats view, remember the space is not removed but is available for reuse, to get the space back you must rebuild the index.

Validate index analyze index test_indx validate structure;
  select lf_rows_len, del_lf_rows from index_stats where name =’IND1’;
  select pct_used from index_stats where name = ‘EMP_INDX’;
Shrink index alter index test_indx shrink space;
Coalesce space

alter index emp_indx coalesce;

Note: cannot move to another tablespace, does not require additional disk space, coalesces index leaf blocks with each branch

Rebuild index (online)

alter index test_indx rebuild online;

Note: uses journal table to store any new rows then applies to new index, can move index to another tablespace, requires double space to rebuild online, creates new tree and adjust tree height

Monitor index usage

alter index test_indx monitoring usage; (turn on monitoring)
alter index test_indx nomonitoring usage; (turn off monitoring)

select index_name, table_name, monitoring, used, start_monitoring from v$object_usage where index_name = ‘TEST_IDX’;


You can setup your own PL/SQL program to advise on tables, etc and to implement the recommendations. This can also be done under the EM console.

create task dbms_advisor.create_task(‘Segment Advisor’, :task_id, task_name, ‘Free space in emp’, null);
create object dbms_advisor_create_object( task_name, ‘TABLE’, ‘TEST01’, ‘EMP’, null, null, object_id);
set task parameter dbms_advisor.set_task_parameter( task_name, ‘recommend_all’, ‘true’);
execute task dbms_advisor.execute_task(task_name);
delete task dbms_advisor.delete_task(task_name);
cancel task dbms_advisor.cancel_task(task_name);
display task print task_id
display recommendations

select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id = ??;

Display actions select task_id, task_name, command, attrl from dba_advisor_actions where task_id = ???;

Alternative table storage structures

 The standard table type is a heap table, a heap consists of variable-length rows in random order, there is no structure to the physical storage. There are more advanced tables which can offer many benefits over the standard heap table.

(index organized table)

Index organized table (IOT) is a B-Tree index but the leaf blocks contain key values followed by the rest of the row, the saving is that only one segment is needed not two like a heap table and a index.

For further details on creating, removing, etc see tables

Index clusters

An index cluster is a group of tables stored in one physical segment. All tables must be linked via a common key, it can be used to de-normalise tables in a foreign key relationship.

For further details on creating, removing, etc see tables

Hash clusters

A hash cluster is created with a cluster key, as is a index cluster but rather than creating a index on this key oracle will use it to construct a hashing algorithm, oracle can calculate a rows location via this key. It is only useful if you search using the equality predicate on the key. Range search or get-next-record operations will almost certainly result in a full cluster scan. Don’t use hash clusters if you do not know how many rows will be returned. The new access path in a sorted hash cluster is used only if an equality predicate is used.

For further details on creating, removing, etc see tables

Sorted hash clusters

Sorted hash clusters is the same as a hash cluster but you specify one or more columns to be used for ordering rows with the same cluster key. This makes sure that the rows are returned in the correct order.

For further details on creating, removing, etc see tables

Temporary storage

Temporary tablespaces are used for order by, group by and create index. It is required when the system tablespace is locally managed. In oracle 10g you can now create temporary tablespace groups which means you can use multiple temporary tablespaces simultaneously.

For further details on creating, removing, etc see tablespaces