This content has been marked as final. Show 2 replies
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).
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
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.