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.<\/p>\n\n\n\n
create or replace PROCEDURE cad_maintenance (\n debugme IN VARCHAR2 DEFAULT 'true'\n) IS\n\/*\n* \n* Author: Mark Young. \n* Date: 22-November-2109\n* Purpose: It will put freespace back on the list to be reused.. \n* History: 22\/11 - V1.0 - Original version of the code\n* 28\/11 - v1.1 - Changed from all tables and all indexes, just action only the required objects.\n* 02\/12 -v1.2 - Bug fixes and testing complete \n\n* Requirements: This script requires the following:\n* grant select on dba_tab_statistics to CAD;\n* grant execute on dbms_space to CAD;\n* grant select on dba_tab_statistics to CAD;\n* grant execute on dbms_stats to CAD; \n* grant select any dictionary to CAD;\n* grant execute on sys.utl_file to CAD; \n* grant analyze any to CAD; \n*\/\n\n fhandle utl_file.file_type;\n max_linesize number := 32000; \n f_date DATE;\n c_date varchar2(50);\n v_count integer;\n v_str varchar2(255);\n t1 integer;\n s_date varchar2(50);\n tenm number := 10475520;\n v_saved varchar2(15);\n \n --Define our cursor and lets see what objects are a candidate for the required action.\n CURSOR c_objects IS\n SELECT segment_name,\n decode(c3,'', NULL, 'X'),replace(c3,chr(34)) AS action1,\n decode(c2,'', NULL, 'X'),replace(c2,chr(34)) AS action2,\n decode(c1,'', NULL, 'X'),replace(c1,chr(34)) AS action3,\n reclaimable_space,\n recommendations\n FROM\n TABLE ( dbms_space.asa_recommendations('FALSE', 'TRUE', 'TRUE') ) --All Runs, Show Manual, Show Findings \n WHERE SEGMENT_OWNER='CAD';\n\nBEGIN\n --Get the current time so we can calculate how long this procedure takes to run.\n t1 := dbms_utility.get_time;\n --Lets open the cursor and see what qualifies\n SELECT to_char(sysdate,'ddmmyyyyhh24miss'),to_char(sysdate,'dd\/mm\/yyyy HH24:mi:ss') \n INTO c_date, s_date\n FROM dual;\n \n dbms_output.put_line('open file');\n --Make sure you place the correct directory in the line below;\n --select * from dba_directories; \n fhandle := utl_file.fopen('MYDIR', c_date || '_table_index_colesce.txt', 'W', max_linesize);\n utl_file.put (fhandle, 'Starting at ' || s_date || chr(10)); \n utl_file.put (fhandle, 'If there are any objects to process.. here is where we will see them' || chr(10)); \n \n FOR getobjects IN c_objects\n LOOP\n \n --Lets see what action is required and report on the benefits\n \n IF debugme = 'true' THEN\n \n dbms_output.put_line(getobjects.action1 || ' - ' || getobjects.action2 || ' - ' || getobjects.action3);\n dbms_output.put_line('writing to file');\n\n if getobjects.reclaimable_space < tenm then\n v_saved := ' Not saved ';\n else\n v_saved := ' Saved ';\n end if;\n \n utl_file.put ( fhandle, getobjects.segment_name || ' - ' || getobjects.recommendations || ' - ' || v_saved || getobjects.reclaimable_space || chr(10));\n \n ELSE\n if getobjects.reclaimable_space < tenm then\n v_saved := ' Not saved ';\n else\n v_saved := ' Saved ';\n end if;\n \n dbms_output.put_line('Output to table_index_colesce for statistics');\n utl_file.put (fhandle, 'Output to table_index_colesce for statistics ' || chr(10)); \n utl_file.put ( fhandle, getobjects.segment_name || ' - ' || getobjects.recommendations || ' - ' || v_saved || getobjects.reclaimable_space || chr(10));\n \n if getobjects.action1 <> 'X' then\n utl_file.put (fhandle, 'executing... ' || getobjects.action1 || chr(10)); \n execute immediate getobjects.action1;\n end if;\n \n if getobjects.action2 <> 'X' then\n utl_file.put (fhandle, 'executing... ' || getobjects.action2 || chr(10)); \n dbms_output.put_line( getobjects.action2);\n execute immediate getobjects.action2;\n end if;\n \n if getobjects.action3 <> 'X' then\n utl_file.put (fhandle, 'executing... ' || getobjects.action3 || chr(10)); \n execute immediate getobjects.action3;\n end if;\n END IF;\n\nEND LOOP;\n \n \/* ------ Statistics --------------- *\/\n dbms_output.put_line('Starting to collect statistics');\n --Now we need to capture the statistics before they are updated, this can be reverted back if necessary\n select count(*) into v_count from dba_objects where object_name='CAD_STATS_TABLE';\n IF v_count = 0 THEN\n \n v_str := 'begin dbms_stats.CREATE_STAT_TABLE( ownname=>user, stattab=>''CAD_STATS_TABLE''); end;';\n if debugme = 'true' then\n dbms_output.put_line(v_str);\n else\n execute immediate v_str;\n end if;\n END IF;\n\n --Take a copy of the statistics (this is important, otherwise you have nothing to go back to\n v_str := 'begin dbms_stats.export_schema_stats( ownname=>user, stattab=>''CAD_STATS_TABLE'', statid=>''CURRENT_STATS''); end;';\n if debugme = 'true' then\n dbms_output.put_line(v_str);\n else\n execute immediate v_str;\n end if;\n\n \n \n FOR stalestats in (SELECT table_name FROM dba_tab_statistics WHERE stale_stats = 'YES' and STATTYPE_LOCKED='ALL' AND owner='CAD')\n LOOP\n utl_file.put (fhandle, 'Unlock Statistics' || chr(10));\n v_str := 'begin dbms_stats.unlock_table_stats(''CAD'',''' || stalestats.table_name || ''' ); end;';\n if debugme = 'true' then\n --unlock the table statistics\n dbms_output.put_line (v_str);\n else\n execute immediate v_str;\n end if;\n END LOOP;\n\n --gather the table statistics across the schema, this is more efficient\n utl_file.put (fhandle, 'Gather Statistics' || chr(10));\n v_str := 'begin dbms_stats.gather_schema_stats(ownname=>''CAD'',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4); end;';\n if debugme = 'true' then\n dbms_output.put_line(v_str);\n else\n execute immediate v_str;\n end if;\n\n --Finally need to lock the statistics for another time\n utl_file.put (fhandle, 'Lock Statistics' || chr(10));\n FOR lock_stats in (SELECT table_name FROM dba_tab_statistics WHERE owner='CAD')\n LOOP \n v_str := 'begin dbms_stats.lock_table_stats(''CAD'',''' || lock_stats.table_name || '''); end;';\n IF debugme = 'true' THEN\n --lock the table statistics\n dbms_output.put_line(v_str);\n ELSE\n EXECUTE IMMEDIATE v_str;\n END IF;\nEND LOOP;\n\n\ndbms_output.put_line('Closing open file');\nutl_file.put (fhandle, 'Completed - ' || (dbms_utility.get_time - t1)\/100 || ' seconds' || chr(13));\nutl_file.fclose(fhandle);\n\n\nEXCEPTION\n WHEN no_data_found THEN\n --utl_file.fclose(fhandle);\n NULL;\n WHEN OTHERS THEN\n raise_application_error(-20001, 'An error was encountered - ' || sqlcode || ' -ERROR- ' || sqlerrm);\n utl_file.fclose(fhandle); \nEND;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[70],"class_list":["post-354","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-procedure"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/354","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=354"}],"version-history":[{"count":2,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/354\/revisions"}],"predecessor-version":[{"id":356,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/354\/revisions\/356"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=354"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=354"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=354"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}