Showing posts from May, 2012


Flushing caches for testing: ALTER SYSTEM FLUSH BUFFER_CACHE; alter system flush shared_pool; Finding unusable, invalid, and disabled indexes: select owner, index_name from dba_indexes where status='UNUSABLE'; select index_name, status from dba_indexes where status = 'DISABLED'; select owner,table_name,index_name,last_analyzed,status from dba_indexes where status != 'VALID'; Rebuild index: alter index XXX.XXX rebuild;   BEGIN     FOR i IN       (SELECT index_name         FROM  dba_indexes          WHERE  status != 'VALID'                         AND  table_name=' ')     LOOP       EXECUTE immediate 'alter index ' || i.index_name || 'rebuild';     END LOOP;   END;   / Compute schema statistics: Auto is superior to estimate Use "cascade => TRUE" to also update all index stats on the table. EXEC DBMS_STATS.gather_table_stats(