SQL Script

10g Segment Advisor

Updated: 02-Jun-2005
Version: 1.0

Description

Sample Script in order to run the Segment Advisor from PLSQL

Parameters

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set echo on 
set serveroutput on size 999999 

col segment_name form a30
col Mb form 9,999.99 

spool seg_advisor.txt

select owner,segment_name,blocks, bytes/1024/1024 Mb
from dba_segments where segment_name = 'MGMT_SYSTEM_ERROR_LOG';

REM Flushes the in-memory stats to SWRF repos 
REM execute dbms_workload_repository.create_snapshot('ALL');
REM run the advisor again 

variable id number;
 begin 

declare 
name varchar2(100) ; 
descr varchar2(500) ; 
objid number; 
begin 
 name := '' ; 
 descr := 'TASK Table Advisor for T' ;
 dbms_advisor.create_task('Segment Advisor', :id, name, descr, NULL) ;
 dbms_output.put_line('ID = ' || :id || ' Name = ' || name) ;
 dbms_advisor.create_object(name, 'TABLE', 'AKD','T', NULL, NULL, objid);
 dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE') ;
 dbms_advisor.set_task_parameter(name, 'VERBOSE', 'TRUE') ;
 dbms_advisor.execute_task(name) ;
 end ;
end ;
/ 

spool off

Return to Index of SQL Scripts

Site Map

SQL Script

10g Segment Advisor

Updated: 02-Jun-2005Version: 1.0

Description

Parameters

SQL Source

Site Map

Updated: 02-Jun-2005
Version: 1.0