[reading notes] manually generate paragraph consultant's suggestion
DECLARE
My_task_id number
Obj_id number
My_task_name varchar2 (100)
My_task_desc varchar2 (500)
BEGIN
My_task_name: = 'Table_Segment_Advice'
My_task_desc: = 'Manual Segment Advisor Run'
-
-- Step 1 creates a task
-
Dbms_advisor.create_task (
Advisor_name = > 'Segment Advisor'
Task_id = > my_task_id
Task_name = > my_task_name
Task_desc = > my_task_desc)
-
-- Step 2 assigns an object to this task
-
Dbms_advisor.create_object (
Task_name = > my_task_name
Object_type = > 'TABLE'
Attr1 = > 'user_name'
Attr2 = > 'table_name'
Attr3 = > NULL
Attr4 = > NULL
Attr5 = > NULL
Object_id = > obj_id)
-
-- Step 3 sets task parameters
-
Dbms_advisor.set_task_parameter (
Task_name = > my_task_name
Parameter = > 'recommend_all'
Value = > 'TRUE')
-
-- Step 4 performs this task
-
Dbms_advisor.execute_task (my_task_name)
END
Review the consultant's recommendations:
Select b.advisor_name,c.attr1,c.attr2,a.task_name,a.message,a.more_info
From DBA_ADVISOR_FINDINGS a,DBA_ADVISOR_TASKS b,dba_advisor_objects c
Where a.task_id=b.task_id
And b.advisor_name='Segment Advisor'
And b.taskmate nameplate TableSegmentationAdvice`
And a.object_id=c.object_id
And a.task_id=c.task_id
Or
SELECT
'Segment Advice--'| | chr (10) | |
'TABLESPACE_NAME:' | | tablespace_name | | chr (10) | |
'SEGMENT_OWNER:' | | segment_owner | | chr (10) | |
'SEGMENT_NAME:' | | segment_name | | chr (10) | |
'ALLOCATED_SPACE:' | | allocated_space | | chr (10) | |
'RECLAIMABLE_SPACE:' | | reclaimable_space | | chr (10) | |
'RECOMMENDATIONS:' | | recommendations | | chr (10) | |
'SOLUTION 1:' | | C1 | | chr (10) | |
'SOLUTION 2:' | | c2 | | chr (10) | |
'SOLUTION 3:' | | c3 Advice
FROM
TABLE (dbms_space.asa_recommendations ('TRUE',' FALSE', 'FALSE'))
