PACKAGE BODY dbms_advisor IS PROCEDURE CANCEL_TASK (TASK_NAME IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CANCEL_TASK(TASK_NAME); RETURN; END CANCEL_TASK; PROCEDURE CREATE_TASK (ADVISOR_NAME IN VARCHAR2, TASK_NAME IN VARCHAR2, TASK_DESC IN VARCHAR2, TEMPLATE IN VARCHAR2, IS_TEMPLATE IN VARCHAR2, HOW_CREATED IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME, ADVISOR_NAME, TRUE); PRVT_ADVISOR.CREATE_TASK (ADVISOR_NAME, TASK_NAME, TASK_DESC, TEMPLATE, IS_TEMPLATE, PRVT_ADVISOR.TASK_PROP_TASK, HOW_CREATED); RETURN; END CREATE_TASK; PROCEDURE CREATE_TASK (ADVISOR_NAME IN VARCHAR2, TASK_ID OUT NUMBER, TASK_NAME IN OUT VARCHAR2, TASK_DESC IN VARCHAR2, TEMPLATE IN VARCHAR2, IS_TEMPLATE IN VARCHAR2, HOW_CREATED IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME, ADVISOR_NAME, TRUE); PRVT_ADVISOR.CREATE_TASK (ADVISOR_NAME, TASK_ID, TASK_NAME, TASK_DESC, TEMPLATE, IS_TEMPLATE, PRVT_ADVISOR.TASK_PROP_TASK, HOW_CREATED); RETURN; END CREATE_TASK; PROCEDURE CREATE_TASK (PARENT_TASK_NAME IN VARCHAR2, REC_ID IN NUMBER, TASK_ID OUT NUMBER, TASK_NAME IN OUT VARCHAR2, TASK_DESC IN VARCHAR2, TEMPLATE IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CHECK_TASK_ENABLED(PARENT_TASK_NAME); PRVT_ADVISOR.CREATE_TASK (PARENT_TASK_NAME, REC_ID, TASK_ID, TASK_NAME, TASK_DESC, TEMPLATE, PRVT_ADVISOR.TASK_PROP_TASK); RETURN; END CREATE_TASK; PROCEDURE DELETE_TASK (TASK_NAME IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.DELETE_TASK(TASK_NAME, PRVT_ADVISOR.TASK_PROP_TASK); RETURN; END DELETE_TASK; PROCEDURE EXECUTE_TASK(TASK_NAME IN VARCHAR2) IS EXEC_NAME VARCHAR2(30); BEGIN PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME); EXEC_NAME := EXECUTE_TASK(TASK_NAME); EXCEPTION WHEN OTHERS THEN RAISE; END EXECUTE_TASK; FUNCTION EXECUTE_TASK( TASK_NAME IN VARCHAR2, EXECUTION_TYPE IN VARCHAR2 := NULL, EXECUTION_NAME IN VARCHAR2 := NULL, EXECUTION_DESC IN VARCHAR2 := NULL, EXECUTION_PARAMS IN ARGLIST := NULL) RETURN VARCHAR2 IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME); RETURN PRVT_ADVISOR.COMMON_EXECUTE_TASK(TASK_NAME, PRVT_ADVISOR.TASK_EXECUTE, EXECUTION_TYPE, EXECUTION_NAME, EXECUTION_DESC, EXECUTION_PARAMS); END EXECUTE_TASK; PROCEDURE INTERRUPT_TASK (TASK_NAME IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.INTERRUPT_TASK(TASK_NAME); RETURN; END INTERRUPT_TASK; PROCEDURE MARK_RECOMMENDATION (TASK_NAME IN VARCHAR2, ID IN NUMBER, ACTION IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.MARK_RECOMMENDATION(TASK_NAME, ID, ACTION); RETURN; END MARK_RECOMMENDATION; PROCEDURE RESET_TASK (TASK_NAME IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.RESET_TASK(TASK_NAME, PRVT_ADVISOR.TASK_PROP_TASK); RETURN; END RESET_TASK; PROCEDURE RESUME_TASK(TASK_NAME IN VARCHAR2) IS EXEC_NAME VARCHAR2(30); BEGIN CHECK_PRIVS; PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME); EXEC_NAME := PRVT_ADVISOR.COMMON_EXECUTE_TASK(TASK_NAME, PRVT_ADVISOR.TASK_RESUME); END RESUME_TASK; PROCEDURE SET_TASK_PARAMETER (TASK_NAME IN VARCHAR2, PARAMETER IN VARCHAR2, VALUE IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, PARAMETER, VALUE, PRVT_ADVISOR.TASK_PROP_TASK); RETURN; END SET_TASK_PARAMETER; PROCEDURE SET_TASK_PARAMETER (TASK_NAME IN VARCHAR2, PARAMETER IN VARCHAR2, VALUE IN NUMBER) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, PARAMETER, VALUE, PRVT_ADVISOR.TASK_PROP_TASK); RETURN; END SET_TASK_PARAMETER; PROCEDURE SET_DEFAULT_TASK_PARAMETER (ADVISOR_NAME IN VARCHAR2, PARAMETER IN VARCHAR2, VALUE IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.SET_DEFAULT_TASK_PARAMETER(ADVISOR_NAME, PARAMETER, VALUE); RETURN; END SET_DEFAULT_TASK_PARAMETER; PROCEDURE SET_DEFAULT_TASK_PARAMETER (ADVISOR_NAME IN VARCHAR2, PARAMETER IN VARCHAR2, VALUE IN NUMBER) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.SET_DEFAULT_TASK_PARAMETER(ADVISOR_NAME, PARAMETER, VALUE); RETURN; END SET_DEFAULT_TASK_PARAMETER; PROCEDURE CREATE_OBJECT(TASK_NAME IN VARCHAR2 , OBJECT_TYPE IN VARCHAR2 , ATTR1 IN VARCHAR2 := NULL, ATTR2 IN VARCHAR2 := NULL, ATTR3 IN VARCHAR2 := NULL, ATTR4 IN CLOB := NULL, ATTR5 IN VARCHAR2 := NULL, OBJECT_ID OUT NUMBER) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CREATE_OBJECT(TASK_NAME, OBJECT_TYPE, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5, OBJECT_ID, NULL, NULL, NULL); RETURN; END CREATE_OBJECT; PROCEDURE CREATE_OBJECT(TASK_NAME IN VARCHAR2 , OBJECT_TYPE IN VARCHAR2 , ATTR1 IN VARCHAR2 := NULL, ATTR2 IN VARCHAR2 := NULL, ATTR3 IN VARCHAR2 := NULL, ATTR4 IN CLOB := NULL, OBJECT_ID OUT NUMBER) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CREATE_OBJECT(TASK_NAME, OBJECT_TYPE, ATTR1, ATTR2, ATTR3, ATTR4, NULL, OBJECT_ID, NULL, NULL, NULL); RETURN; END CREATE_OBJECT; PROCEDURE UPDATE_OBJECT(TASK_NAME IN VARCHAR2 , OBJECT_ID IN NUMBER , ATTR1 IN VARCHAR2 := NULL, ATTR2 IN VARCHAR2 := NULL, ATTR3 IN VARCHAR2 := NULL, ATTR4 IN CLOB := NULL, ATTR5 IN VARCHAR2 := NULL) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.UPDATE_OBJECT(TASK_NAME, OBJECT_ID, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5); RETURN; END UPDATE_OBJECT; PROCEDURE CREATE_FILE (BUFFER IN CLOB, LOCATION IN VARCHAR2, FILENAME IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CREATE_FILE (BUFFER, LOCATION, FILENAME); RETURN; END CREATE_FILE; FUNCTION GET_TASK_REPORT (TASK_NAME IN VARCHAR2, TYPE IN VARCHAR2, LEVEL IN VARCHAR2, SECTION IN VARCHAR2, OWNER_NAME IN VARCHAR2 := NULL, EXECUTION_NAME IN VARCHAR2 := NULL, OBJECT_ID IN NUMBER := NULL) RETURN CLOB IS BEGIN CHECK_READ_PRIVS(OWNER_NAME); PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME); RETURN PRVT_ADVISOR.GET_TASK_REPORT(TASK_NAME, TYPE, LEVEL, SECTION, OWNER_NAME, EXECUTION_NAME, OBJECT_ID); END GET_TASK_REPORT; FUNCTION GET_TASK_SCRIPT (TASK_NAME IN VARCHAR2, TYPE IN VARCHAR2, REC_ID IN NUMBER, ACT_ID IN NUMBER, OWNER_NAME IN VARCHAR2 := NULL, EXECUTION_NAME IN VARCHAR2 := NULL, OBJECT_ID IN NUMBER := NULL) RETURN CLOB IS BEGIN CHECK_PRIVS; CHECK_READ_PRIVS(OWNER_NAME); PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME); RETURN PRVT_ADVISOR.GET_TASK_SCRIPT(TASK_NAME, TYPE, REC_ID, ACT_ID, OWNER_NAME, EXECUTION_NAME, OBJECT_ID); END GET_TASK_SCRIPT; PROCEDURE IMPLEMENT_TASK (TASK_NAME IN VARCHAR2, REC_ID IN NUMBER, EXIT_ON_ERROR IN BOOLEAN) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME); PRVT_ADVISOR.IMPLEMENT_TASK(TASK_NAME,REC_ID,EXIT_ON_ERROR); RETURN; END IMPLEMENT_TASK; PROCEDURE QUICK_TUNE (ADVISOR_NAME IN VARCHAR2, TASK_NAME IN VARCHAR2, ATTR1 IN CLOB, ATTR2 IN VARCHAR2, ATTR3 IN NUMBER, TEMPLATE IN VARCHAR2, IMPLEMENT IN BOOLEAN, DESCRIPTION IN VARCHAR2) IS BEGIN CHECK_PRIVS; DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED(DBMS_MANAGEMENT_PACKS.TUNING_PACK); PRVT_ADVISOR.QUICK_TUNE(ADVISOR_NAME, TASK_NAME, ATTR1, ATTR2, ATTR3, TEMPLATE, IMPLEMENT, DESCRIPTION); RETURN; END QUICK_TUNE; PROCEDURE TUNE_MVIEW (TASK_NAME IN OUT VARCHAR2, MV_CREATE_STMT IN CLOB) IS BEGIN CHECK_PRIVS; PRVT_TUNE_MVIEW.TUNE_MVIEW(TASK_NAME, MV_CREATE_STMT); RETURN; END TUNE_MVIEW; PROCEDURE UPDATE_REC_ATTRIBUTES (TASK_NAME IN VARCHAR2, REC_ID IN NUMBER, ACTION_ID IN NUMBER, ATTRIBUTE_NAME IN VARCHAR2, VALUE IN VARCHAR2) IS BEGIN CHECK_PRIVS; DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED(DBMS_MANAGEMENT_PACKS.TUNING_PACK); PRVT_ADVISOR.UPDATE_REC_ATTRIBUTES (TASK_NAME, REC_ID, ACTION_ID, ATTRIBUTE_NAME, VALUE); RETURN; END UPDATE_REC_ATTRIBUTES; PROCEDURE GET_REC_ATTRIBUTES (TASK_NAME IN VARCHAR2, REC_ID IN NUMBER, ACTION_ID IN NUMBER, ATTRIBUTE_NAME IN VARCHAR2, VALUE OUT VARCHAR2, OWNER_NAME IN VARCHAR2) IS BEGIN CHECK_PRIVS; CHECK_READ_PRIVS(OWNER_NAME); DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED(DBMS_MANAGEMENT_PACKS.TUNING_PACK); PRVT_ADVISOR.GET_REC_ATTRIBUTES (TASK_NAME, REC_ID, ACTION_ID, ATTRIBUTE_NAME, VALUE, OWNER_NAME); RETURN; END GET_REC_ATTRIBUTES; PROCEDURE UPDATE_TASK_ATTRIBUTES (TASK_NAME IN VARCHAR2, NEW_NAME IN VARCHAR2, DESCRIPTION IN VARCHAR2, READ_ONLY IN VARCHAR2, IS_TEMPLATE IN VARCHAR2, HOW_CREATED IN VARCHAR2) IS BEGIN CHECK_PRIVS; PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME); PRVT_ADVISOR.UPDATE_TASK_ATTRIBUTES (TASK_NAME, NEW_NAME, DESCRIPTION, READ_ONLY, IS_TEMPLATE, HOW_CREATED, PRVT_ADVISOR.TASK_PROP_TASK); RETURN; END UPDATE_TASK_ATTRIBUTES; FUNCTION FORMAT_MESSAGE_GROUP(GROUP_ID IN NUMBER, MSG_TYPE IN NUMBER := 0) RETURN VARCHAR2 IS STR VARCHAR2(4000); BEGIN STR := PRVT_ADVISOR.FORMAT_MESSAGE_GROUP(GROUP_ID, MSG_TYPE); RETURN STR; END FORMAT_MESSAGE_GROUP; FUNCTION FORMAT_MESSAGE(MSG_ID IN VARCHAR2) RETURN VARCHAR2 IS BEGIN IF MSG_ID IS NULL OR MSG_ID = '' THEN RETURN (''); END IF; RETURN PRVT_ADVISOR.FORMAT_MESSAGE(0, MSG_ID); END FORMAT_MESSAGE; PROCEDURE CHECK_PRIVS IS PRIV_CNT INTEGER; L_USER VARCHAR2(30); BEGIN IF (NOT DBMS_SQLTUNE_UTIL2.CHECK_PRIV('ADVISOR')) THEN SELECT USER INTO L_USER FROM DUAL; DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(PRVT_ADVISOR.NO_PRIVS, L_USER); END IF; END CHECK_PRIVS; PROCEDURE CHECK_READ_PRIVS(OWNER_NAME VARCHAR2) IS PRIV_CNT INTEGER := 1; BEGIN IF (OWNER_NAME IS NOT NULL AND OWNER_NAME <> SYS_CONTEXT('USERENV', 'CURRENT_USER')) THEN SELECT COUNT(*) INTO PRIV_CNT FROM SYS.DBA_ADVISOR_TASKS WHERE OWNER = OWNER_NAME AND ROWNUM = 1; END IF; EXCEPTION WHEN OTHERS THEN DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(PRVT_ADVISOR.NO_READ_PRIVS); END CHECK_READ_PRIVS; FUNCTION ACCESS_ADV_LOOKUP_STS_ID(STS_OWNER IN OUT VARCHAR2, STS_NAME IN VARCHAR2) RETURN BINARY_INTEGER IS CUR_USER VARCHAR2(30) := SYS_CONTEXT('USERENV', 'SESSION_USER'); ERR_NO_OWNER CONSTANT NUMBER := -13751; TYPE CURTYPE IS REF CURSOR; L_STS_NAME VARCHAR2(30); STS_CUR CURTYPE; L_ID BINARY_INTEGER; BEGIN IF STS_OWNER IS NULL OR LENGTH(STS_OWNER) = 0 THEN STS_OWNER := CUR_USER; END IF; L_STS_NAME := TRIM(BOTH '"' FROM STS_NAME); OPEN STS_CUR FOR 'SELECT id' || ' FROM all_sqlset' || ' WHERE name = :param1' || ' AND owner = :param2' USING L_STS_NAME, STS_OWNER; L_ID := 0; LOOP FETCH STS_CUR INTO L_ID; EXIT WHEN STS_CUR%NOTFOUND; END LOOP; CLOSE STS_CUR; IF L_ID = 0 THEN DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(ERR_NO_OWNER, L_STS_NAME, STS_OWNER, CUR_USER); END IF; RETURN (L_ID); END ACCESS_ADV_LOOKUP_STS_ID; PROCEDURE SETUP_REPOSITORY IS L_NAME VARCHAR2(30); L_VALUE VARCHAR2(4000); L_HIDE NUMBER; CURSOR HDM_CUR IS SELECT NAME, VALUE, DECODE(BITAND(FLAGS, 1), 1, 0, 1) HIDE FROM X$KEHRP WHERE INDX > 0; BEGIN CHECK_PRIVS; PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_DEFAULT, ADV_NAME_DEFAULT,0, WRI$_ADV_ABSTRACT_T(ADV_ID_DEFAULT)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_ADDM, ADV_NAME_ADDM,1, WRI$_ADV_HDM_T(ADV_ID_ADDM)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_SQLACCESS, ADV_NAME_SQLACCESS, PRVT_ADVISOR.ADV_PROP_COMPREHENSIVE + PRVT_ADVISOR.ADV_PROP_LIMITED + PRVT_ADVISOR.ADV_PROP_RESUMABLE + PRVT_ADVISOR.ADV_PROP_DIRECTIVE + PRVT_ADVISOR.ADV_PROP_GATHER_STATS, WRI$_ADV_SQLACCESS_ADV(ADV_ID_SQLACCESS)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_UNDO, ADV_NAME_UNDO,1, WRI$_ADV_UNDO_ADV(ADV_ID_UNDO)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_SQLTUNE, ADV_NAME_SQLTUNE, PRVT_ADVISOR.ADV_PROP_COMPREHENSIVE + PRVT_ADVISOR.ADV_PROP_LIMITED + PRVT_ADVISOR.ADV_PROP_RESUMABLE + PRVT_ADVISOR.ADV_PROP_MULTI_EXEC + PRVT_ADVISOR.ADV_PROP_RESUME_FATALER + PRVT_ADVISOR.ADV_PROP_GATHER_STATS + PRVT_ADVISOR.ADV_PROP_MANUAL_REPT_FUSG, WRI$_ADV_SQLTUNE(ADV_ID_SQLTUNE)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_SEGMENT, ADV_NAME_SEGMENT, PRVT_ADVISOR.ADV_PROP_COMPREHENSIVE + PRVT_ADVISOR.ADV_PROP_LIMITED + PRVT_ADVISOR.ADV_PROP_NO_USAGE_TRACK, WRI$_ADV_OBJSPACE_TREND_T(ADV_ID_SEGMENT)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_SQLWM, ADV_NAME_SQLWM, 0, WRI$_ADV_WORKLOAD(ADV_ID_SQLWM)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_TUNEMV, ADV_NAME_TUNEMV, 31, WRI$_ADV_TUNEMVIEW_ADV(ADV_ID_TUNEMV)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_SQLPA, ADV_NAME_SQLPA, PRVT_ADVISOR.ADV_PROP_COMPREHENSIVE + PRVT_ADVISOR.ADV_PROP_LIMITED + PRVT_ADVISOR.ADV_PROP_RESUMABLE + PRVT_ADVISOR.ADV_PROP_MULTI_EXEC + PRVT_ADVISOR.ADV_PROP_RESUME_FATALER + PRVT_ADVISOR.ADV_PROP_GATHER_STATS + PRVT_ADVISOR.ADV_PROP_MANUAL_REPT_FUSG, WRI$_ADV_SQLPI(ADV_ID_SQLPA)); PRVT_ADVISOR.INSERT_ADV_DEFINITION (ADV_ID_SQLREPAIR, ADV_NAME_SQLREPAIR, PRVT_ADVISOR.ADV_PROP_COMPREHENSIVE + PRVT_ADVISOR.ADV_PROP_LIMITED + PRVT_ADVISOR.ADV_PROP_RESUMABLE + PRVT_ADVISOR.ADV_PROP_MULTI_EXEC + PRVT_ADVISOR.ADV_PROP_RESUME_FATALER + PRVT_ADVISOR.ADV_PROP_
