Analyze & Compute Oracle Table Statistics & Rebuild & Move indexes to different tablespace

analyze_rebuild_index.sql

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;

spool off

@analyze_rebuild_index.sql

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => :USER_NAME,METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',DEGREE => 8,CASCADE => TRUE);

gistfile2.sql