Skip to content

Instantly share code, notes, and snippets.

@SathyaBhat

SathyaBhat/analyze_rebuild_index.sql

Created November 10, 2010 21:54
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save SathyaBhat/671602 to your computer and use it in GitHub Desktop.
Save SathyaBhat/671602 to your computer and use it in GitHub Desktop.
Download ZIP
Analyze & Compute Oracle Table Statistics & Rebuild & Move indexes to different tablespace
Raw
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment