Thanks for the question.

Asked: October 20, 2009 - 8:52 am UTC

Last updated: January 31, 2013 - 9:06 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Tom ,

Segment Advisor does not seem to be giving the accurate space information . Per Example below , the reclaimable space does not match with the space occupied after performing shrink operations.

Please advise , if my understanding is incorrect.

SQL> select sum(bytes)from USER_SEGMENTS where segment_name = 'TCA';


SUM(BYTES)

----------

 228589568

 

SQL> delete from tca;


3606232 rows deleted

 

SQL> select sum(bytes)from USER_SEGMENTS where segment_name = 'TCA';

 

SUM(BYTES)

----------

 228589568


SQL> commit;

Commit complete

 

SQL> select sum(bytes)from USER_SEGMENTS where segment_name = 'TCA';

SUM(BYTES)

----------

 228589568

 
Ran the segment advisor from the OEM.
 


SQL>   SELECT allocated_space , used_space , reclaimable_space

  2        FROM TABLE(dbms_space.asa_recommendations())

  3        Where  segment_owner  = user

  4        and segment_type ='TABLE'

  5        and  segment_name = 'TCA'

  6  /

 

ALLOCATED_SPACE USED_SPACE RECLAIMABLE_SPACE

--------------- ---------- -----------------

      228589568   32917933         195671635

      

      

SQL> alter table "TCA" enable row movement;

      Table altered

SQL> alter table "TCA" shrink space COMPACT;

      Table altered

SQL> alter table "TCA" shrink space;

      Table altered

SQL> select sum(bytes)from USER_SEGMENTS where segment_name = 'TCA';

      SUM(BYTES)

      ----------

           65536

and Tom said...

Everything is an estimate. It is like gathering statistics - we usually use an ESTIMATE. It is like the cardinality in a query plan (explain plan) - it is an estimate.

The segment advisor did a quick sample of that segment and said "hey, this thing is currently large, about 218mb. A quick and dirty sample of the table shows it appears to be mostly empty - we think it would shrink down to be at most about 15% of the current size - it would definitely be a candidate to consider shrinking"

It'll always err on the side of caution, it's job is to find the candidates for shrinking and express a guesstimate of how much space could be saved (if it did the EXACT numbers, it would take so long to complete, it would be useless - you'd have to read the entire database - every byte of it - every time! It is all about samples)

Consider:

ops$tkyte%ORA10GR2> create or replace procedure run_sa
  2  authid current_user
  3  as
  4    obj_id number;
  5  begin
  6    dbms_advisor.create_task (
  7      advisor_name     => 'Segment Advisor',
  8      task_name        => 'Manual_T' );
  9
 10    dbms_advisor.create_object (
 11      task_name        => 'Manual_T',
 12      object_type      => 'TABLE',
 13      attr1            => user,
 14      attr2            => 'T',
 15      attr3            => NULL,
 16      attr4            => NULL,
 17      attr5            => NULL,
 18      object_id        => obj_id);
 19
 20    dbms_advisor.set_task_parameter(
 21      task_name        => 'Manual_T',
 22      parameter        => 'recommend_all',
 23      value            => 'TRUE');
 24
 25    dbms_advisor.execute_task('Manual_T');
 26  end;
 27  /

Procedure created.

That'll let us run the advisor from sqlplus whenever...

In the following, we know that the table could be just one extent (64k in my case) - but the sampling is always going to leave room for error - but (seriously) SO WHAT? You have accomplished the goal - "what are the candidate segments for shrinking and what would be a good ball park estimate on what I would save"

ops$tkyte%ORA10GR2> @test 2
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select *
  4    from all_objects, (select level l from dual connect by level <= &1)
  5  /
old   4:   from all_objects, (select level l from dual connect by level <= &1)
new   4:   from all_objects, (select level l from dual connect by level <= 2)

Table created.

ops$tkyte%ORA10GR2> select sum(bytes)from USER_SEGMENTS where segment_name = 'T';

SUM(BYTES)
----------
  12582912

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from t;

100380 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec run_sa

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT segment_name,
  2         round(allocated_space/1024/1024,1) alloc_mb,
  3         round( used_space/1024/1024, 1 ) used_mb,
  4             round( reclaimable_space/1024/1024) reclaim_mb,
  5             round(reclaimable_space/allocated_space*100,0) pctsave,
  6             recommendations
  7    FROM TABLE(dbms_space.asa_recommendations())
  8   where segment_owner = user
  9  /

SEGME ALLOC_MB  USED_MB RECLAIM_MB  PCTSAVE RECOMMENDATIONS
----- -------- -------- ---------- -------- ----------------------------------------
T         12.0      1.5       11.0     88.0 Enable row movement of the table OPS$TKY
                                            TE.T and perform shrink, estimated savin
                                            gs is 11019016 bytes.


ops$tkyte%ORA10GR2> exec  dbms_advisor.delete_task('Manual_T');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @test 20
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select *
  4    from all_objects, (select level l from dual connect by level <= &1)
  5  /
old   4:   from all_objects, (select level l from dual connect by level <= &1)
new   4:   from all_objects, (select level l from dual connect by level <= 20)

Table created.

ops$tkyte%ORA10GR2> select sum(bytes)from USER_SEGMENTS where segment_name = 'T';

SUM(BYTES)
----------
 125829120

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from t;

1003800 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec run_sa

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT segment_name,
  2         round(allocated_space/1024/1024,1) alloc_mb,
  3         round( used_space/1024/1024, 1 ) used_mb,
  4             round( reclaimable_space/1024/1024) reclaim_mb,
  5             round(reclaimable_space/allocated_space*100,0) pctsave,
  6             recommendations
  7    FROM TABLE(dbms_space.asa_recommendations())
  8   where segment_owner = user
  9  /

SEGME ALLOC_MB  USED_MB RECLAIM_MB  PCTSAVE RECOMMENDATIONS
----- -------- -------- ---------- -------- ----------------------------------------
T        120.0     14.9      105.0     88.0 Enable row movement of the table OPS$TKY
                                            TE.T and perform shrink, estimated savin
                                            gs is 110200429 bytes.


ops$tkyte%ORA10GR2> exec  dbms_advisor.delete_task('Manual_T');

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @test 40
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select *
  4    from all_objects, (select level l from dual connect by level <= &1)
  5  /
old   4:   from all_objects, (select level l from dual connect by level <= &1)
new   4:   from all_objects, (select level l from dual connect by level <= 40)

Table created.

ops$tkyte%ORA10GR2> select sum(bytes)from USER_SEGMENTS where segment_name = 'T';

SUM(BYTES)
----------
 239075328

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from t;

2007600 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec run_sa

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT segment_name,
  2         round(allocated_space/1024/1024,1) alloc_mb,
  3         round( used_space/1024/1024, 1 ) used_mb,
  4             round( reclaimable_space/1024/1024) reclaim_mb,
  5             round(reclaimable_space/allocated_space*100,0) pctsave,
  6             recommendations
  7    FROM TABLE(dbms_space.asa_recommendations())
  8   where segment_owner = user
  9  /

SEGME ALLOC_MB  USED_MB RECLAIM_MB  PCTSAVE RECOMMENDATIONS
----- -------- -------- ---------- -------- ----------------------------------------
T        228.0     29.8      198.0     87.0 Enable row movement of the table OPS$TKY
                                            TE.T and perform shrink, estimated savin
                                            gs is 207840702 bytes.


ops$tkyte%ORA10GR2> exec  dbms_advisor.delete_task('Manual_T');

PL/SQL procedure successfully completed.

Rating

(15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks .

Zahir M, October 20, 2009 - 11:23 am UTC

Thanks Tom .

Query regarding Segment Advisor

lyxx09, February 01, 2010 - 11:20 pm UTC

Hi Tom,

20 dbms_advisor.set_task_parameter(
21 task_name => 'Manual_T',
22 parameter => 'recommend_all',
23 value => 'TRUE');

you have set this explicitly but it seems it's by default true as per the documentation.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_advis.htm#CIHIGAED

second point - if I want to run this on big tablespaces like 2/3 TB then what paramemter settings you will suggest ?

time_limit ?

I understand that segment advisor is scheduled to run but with limited criteria to pick tables. Can you please share your thoughts if in case you want to run explicitly on big tablespaces with lot of segments .. after any heavy load/quarter end .

Thanks-

February 02, 2010 - 12:19 pm UTC

I was probably playing around with different values during the asking of the question. But - it never hurts to not rely on defaults so no issue there.

... second point - if I want to run this on big tablespaces like 2/3 TB then what
paramemter settings you will suggest ?
...

if there were suggested settings that could be suggested without any sort of insight, any sort of context - they would be.... defaults - so, I guess, the suggested would be use to the defaults? And adjust as you see fit.

I don't really have any beyond out of the box customizations to suggest.

Recommend_all

lyxx, February 05, 2010 - 11:59 pm UTC

Thanks Tom,

As per my understanding on documentation - recommend_all=TRUE mean it will generate recommendation for each and every segment it's analyzed... and FALSE means suggestion for the only segment which are suggested to shrink/re-org.

But it seems it's not working for me as per the test case below. I have one table in TEST schema where I deleted almost 90% data from table.

If I choose recommend_all=TRUE then I see recommendation

SQL> exec dbms_advisor.create_Task('Segment Advisor','mytask');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.create_object('mytask','TABLE','TEST','T',null,null,:b);

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.set_Task_parameter(task_name=>'mytask',parameter=>'recommend_all',value=>'TRUE');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task('mytask');

PL/SQL procedure successfully completed.

SQL> SELECT segment_name,
2 round(allocated_space/1024/1024,1) alloc_mb,
3 round( used_space/1024/1024, 1 ) used_mb,
4 round( reclaimable_space/1024/1024) reclaim_mb,
5 round(reclaimable_space/allocated_space*100,0) pctsave,
6 recommendations
7 FROM TABLE(dbms_space.asa_recommendations())
8 where segment_owner = 'TEST'
9 /

SEGMENT_NAME ALLOC_MB USED_MB RECLAIM_MB PCTSAVE RECOMMENDATIONS
------------------------------ ---------- ---------- ---------- ---------- ------------------------------------------------------------
T 104.1 10.5 94 90 Perform re-org on the object T, estimated savings is
98168930 bytes.

But If I choose recommend_all=FALSE then I do not see any recommendation. I also check directly in dba_adivosr_findings and recommendation.

SQL> exec dbms_advisor.reset_Task('mytask');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.delete_Task('mytask');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.create_Task('Segment Advisor','mytask');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.create_object('mytask','TABLE','TEST','T',null,null,:b);

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.set_Task_parameter(task_name=>'mytask',parameter=>'recommend_all',value=>'FALSE');

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task('mytask');

PL/SQL procedure successfully completed.

SQL> SELECT segment_name,
2 round(allocated_space/1024/1024,1) alloc_mb,
3 round( used_space/1024/1024, 1 ) used_mb,
4 round( reclaimable_space/1024/1024) reclaim_mb,
5 round(reclaimable_space/allocated_space*100,0) pctsave,
6 recommendations
7 FROM TABLE(dbms_space.asa_recommendations())
8 where segment_owner = 'TEST'
9 /

no rows selected

SQL>

Alexander, April 14, 2010 - 10:55 am UTC

Tom,

Can you look over the following, do I have a flaw in my logic? I want to use dbms_advisor to identify the top number of large schema objects based on a size specified. The point is to help track down the objects that have suffered wild out of the ordinary fragmentation.

/database/oracle
(x212kdc:oracle)> sqlplus /

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Apr 14 11:42:28 2010

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>  select task_name from dba_advisor_tasks where owner = 'OPS$ORACLE';

no rows selected

SQL> select segment_name, segment_type
 from dba_segments
where owner = 'SERVICE'
 and bytes > 1073741824
 and segment_type in ('TABLE', 'INDEX')  2    3    4    5
  6  /

SEGMENT_NAME                                                                      SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
INCIDENTSM1                                                                       TABLE
PROBSUMMARYM1                                                                     TABLE
ACTIVITYM1                                                                        TABLE
PROBLEMM1                                                                         TABLE
EVENTINM1                                                                         TABLE
SCIREXPERTM1                                                                      TABLE

6 rows selected.

SQL> declare
  2        obj_id number;
  3      begin
  4        for x in (select segment_name, segment_type
  5              from dba_segments
            where owner = 'SERVICE'
  6    7              and bytes > 1073741824
  8              and segment_type in ('TABLE', 'INDEX')) loop
  9
 10        dbms_advisor.create_task (
 11          advisor_name     => 'Segment Advisor',
 12          task_name        => x.segment_name );
 13
      dbms_advisor.create_object (
 14   15         task_name        => x.segment_name,
       object_type      => x.segment_type,
 16   17         attr1            => user,
       attr2            => x.segment_name,
 18   19         attr3            => NULL,
 20         attr4            => NULL,
 21         attr5            => NULL,
 22         object_id        => obj_id);
 23
 24       dbms_advisor.set_task_parameter(
 25         task_name        => x.segment_name,
 26         parameter        => 'recommend_all',
 27         value            => 'TRUE');
 28
 29       dbms_advisor.execute_task(x.segment_name);
     --dbms_advisor.delete_task(x.segment_name);
 30   31      end loop;
 32     end;
 33     /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at line 29


SQL> select task_name from dba_advisor_tasks where owner = 'OPS$ORACLE';

TASK_NAME
------------------------------
INCIDENTSM1

1 row selected.

April 14, 2010 - 3:51 pm UTC

if you have access to the packs, why don't you just use the built in job that tells you this??? It already happens, just go into the gui and read it?

Alexander, April 14, 2010 - 8:23 pm UTC

What job? What gui? I don't think it is happening already because when I query DBMS_SPACE.ASA_RECOMMENDATIONS the information wasn't already populated.

April 15, 2010 - 8:17 am UTC

there is a segment advisor that would report on any segments that would benefit from shrinking

https://asktom.oracle.com/Misc/oramag/on-favorites-and-connect-by.html

and if nothing is populated - that doesn't mean nothing is running - it likely just means that the need to reorganize (which is truly rare) doesn't exist for you.

Alexander, April 15, 2010 - 9:04 am UTC

That "show sql" button is what I'm interested in.

Is that the db console? Or grid control? We don't use either, not a big fan. I'm even sure how to access it. We're mostly command line people.

April 15, 2010 - 9:08 am UTC

I suggest for tuning and reporting, you lose that mindset.

Don't get me wrong, I'm command line central - but you cannot ignore it (the GUI). It actually provides things that you simply will not get in command line mode. This is your perfect chance to get caught up and learn it. Honestly - you won't become instantly dumb or anything - in fact, you'll become a tad more efficient and able to find out things even faster then you do now.

In other words, let yourself get dragged kicking and screaming into the year 2010.

Read the article, it walks you through it - it shows they were using dbconsole.

so simple it is now

A reader, April 15, 2010 - 9:33 am UTC

oh, many times in this forum I have asked Tom about how to find the segments where the HWM is high, Tom answered too..but I think Segment advisor is the answer , effortless to understand .

Thanks

What is the need of shrinking ?

Matt, June 05, 2010 - 5:15 am UTC

Tom,

One of the tables has 50 million rows and the advisor shows that 15% empty. But all the queries on the table go for index scan (OLTP) and no full table scan required. Now please tell me the use of shrinking / rebuilting the table and indexes. First of all the shrinking is required in this case?

June 09, 2010 - 7:50 am UTC

you would probably gain nothing performance wise from this operation.

You might be able to return 10-15% the size of this existing table back to dba_free_space.

That is what you would gain - that last point. If that is not relevant to you (for example, you know the table will be growing again anyway), then you have nothing more to consider here, ignore the recommendation.

Segment shrink will benifit FTS and Index Full Scans. Is it true of false

Frank, June 10, 2010 - 5:25 am UTC

Tom,

Segment shrink would improve the performance of FTS and Index Full Scans. Is it true of false?

June 10, 2010 - 2:25 pm UTC

it is:

a) true
b) false
c) neither true nor false, sometimes a bit of both....

Let us look at a true case:

The table had 1,000,000,000 rows in it. The table now has 500,000 rows in it. It is likely that an alter table shrink space would have a noticeable impact on full scan performance as there will be far fewer blocks in it than before.

Let us look at a false case:

The table is only ever inserted into, we use partitions to roll off old archive data (so there is a de-facto 'delete', but we did it smart by using partitions). A shrink space compact would just eat CPU and IO cycles and do nothing for you.

Let us look at a true/false case:

Index had every other row deleted, shrink space removed them making the index 60% of the original size. Index fast full scan/Index full scan read less blocks, benefit from that - but the process that is charged with putting rows into the table (the index was on last name) is penalized because it will just have to split out the index blocks all over again. Here - one thing is "made better" and one thing is "made worse"

In most cases in real life - the third situation is what you are faced with - there will be benefits in one area and costs in another. You do the cost benefit analysis to see which is better than the other.

Index split

Peter, June 11, 2010 - 2:08 am UTC

.... but the process that is charged with putting rows into the table (the index was on last name) is penalized because it will just have to split out the index blocks all over again.

Tom, Can you please explain? I don't get anything.

June 11, 2010 - 7:31 am UTC

you have an index

it is on last name

each leaf block is about 1/2 empty.

If you insert right now a new record, there will be space on the leaf block it must go on to be inserted into. No work needs be done to make the index wider. The insert happens fast and efficiently. EVERY NOW AND THEN - you will have to split a leaf block when it fills up and it'll split into two half empty blocks starting the process all over again. So, the last name index really wants to be 50% white space to be at its best for inserts and updates.

Then you go and shrink it. It is now probably 10% empty on each leaf block. Now, for a long while, all of your inserts are going to have a very HIGH PROBABILITY of having to perform a leaf block split since 10% free is small. They will split leaf blocks in response to inserts and updates very often and very soon and for a very long time (since all blocks are almost full - it'll be a while till most of them are 50% full again). So now your inserts and updates have to do a lot of work, generate a lot of extra modified blocks, which generates undo and redo and the undo itself generates more redo...

And you end up with (after a while) an index that is 50% white space again. So, you rebuild it and bam - same thing over and over and over again

just like this:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

very good

Rahul, July 16, 2010 - 7:28 pm UTC

Hi Tom,

I went thru the whole discussion and got lot of useful stuff. Now i was in the process of generating the advice for TABLESPACE, but unfortunately getting error. I have litrelly searched whole internet/google but could not find a single example of tablespace advice. Below is my code with error, please suggest what is wrong in it:

SQL> conn / as sysdba
Connected.

SQL> variable id number;
SQL> begin
  2    declare
  3    name varchar2(100);
  4    descr varchar2(500);
  5    obj_id number;
  6    begin
  7    name:='tblspc';
  8    descr:='Segment Advisor Example';
  9
 10    dbms_advisor.create_task (
 11      advisor_name     => 'Segment Advisor',
 12      task_id          => :id,
 13      task_name        => name,
 14      task_desc        => descr);
 15
 16    dbms_advisor.create_object (
 17      task_name        => name,
 18      object_type      => 'TABLESPACE',
 19      attr1            => 'USERS',
 20      attr2            => NULL,
 21      attr3            => NULL,
 22      attr4            => NULL,
 23      attr5            => NULL,
 24      object_id        => obj_id);
 25
 26    dbms_advisor.set_task_parameter(
 27      task_name        => name,
 28      parameter        => 'recommend_all',
 29      value            => 'TRUE');
 30
 31    dbms_advisor.execute_task(name);
 32    end;
 33  end;
 34  /
begin
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at line 31

Thanks a lot in advance.

July 19, 2010 - 1:49 pm UTC

I just ran that as a cut and paste in my system and it "just ran".

please try not to use sysdba for things that do not need sysdba.

it may be bug

Rahul, July 19, 2010 - 9:24 pm UTC

On few website I found people say its a bug in Oracle 10.2.0.1 version. As I have the same so it may be.

Please confirm.

July 23, 2010 - 6:44 am UTC

10.2.0.1 is very much unpatched, I would suggest getting patched up - definitely - before wasting time on trying to track something like this down.

A reader, January 19, 2012 - 9:02 am UTC

Hi Tom,

I have run below script but no rows found.Please help :

begin
dbms_advisor.delete_task('Manual_T');
end ;

begin
-- Call the procedure
run_sa;
end;

SELECT segment_name,
round(allocated_space/1024/1024,1) alloc_mb,
round( used_space/1024/1024, 1 ) used_mb,
round( reclaimable_space/1024/1024) reclaim_mb,
round(reclaimable_space/allocated_space*100,0) pctsave,
recommendations
FROM TABLE(dbms_space.asa_recommendations())

create or replace procedure run_sa
authid current_user
as
obj_id number;
begin
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_name => 'Manual_T' );

dbms_advisor.create_object (
task_name => 'Manual_T',
object_type => 'TABLE',
attr1 => user,
attr2 => 'CH_NOBOOK',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);

dbms_advisor.set_task_parameter(
task_name => 'Manual_T',
parameter => 'recommend_all',
value => 'TRUE');

dbms_advisor.execute_task('Manual_T');
end;

January 19, 2012 - 12:37 pm UTC

good, that means you have nothing to do.

it only shows things that MIGHT need attention

A reader, July 31, 2012 - 9:34 am UTC

What is the difference between

alter table .. move 
and 
alter table ... shrink space [compact] ?

Under what circumstances I should perform one over the other?

Thanks

July 31, 2012 - 12:53 pm UTC

alter table move is an offline operation that reads a segment and writes a brand new segment. You cannot modify the table while this happens.

alter table shrink space is an online operation that reads a single segment from the "bottom" and finds the last row - deletes it - and reinserts it at the top of the table - and it keeps doing this until all of the rows are at the 'top' of the table. Then it can optionally redraw the high water of the table and deallocate some extents from it. If you have it do that - it will lock the segment for this redrawing of the high water mark.

if you need online, shrink
if you want to rebuild the segment, move
if you want to put the segment in a new tablespace, move
if you are stretched on space and just want to reclaim some, shrink

and so on. now that you know what they do, you should be able to pick the one that makes the most sense to you in your circumstances.

List of oracle advisers

Arvind Mishra, January 28, 2013 - 4:49 pm UTC

Hi Tom,

Can you please give me URL of a document having list of Oracle advisers and when and how to use them.

It seems like there are many advisers and information about them is scattered in different Oracle manuals.

Thanks,

Arvind

January 31, 2013 - 9:06 am UTC

http://docs.oracle.com/cd/E11882_01/server.112/e10897/montune.htm#ADMQS103

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer