jonasgiehl

NOTE:
Oracle9i:	estimate_percent defaults to NULL which is COMPUTE
Oracle10g:	estimate_percent defaults to to_estimate_percent_type (get_param('ESTIMATE_PERCENT'))

-- ********************************
-- * Generating Schema Statistics *
-- ********************************
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree)
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO')
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1') -- No Histograms

jonasgiehl

SET NEWPAGE 0 PAGESIZE 0 FEEDBACK OFF HEADING OFF

spool  analyze_rebuild_index.sql

SELECT 'ALTER TABLE '||TABLE_NAME|| ' INITRANS 30;' FROM USER_TABLES;
SELECT 'ALTER INDEX '||INDEX_NAME|| ' INITRANS 50;' FROM USER_INDEXES;
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE INDX ;' FROM USER_INDEXES;
SELECT 'ALTER INDEX '||INDEX_NAME|| ' REBUILD;' FROM USER_INDEXES;
SELECT 'ANALYZE TABLE '||TABLE_NAME ||' COMPUTE STATISTICS;' FROM USER_TABLES;