Oracle, Performance

Reclaim Unused Space

by jb

This post describes the steps that can be used to free unused space in database objects. Sometimes this is also called defragmentation.

We will use the Oracle Segment Advisor to get recommendations about the database objects that contain fragmented space.

Running the Segment Advisor manually

The Segment Advisor also runs during the Maintenance Window, but does not analyze every object. Therefor we run the Segment Advisor by hand as follows:

Create the Segment Advisor Task

declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  id number;
  begin
  name:='Segment Advisor Analysis';
  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor', 
    task_id          => id,
    task_name        => name,
    task_desc        => 'Segment Advisor Task');
  dbms_advisor.create_object (
    task_name        => name,
    object_type      => 'TABLESPACE', -- change if needed (TABLE, INDEX, MVIEW, MVIEW_LOG, TABLESPACE, ...)
    attr1            => 'USERS',      -- change if needed
    attr2            => NULL,
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);
  dbms_advisor.set_task_parameter(
    task_name        => name,
    parameter        => 'RECOMMEND_ALL',
    value            => 'TRUE');
  end;
/

Run (or re-run) the Segment Advisor Task

set timing on
-- the next command resets a previous run
exec dbms_advisor.reset_task('Segment Advisor Analysis')
exec dbms_advisor.execute_task('Segment Advisor Analysis')

Display the Segment Advisor Recommendations

select tablespace_name, segment_owner, segment_name, recommendations, C1 command 
 from table (dbms_space.asa_recommendations(all_runs=>'TRUE', show_manual=>'TRUE', show_findings=>'FALSE'));
Sample Output: 
TABLESPACE_NAME                SEGMENT_OWNER                  SEGMENT_NAME                   RECOMMENDATIONS                                                                                      COMMAND
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------- ----------------------------------------
USERS                          SYS                            T                              Enable row movement of the table SYS.T and perform shrink, estimated savings is 34050706 bytes.
alter table "SYS"."T" shrink space

If you want to see all findings you can run:

select tablespace_name, segment_owner, segment_name, recommendations, C1 command 
 from table (dbms_space.asa_recommendations(all_runs=>'TRUE', show_manual=>'TRUE', show_findings=>'TRUE'));
Sample Output: 
USERS	SYS	T	The free space in the object is less than 10MB.	
USERS	SYS	T	The free space in the object is less than 10MB.	
USERS	SYS	T	The free space in the object is less than 10MB.	
USERS	SYS	T	The free space in the object is less than 10MB.

Removing the Segment Advisor Task

-- Remove the Segment Advisor Task and associated recommendations
exec dbms_advisor.delete_task('Segment Advisor Analysis');

alter table xxx shrink space (compact);

To reclaim space on tables the shrink operation should (mainly on heavy used tables) be split into two steps:

-- DML can continue to run during that phase
alter table t shrink space compact;
-- DML cannot take place during a part of that phase
alter table t shrink space;

Monitoring the process of the shrink command

This can be used to verify if the segment has free space and how far the shrink command has proceeded. At the end of the shrink command all blocks should be “Full Blocks”:

set serverout on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('SYSTEM', 'T', 'TABLE', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks (Number of blocks having at least  0 to  25% free space) = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks (Number of blocks having at least 25 to  50% free space) = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks (Number of blocks having at least 50 to  75% free space) = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks (Number of blocks having at least 75 to 100% free space) = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
Sample Output: 

Unformatted Blocks = 0
FS1 Blocks (Number of blocks having at least  0 to  25% free space) = 0
FS2 Blocks (Number of blocks having at least 25 to  50% free space) = 726
FS3 Blocks (Number of blocks having at least 50 to  75% free space) = 150548
FS4 Blocks (Number of blocks having at least 75 to 100% free space) = 0
Full Blocks = 0

Restrictions of the shrink space command

For some types of tables the shrink command cannot be used. The findings of the Segment Advisor will let you know:

# for tables with long column:
The object has some free space but cannot be shrunk because ORA-10662: Segment has long columns.
# for clustered tables:
The object has some free space but cannot be shrunk because ORA-10653: Table is in a cluster.

For some types of tables the following errors are shown when trying to run the shrink space (compact) command:

# for tables with Function Based Indexes or Domain Indexes:
ORA-10631: SHRINK clause should not be specified for this object
# for tables with Bitmap Join Indexes:
ORA-10664: Table has bitmap join indexes
# for tables with On Commit Materialized Views
ORA-10652: Object has on-commit materialized views

We can also check a certain segment if it is possible to shrink:

alter table t2 shrink space check;
alter index i1 shrink space check;
Sample Output 
SQL> alter table t2 shrink space check;

Error starting at line : 1 in command -
alter table t2 shrink space check
Error report -
ORA-10662: Segment has long columns

https://docs.oracle.com/error-help/db/ora-10662/10662. 00000 -  "Segment has long columns"
*Document: NO
*Cause:    Shrink was issued on a segment with long columns. This is not
           supported.
*Action:
SQL>
SQL> alter index i1 shrink space check;

Error starting at line : 1 in command -
alter index i1 shrink space check
Error report -
ORA-10655: Segment can be shrunk

https://docs.oracle.com/error-help/db/ora-10655/10655. 00000 -  "Segment can be shrunk"
*Document: NO
*Cause:    Error message returned when called in probe mode by OEM
*Action:
SQL>

More information