Reclaim Unused Space
This post describes the steps that can be used to free unused space in database objects. Sometimes this is also called defragmentation.
Table of Contents
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
This command shows the results of the latest manual ‘Segment Advisor Analysis’ run:
select tablespace_name, segment_owner, segment_name, recommendations, round(reclaimable_space/1024/1024) mb_recl, c1, c2, c3
from table (dbms_space.asa_recommendations(all_runs=>'TRUE', show_manual=>'TRUE', show_findings=>'FALSE'))
where task_id = (select max(task_id) from dba_advisor_executions where task_name='Segment Advisor Analysis');
Sample Output:
TABLESPACE_NAME SEGMENT_OWNER SEGMENT_NAME RECOMMENDATIONS MB_RECL C1 C2 C3
__________________ ________________ _______________ ______________________________________________________________________________________________________ __________ _______________________________________ _______________________________________________ ______________________________________________
USERS USER1 T Enable row movement of the table USER1.T and perform shrink, estimated savings is 164618240 bytes. 157 alter table "USER1"."T" shrink space alter table "USER1"."T" shrink space COMPACT alter table "USER1"."T" enable row movement
If you want to see all findings of the latest manual ‘Segment Advisor Analysis’ run you can run:
select tablespace_name, segment_owner, segment_name, recommendations, c1, c2, c3
from table (dbms_space.asa_recommendations(all_runs=>'TRUE', show_manual=>'TRUE', show_findings=>'TRUE'))
where task_id = (select max(task_id) from dba_advisor_executions where task_name='Segment Advisor Analysis');Sample Output:
TABLESPACE_NAME SEGMENT_OWNER SEGMENT_NAME RECOMMENDATIONS C1 C2 C3
__________________ ________________ _______________ __________________________________________________ _____ _____ _____
USERS HR COUNTRIES The free space in the object is less than 10MB.
USERS HR DEPARTMENTS The free space in the object is less than 10MB.
USERS HR DEPARTMENTS The free space in the object is less than 10MB.
USERS HR DEPARTMENTS The free space in the object is less than 10MB.
USERS HR DEPARTMENTS 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 = 0Restrictions 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 viewsWe 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>
Leave a Reply