Home » RDBMS Server » Server Administration » refresh asa_recommendation (Oracle 10g)
refresh asa_recommendation [message #440521] Mon, 25 January 2010 06:25 Go to next message
Messages: 1
Registered: January 2010
Junior Member
Hi everybody.
I'm new in Oracle and I have a huge (for me) problem.
I try reorganize the tablespaces by observing dbms_space.asa_recommendations. If there is any tablespace with recommendation "re-org" then I do DBMS_REDEFINITION. And my problem is after success reorganization. I don't know why, but in dbms_space.asa_recommendations there aren't any changes. The name of the table recommended to reorganize still appear as recommendation to reorg. When I do dbms_space.advisor also nothing is change. The Automatic Segment Advisor Job has a standard configuration.

I get information from asa_recomendations by:
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from

I try many methods and now I send this question.

The same situation after shrink.

How can I refresh information in dbms_space.asa_recommendations() ? The DBMS_ADVISOR do nothing.

Re: refresh asa_recommendation [message #467156 is a reply to message #440521] Thu, 22 July 2010 06:09 Go to previous message
Messages: 1
Registered: July 2010
Location: CZ
Junior Member
You need to analyze object you reorganized and then check parameters of this function to see that default it uses all (it means also old) runs of auto space advisor:

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 ,
re.task_id, ta.execution_end
FROM TABLE(dbms_space.asa_recommendations()) re, dba_advisor_tasks ta
Where ta.task_id=re.task_id

so use dbms_space.asa_recommendations('FALSE','FALSE','FALSE')
to see latest.

you can also manually invoke the run of advisor by submitting of DB job (under a SYSDBA account):

v_Job Integer;
'Begin dbms_scheduler.run_job(''AUTO_SPACE_ADVISOR_JOB''); End;' );
Previous Topic: Inrease SGA Parameter
Next Topic: MGMT_JOB
Goto Forum:

Current Time: Thu Jul 27 00:17:26 CDT 2017

Total time taken to generate the page: 0.16731 seconds