This discussion is archived
2 Replies Latest reply: Nov 16, 2012 5:30 PM by rp0428 RSS

Reclaiming space?

apex_disco Newbie
Currently Being Moderated
DB10gR2

EDIT: I was testing the sql alter index abc shrink space; and when I requeried, I was able to see the space shrunk.

However, it doesn't work for alter table syz shrink space cascade; command

So I issued the following query that I found - might be from AskTom or someplace else. Have been browsing the net
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()) 
where segment_owner = 'schemaname' 
order by reclaim_mb desc
And found table XYZ to have
alloc_mb: 2663 mb
used_mb: 1521.7mb
reclaim_mb: 1141mb
pctsave: 43

So I issued the commands
alter table xyz enable row movement;
alter table xyz shrink space cascade;
waited for it to complete then issue
alter table xyz disable row movement ;
Then I run the query
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()) 
where segment_owner = 'schemaname' 
order by reclaim_mb desc
and the results are still the same
alloc_mb: 2663 mb
used_mb: 1521.7mb
reclaim_mb: 1141mb
pctsave: 43

So, am I wrong to expect that it'll actually reclaim the 1141mb and table XYZ would have shrunk?? And the query shouldn't have returned table XYZ again?

-confused-

Edited by: apex_disco on Nov 16, 2012 2:09 PM
  • 1. Re: Reclaiming space?
    jgarry Guru
    Currently Being Moderated
    I believe it will honor pctfree, so it could conceivably make a dense table grow larger.

    The procedure you are looking at is for segment advisor recommendations, so that advisor needs to be run again (probably auto runs nightly).
  • 2. Re: Reclaiming space?
    rp0428 Guru
    Currently Being Moderated
    >
    So, am I wrong to expect that it'll actually reclaim the 1141mb and table XYZ would have shrunk?? And the query shouldn't have returned table XYZ again?
    >
    You are wrong to expect that you will get different results from calling the DBMS_SPACE.ASA_RECOMMENDATIONS procedure without rerunning the advisor.

    You do know, don't you, that you actually have to execute the Automatic Segment Advisor? You don't show anything that indicates that you ran the advisor again after shrinking the table.

    If you query the SCOTT.EMP table (e.g. SELECT * FROM SCOTT.EMP) you will get a set of data. Unless someone changes the data you will ALWAYS get the same set of data everytime you query the table.

    Same thing with dbms_space.asa_recommendations. All it does is return data that has already been accumulated. If you query that procedure without rerunning the advisor you wll keep getting the same set of data every time; just like querying SCOTT.EMP.

    See Reclaiming Wasted Space in the DBA Guide
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm
    >
    Viewing Segment Advisor Results with DBMS_SPACE.ASA_RECOMMENDATIONS

    The ASA_RECOMMENDATIONS procedure in the DBMS_SPACE package returns a nested table object that contains findings or recommendations for Automatic Segment Advisor runs and, optionally, manual Segment Advisor runs. Calling this procedure may be easier than working with the DBA_ADVISOR_* views, because the procedure performs all the required joins for you and returns information in an easily consumable format.

    The following query returns recommendations by the most recent run of the Auto Segment Advisor, with the suggested command to run to follow the recommendations:
    >
    The query returns recommendations 'by the most recent run'; if you don't rerun the advisor you will keep getting the same results.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points