Thursday, February 14, 2013

Job Analyze Temp tables


CREATE OR REPLACE PROCEDURE Analyzetemp AS
BEGIN
 FOR CUR_REC IN (SELECT DISTINCT OWNER,TABLE_NAME
                  FROM   DBA_TABLES where table_name like '%TEMP%') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE TABLE '  || CUR_REC.OWNER || '.' || CUR_REC.TABLE_NAME ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
 FOR CUR_REC IN (SELECT DISTINCT OWNER,INDEX_NAME
                  FROM   DBA_INDEXES where table_name like '%TEMP%') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE INDEX ' || CUR_REC.OWNER || '.' || CUR_REC.INDEX_NAME ||' COMPUTE STATISTICS' ;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


No comments:

Post a Comment

Followers