Execute Oracle’s Segment Advisor manually

Oracle’s Segment Advisor, a feature introduced in Oracle 10g, has helped a lot of dba’s with their day-2-day maintenance tasks.

The segment advisor is used for identifying segments which are candidates for a ‘shrink’-operation (releasing unused space and moving the High Water Mark) :

 On some occasions you might want to run the segment advisor manually for one specific segment :

1 – Create a task

seg_task_id   number;
seg_task_name varchar2(100);
seg_task_desc varchar2(500);
seg_task_name := ‘AdviceTest’;
seg_task_desc := ‘Manual Segment Advisor Run for table TESTDATA’;
dbms_advisor.create_task (
advisor_name => ‘Segment Advisor’,
task_id      => seg_task_id,
task_name    => seg_task_name,
task_desc    => seg_task_desc);

2 – Create an advisor object for specific object

obj_id        number;
dbms_advisor.create_object (
task_name   => ‘AdviceTest’,
object_type => ‘TABLE’,
attr1       => ‘ORACLE’, –OWNER
attr2       => ‘TESTDATA’, –TABLE NAME
attr3       => NULL,
attr4       => NULL,
attr5       => NULL,
object_id   => obj_id);

3 – Set the parameters for the job

task_name => ‘AdviceTest’,
parameter => ‘recommend_all’,
value     => ‘TRUE’);

4 – Run the job

exec dbms_advisor.execute_task(‘AdviceTest’);

5 – With the following query you can find out if there are recommendations :

SQL> select message,more_info from dba_advisor_findings where task_name=’AdviceTest’;

Enable row movement of the table ORACLE.TESTDATA and perform shrink, estimated savings is 102455931 bytes.
Allocated Space:285212672: Used Space:182756741: Reclaimable Space :102455931:

1 row selected.

6 – Implement the recommendations if required. Take care that a shrink operation on a production system can have a serious impact on the performance !!:

SQL> select bytes from dba_segments where segment_name=’TESTDATA’;


1 row selected.

SQL> alter table testdata enable row movement;

Table altered.

SQL> alter table testdata shrink space;

Table altered.

SQL> select bytes from dba_segments where segment_name=’TESTDATA’;


1 row selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s