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;
/
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment