dbms_addm 内容

时间：2010-09-20 来源：brjl

一些dbms_XXXX包的内容打包

dbms_advisor.sql               dbms_alert.sql
dbms_auto_task.sql             dbms_backup_restore.sql
dbms_crypto.sql                dbms_datapump.sql
dbms_job.sql                   dbms_lob.sql
dbms_lock.sql                  dbms_logmnr.sql
DBMS_LOGMNR_D.sql              DBMS_METADATA.sql
dbms_output.sql                dbms_rcvman.sql
dbms_redefinition.sql          dbms_refresh.sql
dbms_repair.sql                dbms_repcat.sql
DBMS_RESULT_CACHE.sql          dbms_rowid.sql
DBMS_SCHEDULER.sql             DBMS_SESSION.sql
dbms_shared_pool.sql           DBMS_SNAPSHOT.sql
dbms_space .sql                DBMS_SPM.sql
dbms_spm_internal.sql          dbms_sql.sql
DBMS_SQLPA.sql                 dbms_stats.sql
DBMS_STATS_INTERNAL.sql        DBMS_STREAMS.sql
DBMS_SYSTEM.sql                DBMS_TRACE.sql
DBMS_TTS.sql                   dbms_workload_repository.sql

文件:	dbms_x_11g.rar
大小:	398KB
下载:	下载

oracle 11gr2 数据库 dbms_addm包内容

PACKAGE BODY dbms_addm
IS

PROCEDURE VALIDATE_TASK(TASK_NAME_IN IN OUT VARCHAR2)
IS
  TID NUMBER;
  STATUS_T VARCHAR2(11);

BEGIN
  SELECT MAX(STATUS) INTO STATUS_T
  FROM USER_ADVISOR_TASKS
  WHERE ADVISOR_NAME = 'ADDM' AND
         TASK_NAME = TASK_NAME_IN;


  IF STATUS_T IS NULL THEN
    DBMS_ADVISOR.CREATE_TASK('ADDM', TID, TASK_NAME_IN);
    RETURN;

  ELSIF STATUS_T = 'COMPLETED' OR STATUS_T = 'EXECUTING' THEN
    RETURN;


  ELSIF STATUS_T <> 'INITIAL' THEN
    DBMS_ADVISOR.RESET_TASK(TASK_NAME_IN);
  END IF;
END;

PROCEDURE ANALYZE_DB ( TASK_NAME IN OUT VARCHAR2,
                       BEGIN_SNAPSHOT IN NUMBER,
                       END_SNAPSHOT IN NUMBER,
                       DB_ID IN NUMBER := NULL)
IS

BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');
  VALIDATE_TASK(TASK_NAME);
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'START_SNAPSHOT', BEGIN_SNAPSHOT);
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'END_SNAPSHOT', END_SNAPSHOT);
  IF DB_ID IS NOT NULL THEN
    DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'DB_ID', DB_ID);
  END IF;
  DBMS_ADVISOR.EXECUTE_TASK(TASK_NAME);
END;

PROCEDURE ANALYZE_INST ( TASK_NAME IN OUT VARCHAR2,
                         BEGIN_SNAPSHOT IN NUMBER,
                         END_SNAPSHOT IN NUMBER,
                         INSTANCE_NUMBER IN NUMBER := NULL,
                         DB_ID IN NUMBER := NULL)
IS
  INST_ID NUMBER;
  DBID NUMBER;

BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');

  VALIDATE_TASK(TASK_NAME);
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'START_SNAPSHOT', BEGIN_SNAPSHOT);
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'END_SNAPSHOT', END_SNAPSHOT);

  INST_ID := INSTANCE_NUMBER;
  DBID := DB_ID;

  IF DB_ID IS NOT NULL THEN
    DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'DB_ID', DBID);
  END IF;

  IF INST_ID IS NULL THEN
    SELECT DBMS_UTILITY.CURRENT_INSTANCE INTO INST_ID FROM DUAL;
  END IF;
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'INSTANCE', INST_ID);

  DBMS_ADVISOR.EXECUTE_TASK(TASK_NAME);
END;

PROCEDURE ANALYZE_PARTIAL ( TASK_NAME IN OUT VARCHAR2,
                            INSTANCE_NUMBERS IN VARCHAR2,
                            BEGIN_SNAPSHOT IN NUMBER,
                            END_SNAPSHOT IN NUMBER,
                            DB_ID IN NUMBER := NULL)
IS

BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');

  VALIDATE_TASK(TASK_NAME);
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'INSTANCES', INSTANCE_NUMBERS);
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'START_SNAPSHOT', BEGIN_SNAPSHOT);
  DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'END_SNAPSHOT', END_SNAPSHOT);
  IF DB_ID IS NOT NULL THEN
    DBMS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, 'DB_ID', DB_ID);
  END IF;
  DBMS_ADVISOR.EXECUTE_TASK(TASK_NAME);
END;

PROCEDURE INSERT_FINDING_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                     DIR_NAME IN VARCHAR2,
                                     FINDING_NAME IN VARCHAR2,
                                     MIN_ACTIVE_SESSIONS IN NUMBER := 0,
                                     MIN_PERC_IMPACT IN NUMBER := 0)
IS
  DIR_ID NUMBER;
  DIR_TEXT VARCHAR2(2000);
  CNT NUMBER;

  TASK_NAME_CPY VARCHAR2(100) := TASK_NAME;

BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');
  IF MIN_ACTIVE_SESSIONS IS NULL OR MIN_ACTIVE_SESSIONS < 0 THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13704,
                                      NVL(TO_CHAR(MIN_ACTIVE_SESSIONS),'NULL'),
                                      'MIN_ACTIVE_SESSION');
  END IF;
  IF MIN_PERC_IMPACT IS NULL OR MIN_PERC_IMPACT < 0 OR MIN_PERC_IMPACT >100 THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13704,
                                      NVL(TO_CHAR(MIN_PERC_IMPACT),'NULL'),
                                      'MIN_PERC_IMPACT');
  END IF;


  IF NOT PRVT_ADVISOR.FINDING_NAME_EXISTS('ADDM',FINDING_NAME) THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13713, FINDING_NAME);
  END IF;


  IF TASK_NAME IS NOT NULL THEN
    VALIDATE_TASK(TASK_NAME_CPY);
  END IF;

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'Finding Filter');

  DIR_TEXT :=
    '<parameter_list> ' ||
    ' <parameter name="FINDING_NAME"> ' ||
    ' <value>' || FINDING_NAME || '</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="ACTIVE_SESSIONS"> ' ||
    ' <value>' || MIN_ACTIVE_SESSIONS || '</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="PERC_ACTIVE_SESSIONS"> ' ||
    ' <value>' || MIN_PERC_IMPACT || '</value> ' ||
    ' </parameter> ' ||
    '</parameter_list>';

  DBMS_ADVISOR.INSERT_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME, DIR_TEXT);
END;

PROCEDURE INSERT_SQL_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                 DIR_NAME IN VARCHAR2,
                                 SQL_ID IN VARCHAR2,
                                 MIN_ACTIVE_SESSIONS IN NUMBER := 0,
                                 MIN_RESPONSE_TIME IN NUMBER := 0)
IS
  DIR_ID NUMBER;
  DIR_TEXT VARCHAR2(2000);
  I NUMBER;
  C VARCHAR2(1);

  TASK_NAME_CPY VARCHAR2(100) := TASK_NAME;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');

  IF SQL_ID IS NULL THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13704, 'NULL',
                                      'SQL_ID');
  END IF;
  IF MIN_ACTIVE_SESSIONS IS NULL OR MIN_ACTIVE_SESSIONS < 0 THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13704,
                                      NVL(TO_CHAR(MIN_ACTIVE_SESSIONS),'NULL'),
                                      'MIN_ACTIVE_SESSION');
  END IF;
  IF MIN_RESPONSE_TIME IS NULL OR MIN_RESPONSE_TIME < 0 THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13704,
                                      NVL(TO_CHAR(MIN_RESPONSE_TIME),'NULL'),
                                      'MIN_RESPONSE_TIME');
  END IF;

  IF LENGTH(SQL_ID) <> 13 THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13714, SQL_ID);
  END IF;
  FOR I IN 1..13 LOOP
    C := SUBSTR(SQL_ID, I, 1);
    IF NOT ((C >= '0' AND C <= '9') OR (C >= 'a' AND C <= 'z')) THEN
      DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13714, SQL_ID);
    END IF;
  END LOOP;


  IF TASK_NAME IS NOT NULL THEN
    VALIDATE_TASK(TASK_NAME_CPY);
  END IF;

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'SQL Filter');

  DIR_TEXT :=
    '<parameter_list> ' ||
    ' <parameter name="SQL_ID"> ' ||
    ' <value>' || SQL_ID || '</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="ACTIVE_SESSIONS"> ' ||
    ' <value>' || MIN_ACTIVE_SESSIONS || '</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="RESPONSE_TIME"> ' ||
    ' <value>' || MIN_RESPONSE_TIME || '</value> ' ||
    ' </parameter> ' ||
    '</parameter_list>';

  DBMS_ADVISOR.INSERT_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME, DIR_TEXT);
END;

PROCEDURE INSERT_SEGMENT_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                     DIR_NAME IN VARCHAR2,
                                     OWNER_NAME IN VARCHAR2,
                                     OBJECT_NAME IN VARCHAR2 := NULL,
                                     SUB_OBJECT_NAME IN VARCHAR2 := NULL)
IS
  DIR_ID NUMBER;
  DIR_TEXT VARCHAR2(2000);
  NAME1 VARCHAR2(100);
  NAME2 VARCHAR2(100);
  NAME3 VARCHAR2(100);

  TASK_NAME_CPY VARCHAR2(100) := TASK_NAME;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');
  IF OWNER_NAME IS NULL THEN
    NAME1 := '%';
  ELSE
    NAME1 := LOWER(OWNER_NAME);
  END IF;
  IF OBJECT_NAME IS NULL THEN
    NAME2 := '%';
  ELSE
    NAME2 := LOWER(OBJECT_NAME);
  END IF;
  IF SUB_OBJECT_NAME IS NULL THEN
    NAME3 := '%';
  ELSE
    NAME3 := LOWER(SUB_OBJECT_NAME);
  END IF;


  IF TASK_NAME IS NOT NULL THEN
    VALIDATE_TASK(TASK_NAME_CPY);
  END IF;

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'Segment Filter');

  DIR_TEXT :=
    '<parameter_list> ' ||
    ' <parameter name="OWNER_NAME"> ' ||
    ' <value>' || NAME1 || '</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="OBJECT_NAME"> ' ||
    ' <value>' || NAME2 || '</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="SUB_OBJECT_NAME"> ' ||
    ' <value>' || NAME3 || '</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="OBJECT_NUMBER"> ' ||
    ' <value>0</value> ' ||
    ' </parameter> ' ||
    '</parameter_list>';

  DBMS_ADVISOR.INSERT_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME, DIR_TEXT);
END;

PROCEDURE INSERT_SEGMENT_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                     DIR_NAME IN VARCHAR2,
                                     OBJECT_NUMBER IN NUMBER)

IS
  DIR_ID NUMBER;
  DIR_TEXT VARCHAR2(2000);

  TASK_NAME_CPY VARCHAR2(100) := TASK_NAME;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');


  IF TASK_NAME IS NOT NULL THEN
    VALIDATE_TASK(TASK_NAME_CPY);
  END IF;

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'Segment Filter');

  DIR_TEXT :=
    '<parameter_list> ' ||
    ' <parameter name="OWNER_NAME"> ' ||
    ' <value>0</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="OBJECT_NAME"> ' ||
    ' <value>0</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="SUB_OBJECT_NAME"> ' ||
    ' <value>0</value> ' ||
    ' </parameter> ' ||
    ' <parameter name="OBJECT_NUMBER"> ' ||
    ' <value>' || OBJECT_NUMBER || '</value> ' ||
    ' </parameter> ' ||
    '</parameter_list>';

  DBMS_ADVISOR.INSERT_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME, DIR_TEXT);
END;

PROCEDURE INSERT_PARAMETER_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                       DIR_NAME IN VARCHAR2,
                                       PARAMETER_NAME IN VARCHAR2)

IS
  DIR_ID NUMBER;
  DIR_TEXT VARCHAR2(2000);
  CNT NUMBER;
  PN VARCHAR2(300) := LOWER(PARAMETER_NAME);

  TASK_NAME_CPY VARCHAR2(100) := TASK_NAME;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');


  IF TASK_NAME IS NOT NULL THEN
    VALIDATE_TASK(TASK_NAME_CPY);
  END IF;

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'Parameter Filter');

  IF NOT PRVT_HDM.PARAMETER_EXISTS(PN) THEN
    DBMS_SYS_ERROR.RAISE_SYSTEM_ERROR(-13715, NVL(PARAMETER_NAME, 'NULL'));
  END IF;

  DIR_TEXT :=
    '<parameter_list> ' ||
    ' <parameter name="PARAMETER_NAME"> ' ||
    ' <value>' || PN || '</value> ' ||
    ' </parameter> ' ||
    '</parameter_list>';

  DBMS_ADVISOR.INSERT_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME, DIR_TEXT);
END;

PROCEDURE DELETE_FINDING_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                     DIR_NAME IN VARCHAR2)
IS
  DIR_ID NUMBER;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'Finding Filter');

  DBMS_ADVISOR.DELETE_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME);
END;

PROCEDURE DELETE_SQL_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                 DIR_NAME IN VARCHAR2)
IS
  DIR_ID NUMBER;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'SQL Filter');

  DBMS_ADVISOR.DELETE_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME);
END;

PROCEDURE DELETE_SEGMENT_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                     DIR_NAME IN VARCHAR2)
IS
  DIR_ID NUMBER;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'Segment Filter');

  DBMS_ADVISOR.DELETE_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME);
END;

PROCEDURE DELETE_PARAMETER_DIRECTIVE ( TASK_NAME IN VARCHAR2,
                                       DIR_NAME IN VARCHAR2)
IS
  DIR_ID NUMBER;
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');

  DIR_ID := PRVT_ADVISOR.GET_DIRECTIVE_ID('ADDM', 'Parameter Filter');

  DBMS_ADVISOR.DELETE_DIRECTIVE(DIR_ID, DIR_NAME, TASK_NAME);
END;

PROCEDURE DELETE ( TASK_NAME IN VARCHAR2)
IS
  CURSOR TNAMES(TN VARCHAR2) IS
  SELECT TASK_NAME
  FROM USER_ADVISOR_TASKS
  WHERE TASK_NAME = TN
    AND ADVISOR_NAME = 'ADDM';
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');
  FOR T IN TNAMES(TASK_NAME) LOOP
    DBMS_ADVISOR.DELETE_TASK(T.TASK_NAME);
  END LOOP;
END;

FUNCTION GET_REPORT ( TASK_NAME IN VARCHAR2)
RETURN CLOB
IS
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');
  RETURN DBMS_ADVISOR.GET_TASK_REPORT(TASK_NAME);
END;

FUNCTION GET_ASH_QUERY ( TASK_NAME IN VARCHAR2, FINDING_ID IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
  DBMS_MANAGEMENT_PACKS.CHECK_PACK_ENABLED('DIAGNOSTIC');
  DBMS_ADVISOR.CHECK_PRIVS;
  PRVT_ADVISOR.CHECK_TASK_ENABLED(TASK_NAME,
                                  DBMS_ADVISOR.ADV_NAME_ADDM,
                                  FALSE);
  RETURN PRVT_HDM.GET_ASH_QUERY(TASK_NAME, FINDING_ID);
END;

END DBMS_ADDM;