The company I work for would rebuild their tables and indexes every quarter, this did cause some concern for me and they were adamant that this needed to be done. I compromised and came up with this script.
create or replace PROCEDURE cad_maintenance (
debugme IN VARCHAR2 DEFAULT 'true'
) IS
/*
*
* Author: Mark Young.
* Date: 22-November-2109
* Purpose: It will put freespace back on the list to be reused..
* History: 22/11 - V1.0 - Original version of the code
* 28/11 - v1.1 - Changed from all tables and all indexes, just action only the required objects.
* 02/12 -v1.2 - Bug fixes and testing complete
* Requirements: This script requires the following:
* grant select on dba_tab_statistics to CAD;
* grant execute on dbms_space to CAD;
* grant select on dba_tab_statistics to CAD;
* grant execute on dbms_stats to CAD;
* grant select any dictionary to CAD;
* grant execute on sys.utl_file to CAD;
* grant analyze any to CAD;
*/
fhandle utl_file.file_type;
max_linesize number := 32000;
f_date DATE;
c_date varchar2(50);
v_count integer;
v_str varchar2(255);
t1 integer;
s_date varchar2(50);
tenm number := 10475520;
v_saved varchar2(15);
--Define our cursor and lets see what objects are a candidate for the required action.
CURSOR c_objects IS
SELECT segment_name,
decode(c3,'', NULL, 'X'),replace(c3,chr(34)) AS action1,
decode(c2,'', NULL, 'X'),replace(c2,chr(34)) AS action2,
decode(c1,'', NULL, 'X'),replace(c1,chr(34)) AS action3,
reclaimable_space,
recommendations
FROM
TABLE ( dbms_space.asa_recommendations('FALSE', 'TRUE', 'TRUE') ) --All Runs, Show Manual, Show Findings
WHERE SEGMENT_OWNER='CAD';
BEGIN
--Get the current time so we can calculate how long this procedure takes to run.
t1 := dbms_utility.get_time;
--Lets open the cursor and see what qualifies
SELECT to_char(sysdate,'ddmmyyyyhh24miss'),to_char(sysdate,'dd/mm/yyyy HH24:mi:ss')
INTO c_date, s_date
FROM dual;
dbms_output.put_line('open file');
--Make sure you place the correct directory in the line below;
--select * from dba_directories;
fhandle := utl_file.fopen('MYDIR', c_date || '_table_index_colesce.txt', 'W', max_linesize);
utl_file.put (fhandle, 'Starting at ' || s_date || chr(10));
utl_file.put (fhandle, 'If there are any objects to process.. here is where we will see them' || chr(10));
FOR getobjects IN c_objects
LOOP
--Lets see what action is required and report on the benefits
IF debugme = 'true' THEN
dbms_output.put_line(getobjects.action1 || ' - ' || getobjects.action2 || ' - ' || getobjects.action3);
dbms_output.put_line('writing to file');
if getobjects.reclaimable_space < tenm then
v_saved := ' Not saved ';
else
v_saved := ' Saved ';
end if;
utl_file.put ( fhandle, getobjects.segment_name || ' - ' || getobjects.recommendations || ' - ' || v_saved || getobjects.reclaimable_space || chr(10));
ELSE
if getobjects.reclaimable_space < tenm then
v_saved := ' Not saved ';
else
v_saved := ' Saved ';
end if;
dbms_output.put_line('Output to table_index_colesce for statistics');
utl_file.put (fhandle, 'Output to table_index_colesce for statistics ' || chr(10));
utl_file.put ( fhandle, getobjects.segment_name || ' - ' || getobjects.recommendations || ' - ' || v_saved || getobjects.reclaimable_space || chr(10));
if getobjects.action1 <> 'X' then
utl_file.put (fhandle, 'executing... ' || getobjects.action1 || chr(10));
execute immediate getobjects.action1;
end if;
if getobjects.action2 <> 'X' then
utl_file.put (fhandle, 'executing... ' || getobjects.action2 || chr(10));
dbms_output.put_line( getobjects.action2);
execute immediate getobjects.action2;
end if;
if getobjects.action3 <> 'X' then
utl_file.put (fhandle, 'executing... ' || getobjects.action3 || chr(10));
execute immediate getobjects.action3;
end if;
END IF;
END LOOP;
/* ------ Statistics --------------- */
dbms_output.put_line('Starting to collect statistics');
--Now we need to capture the statistics before they are updated, this can be reverted back if necessary
select count(*) into v_count from dba_objects where object_name='CAD_STATS_TABLE';
IF v_count = 0 THEN
v_str := 'begin dbms_stats.CREATE_STAT_TABLE( ownname=>user, stattab=>''CAD_STATS_TABLE''); end;';
if debugme = 'true' then
dbms_output.put_line(v_str);
else
execute immediate v_str;
end if;
END IF;
--Take a copy of the statistics (this is important, otherwise you have nothing to go back to
v_str := 'begin dbms_stats.export_schema_stats( ownname=>user, stattab=>''CAD_STATS_TABLE'', statid=>''CURRENT_STATS''); end;';
if debugme = 'true' then
dbms_output.put_line(v_str);
else
execute immediate v_str;
end if;
FOR stalestats in (SELECT table_name FROM dba_tab_statistics WHERE stale_stats = 'YES' and STATTYPE_LOCKED='ALL' AND owner='CAD')
LOOP
utl_file.put (fhandle, 'Unlock Statistics' || chr(10));
v_str := 'begin dbms_stats.unlock_table_stats(''CAD'',''' || stalestats.table_name || ''' ); end;';
if debugme = 'true' then
--unlock the table statistics
dbms_output.put_line (v_str);
else
execute immediate v_str;
end if;
END LOOP;
--gather the table statistics across the schema, this is more efficient
utl_file.put (fhandle, 'Gather Statistics' || chr(10));
v_str := 'begin dbms_stats.gather_schema_stats(ownname=>''CAD'',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4); end;';
if debugme = 'true' then
dbms_output.put_line(v_str);
else
execute immediate v_str;
end if;
--Finally need to lock the statistics for another time
utl_file.put (fhandle, 'Lock Statistics' || chr(10));
FOR lock_stats in (SELECT table_name FROM dba_tab_statistics WHERE owner='CAD')
LOOP
v_str := 'begin dbms_stats.lock_table_stats(''CAD'',''' || lock_stats.table_name || '''); end;';
IF debugme = 'true' THEN
--lock the table statistics
dbms_output.put_line(v_str);
ELSE
EXECUTE IMMEDIATE v_str;
END IF;
END LOOP;
dbms_output.put_line('Closing open file');
utl_file.put (fhandle, 'Completed - ' || (dbms_utility.get_time - t1)/100 || ' seconds' || chr(13));
utl_file.fclose(fhandle);
EXCEPTION
WHEN no_data_found THEN
--utl_file.fclose(fhandle);
NULL;
WHEN OTHERS THEN
raise_application_error(-20001, 'An error was encountered - ' || sqlcode || ' -ERROR- ' || sqlerrm);
utl_file.fclose(fhandle);
END;