CREATE OR REPLACE PACKAGE BODY QUEST_LUCY_COLLECTOR IS
TYPE TYP_ERROR IS RECORD
(
ERROR_TIME TIMESTAMP WITH TIME ZONE( 6 ),
ERROR_MESSAGE VARCHAR2( 4000 ),
ERROR_STACK VARCHAR2( 4000 ),
ERROR_BACKTRACE VARCHAR2( 4000 ),
CALL_STACK VARCHAR2( 4000 )
);
TYPE TYP_ERROR_ARR IS TABLE OF TYP_ERROR INDEX BY PLS_INTEGER;
TYPE TYP_REFRESH_TIMES IS TABLE OF NUMBER INDEX BY VARCHAR2( 30 );
TYPE TYP_DATABASE IS RECORD
(
DBID NUMBER,
DB_UNIQUE_NAME VARCHAR2( 30 ),
NAME VARCHAR2( 9 ),
PLATFORM_NAME VARCHAR2( 101 ),
OPEN_MODE VARCHAR2( 20 )
);
TYPE TYP_INSTANCE IS RECORD
(
INSTANCE_NUMBER NUMBER,
INSTANCE_NAME VARCHAR2( 16 ),
HOST_NAME VARCHAR2( 64 ),
VERSION VARCHAR2( 17 ),
STARTUP_TIME DATE,
STATUS VARCHAR2( 12 ),
THREAD# NUMBER,
INSTANCE_ROLE VARCHAR2( 18 )
);
TYPE TYP_ACTIVE_INSTANCE IS RECORD
(
INST_NUMBER NUMBER,
INST_NAME VARCHAR2( 240 )
);
TYPE TYP_ACTIVE_INSTANCE_ARR IS TABLE OF TYP_ACTIVE_INSTANCE INDEX BY PLS_INTEGER;
TYPE TYP_EVENT IS RECORD
(
EVENT_NAME VARCHAR2( 64 ),
TOTAL_WAITS NUMBER,
TOTAL_TIMEOUTS NUMBER,
TIME_WAITED_MICRO NUMBER
);
TYPE TYP_EVENT_ARR IS TABLE OF TYP_EVENT INDEX BY PLS_INTEGER;
TYPE TYP_DYNAMIC_COMPONENT IS RECORD
(
COMPONENT VARCHAR2( 64 ),
CURRENT_SIZE NUMBER
);
TYPE TYP_DYNAMIC_COMPONENT_ARR IS TABLE OF TYP_DYNAMIC_COMPONENT INDEX BY PLS_INTEGER;
TYPE TYP_IOSTAT_FILE IS RECORD
(
FILETYPE_NAME VARCHAR2( 28 ),
FILE_NAME VARCHAR2( 513 ),
TABLESPACE_NAME VARCHAR2( 30 ),
READ_MEGABYTES NUMBER,
WRITE_MEGABYTES NUMBER,
READ_REQS NUMBER,
WRITE_REQS NUMBER,
READ_SERVICETIME NUMBER,
WRITE_SERVICETIME NUMBER,
SMALL_READ_REQS NUMBER,
SMALL_READ_SERVICETIME NUMBER
);
TYPE TYP_IOSTAT_FILE_ARR IS TABLE OF TYP_IOSTAT_FILE INDEX BY PLS_INTEGER;
TYPE TYP_MEMORY_ADVISORY IS RECORD
(
ADVISORY_NAME VARCHAR2( 30 ),
POOL_NAME VARCHAR2( 20 ),
POOL_BLOCK_SIZE NUMBER,
MEMORY_SIZE_FACTOR NUMBER,
MEMORY_SIZE NUMBER,
ESTD_PHYSICAL_READS NUMBER,
ESTD_TIME NUMBER,
ESTD_OVERALLOC_COUNT NUMBER
);
TYPE TYP_MEMORY_ADVISORY_ARR IS TABLE OF TYP_MEMORY_ADVISORY INDEX BY PLS_INTEGER;
TYPE TYP_PARAMETER IS RECORD
(
NAME VARCHAR2( 80 ),
VALUE VARCHAR2( 4000 ),
ISDEFAULT VARCHAR2( 9 )
);
TYPE TYP_PARAMETER_ARR IS TABLE OF TYP_PARAMETER INDEX BY PLS_INTEGER;
TYPE TYP_STAT IS RECORD
(
STAT_NAME VARCHAR2( 64 ),
VALUE NUMBER
);
TYPE TYP_STAT_ARR IS TABLE OF TYP_STAT INDEX BY PLS_INTEGER;
TYPE TYP_ARCHIVE_DEST IS RECORD
(
DEST_ID NUMBER,
DEST_NAME VARCHAR2( 256 ),
STATUS VARCHAR2( 9 ),
BINDING VARCHAR2( 9 ),
TARGET VARCHAR2( 7 ),
ARCHIVER VARCHAR2( 10 ),
SCHEDULE VARCHAR2( 8 ),
DESTINATION VARCHAR2( 256 ),
DELAY_MINS NUMBER,
PROCESS VARCHAR2( 10 ),
TRANSMIT_MODE VARCHAR2( 12 ),
DB_UNIQUE_NAME VARCHAR2( 30 )
);
TYPE TYP_ARCHIVE_DEST_ARR IS TABLE OF TYP_ARCHIVE_DEST INDEX BY PLS_INTEGER;
TYPE TYP_LOGFILE IS RECORD
(
GROUP# NUMBER,
THREAD# NUMBER,
SEQUENCE# NUMBER,
BYTES NUMBER,
ARCHIVED VARCHAR2( 3 ),
GROUP_STATUS VARCHAR2( 16 ),
FIRST_CHANGE# NUMBER,
FIRST_TIME DATE,
MEMBER_NAME VARCHAR2( 513 ),
MEMBER_STATUS VARCHAR2( 7 )
);
TYPE TYP_LOGFILE_ARR IS TABLE OF TYP_LOGFILE INDEX BY PLS_INTEGER;
TYPE TYP_SNAPSHOT IS RECORD
(
SNAPSHOT_ID NUMBER,
SNAPSHOT_TIME TIMESTAMP WITH TIME ZONE( 6 ),
DATABASE TYP_DATABASE,
INSTANCE TYP_INSTANCE,
ACTIVE_INSTANCE TYP_ACTIVE_INSTANCE_ARR,
SYSTEM_EVENT TYP_EVENT_ARR,
DYNAMIC_COMPONENT TYP_DYNAMIC_COMPONENT_ARR,
IOSTAT_FILE TYP_IOSTAT_FILE_ARR,
燙燙燙燙?|???? MEMORY_ADVISORY TYP_MEMORY_ADVISORY_ARR,
PARAMETER TYP_PARAMETER_ARR,
OSSTAT TYP_STAT_ARR,
PGASTAT TYP_STAT_ARR,
SYS_TIME_MODEL TYP_STAT_ARR,
SYSSTAT TYP_STAT_ARR,
ARCHIVE_DEST TYP_ARCHIVE_DEST_ARR,
LOGFILE TYP_LOGFILE_ARR
);
TYPE TYP_SNAPSHOT_ARR IS TABLE OF TYP_SNAPSHOT INDEX BY PLS_INTEGER;
TYPE TYP_LUCY_SNAPSHOT_TBL IS RECORD
(
SNAPSHOT_ID NUMBER,
SNAPSHOT_TIME TIMESTAMP WITH TIME ZONE( 6 ),
SNAPSHOT_XML CLOB
);
TYPE TYP_LUCY_SNAPSHOT_TBL_ARR IS TABLE OF TYP_LUCY_SNAPSHOT_TBL INDEX BY PLS_INTEGER;
TYPE TYP_VARCHAR2_TABLE IS TABLE OF VARCHAR2( 4000 ) INDEX BY PLS_INTEGER;
TYPE TYP_SCHEDULER_JOB IS RECORD
(
JOB_NAME VARCHAR2( 30 ),
INSTANCE_ID NUMBER,
JOB_ACTION VARCHAR2( 4000 ),
JOB_TYPE VARCHAR2( 16 ),
REPEAT_INTERVAL VARCHAR2( 4000 ),
START_DATE TIMESTAMP WITH TIME ZONE( 6 ),
ENABLED VARCHAR2( 5 ),
LAST_START_DATE TIMESTAMP WITH TIME ZONE( 6 )
);
TYPE TYP_SCHEDULER_JOB_ARR IS TABLE OF TYP_SCHEDULER_JOB INDEX BY VARCHAR2( 30 );
TYPE TYP_DBMS_JOB IS RECORD
(
JOB NUMBER,
LAST_DATE DATE,
NEXT_DATE DATE,
BROKEN VARCHAR2( 1 ),
INTERVAL VARCHAR2( 200 ),
INSTANCE NUMBER
);
TYPE TYP_DBMS_JOB_ARR IS TABLE OF TYP_DBMS_JOB INDEX BY PLS_INTEGER;
FUNCTION GET_OWNER
RETURN VARCHAR2;
GC_PACKAGE_VERSION CONSTANT PLS_INTEGER := 1;
G_ERRORS TYP_ERROR_ARR;
G_SNAPSHOT TYP_SNAPSHOT;
G_REFRESH_TIMES TYP_REFRESH_TIMES;
G_TEMP_REFRESH_TIME TIMESTAMP WITH TIME ZONE( 6 );
G_ORACLE_VERSION NUMBER;
G_INSTANCE_LIST TYP_NUMBER_TABLE;
G_CURRENT_INSTANCE NUMBER;
G_SNAPSHOT_INSTANCE NUMBER;
G_SCHEDULER_JOB_LIST TYP_SCHEDULER_JOB_ARR;
G_SCHEDULER_JOB_LIST_INIT BOOLEAN;
G_DBMS_JOB_LIST TYP_DBMS_JOB_ARR;
G_DBMS_JOB_LIST_INIT BOOLEAN;
G_INDENT_LEVEL PLS_INTEGER;
G_OWNER VARCHAR2( 4000 );
G_USER VARCHAR2( 4000 );
G_GENERATE_SNAPSHOT_ID BOOLEAN;
GC_SPACE_PER_INDENT CONSTANT PLS_INTEGER := 2;
GC_XML_NAMESPACE CONSTANT VARCHAR2( 255 ) := '';
GC_JOB_PREFIX CONSTANT VARCHAR2( 30 ) := 'QUEST_LUCY_JOB';
GC_JOB_ACTION CONSTANT VARCHAR2( 4000 ) := 'BEGIN "' || GET_OWNER || '".quest_lucy_collector.take_snapshot; END;';
GC_REPEAT_MINUTES CONSTANT PLS_INTEGER := 60;
GC_SNAPSHOT_RETENTION_DAYS CONSTANT PLS_INTEGER := 14;
GC_REPEAT_INTERVAL CONSTANT VARCHAR2( 4000 ) := 'FREQ=MINUTELY;INTERVAL=' || TO_CHAR( GC_REPEAT_MINUTES );
GC_DBMS_JOB_INTERVAL CONSTANT VARCHAR2( 4000 ) := 'SYSDATE+(' || GC_REPEAT_MINUTES || ' / (24*60))';
GC_JOB_TYPE CONSTANT VARCHAR2( 16 ) := 'PLSQL_BLOCK';
FUNCTION GET_USER
RETURN VARCHAR2
IS
BEGIN
IF G_USER IS NULL THEN
EXECUTE IMMEDIATE 'SELECT SYS_CONTEXT (''USERENV'', ''SESSION_USER'') FROM dual'
INTO G_USER;
END IF;
RETURN G_USER;
END;
FUNCTION GET_OWNER
RETURN VARCHAR2
IS
L_CALL_STACK VARCHAR2( 4000 );
L_CALL_STACK_LINE VARCHAR2( 4000 );
L_ITERATOR NUMBER;
L_PACKAGE_NAME VARCHAR2( 4000 );
L_OWNER VARCHAR2( 4000 );
BEGIN
IF G_OWNER IS NULL THEN
BEGIN
EXECUTE IMMEDIATE 'BEGIN
:call_stack := DBMS_UTILITY.format_call_stack;
END;'
USING OUT L_CALL_STACK;
EXCEPTION
WHEN OTHERS THEN
L_CALL_STACK := NULL;
END;
IF L_CALL_STACK IS NOT NULL THEN
BEGIN
LOOP
L_ITERATOR := INSTR( L_CALL_STACK, CHR( 10 ) );
EXIT WHEN ( L_ITERATOR IS NULL OR L_ITERATOR = 0 OR L_OWNER IS NOT NULL );
L_CALL_STACK_LINE := SUBSTR( L_CALL_STACK, 1, L_ITERATOR - 1 );
L_CALL_STACK := SUBSTR( L_CALL_STACK, L_ITERATOR + 1 );
IF L_CALL_STACK_LINE LIKE '%package body%' THEN
燙燙燙燙?|???? L_PACKAGE_NAME := SUBSTR( L_CALL_STACK_LINE, INSTR( L_CALL_STACK_LINE, 'package body' ) + LENGTH( 'package body' ) + 1 );
L_OWNER := SUBSTR( L_PACKAGE_NAME, 1, LENGTH( L_PACKAGE_NAME ) - LENGTH( 'QUEST_LUCY_COLLECTOR' ) - 1 );
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
L_OWNER := NULL;
END;
END IF;
IF L_OWNER IS NULL THEN
L_OWNER := GET_USER;
END IF;
G_OWNER := L_OWNER;
END IF;
RETURN G_OWNER;
END;
PROCEDURE SAVE_EXCEPTION( I_ERROR_MESSAGE IN VARCHAR2 )
IS
L_ERROR TYP_ERROR;
L_ERROR_STACK VARCHAR2( 4000 );
L_ERROR_BACKTRACE VARCHAR2( 4000 );
L_CALL_STACK VARCHAR2( 4000 );
L_TEMP_ERROR_STACK VARCHAR2( 4000 );
BEGIN
L_TEMP_ERROR_STACK := SQLERRM;
BEGIN
EXECUTE IMMEDIATE 'BEGIN
:error_stack := DBMS_UTILITY.format_error_stack;
END;'
USING OUT L_ERROR_STACK;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_STACK := L_TEMP_ERROR_STACK;
END;
BEGIN
EXECUTE IMMEDIATE 'BEGIN
:error_backtrace := DBMS_UTILITY.format_error_backtrace;
END;'
USING OUT L_ERROR_BACKTRACE;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_BACKTRACE := NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'BEGIN
:call_stack := DBMS_UTILITY.format_call_stack;
END;'
USING OUT L_CALL_STACK;
EXCEPTION
WHEN OTHERS THEN
L_CALL_STACK := NULL;
END;
L_ERROR.ERROR_TIME := SYSTIMESTAMP;
L_ERROR.ERROR_MESSAGE := I_ERROR_MESSAGE;
L_ERROR.ERROR_STACK := L_ERROR_STACK;
L_ERROR.ERROR_BACKTRACE := L_ERROR_BACKTRACE;
L_ERROR.CALL_STACK := L_CALL_STACK;
G_ERRORS( G_ERRORS.COUNT + 1 ) := L_ERROR;
G_TEMP_REFRESH_TIME := NULL;
END;
PROCEDURE REMOVE_DBMS_JOB( I_JOB IN NUMBER )
IS
BEGIN
IF GET_OWNER = GET_USER THEN
EXECUTE IMMEDIATE 'BEGIN
DBMS_JOB.remove (:job);
END;'
USING IN I_JOB;
ELSE
EXECUTE IMMEDIATE 'BEGIN
SYS.DBMS_IJOB.remove (:job);
END;'
USING IN I_JOB;
END IF;
END;
PROCEDURE REMOVE_SCHEDULER_JOB( I_JOB_NAME IN VARCHAR2 )
IS
L_JOB_NAME VARCHAR2( 4000 );
BEGIN
L_JOB_NAME := '"' || GET_OWNER || '".' || I_JOB_NAME;
BEGIN
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.drop_job (:job_name);
END;'
USING IN L_JOB_NAME;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute_null (:job_name,
''REPEAT_INTERVAL'');
END;'
USING IN L_JOB_NAME;
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute (:job_name,
''AUTO_DROP'',
true);
END;'
USING IN L_JOB_NAME;
END;
END;
FUNCTION GET_ORACLE_VERSION
RETURN NUMBER
IS
L_BANNER VARCHAR2( 80 );
L_VERSION_TEXT VARCHAR2( 80 );
L_FORMAT_MASK VARCHAR2( 80 );
L_VERSION_MAJOR VARCHAR2( 80 );
L_VERSION_MINOR VARCHAR2( 80 );
BEGIN
IF G_ORACLE_VERSION IS NULL THEN
EXECUTE IMMEDIATE 'SELECT banner
FROM v$version
WHERE banner LIKE ''Oracle%'' OR banner LIKE ''Personal Oracle%'''
INTO L_BANNER;
L_VERSION_TEXT := SUBSTR( SUBSTR( L_BANNER, INSTR( L_BANNER, 'Release ' ) + 8 ), 1, INSTR( SUBSTR( L_BANNER, INSTR(燙燙燙燙?|???? L_BANNER, 'Release ' ) + 8 ), ' ' ) - 1 );
L_VERSION_MAJOR := SUBSTR( L_VERSION_TEXT, 1, INSTR( L_VERSION_TEXT, '.', 1, 1 ) - 1 );
L_VERSION_MINOR := SUBSTR( L_VERSION_TEXT, INSTR( L_VERSION_TEXT, '.', 1, 1 ) + 1, INSTR( L_VERSION_TEXT, '.', 1, 2 ) - INSTR( L_VERSION_TEXT, '.', 1, 1 ) - 1 );
L_FORMAT_MASK := RPAD( '9', LENGTH( L_VERSION_MAJOR ), '9' ) || '.' || RPAD( '9', LENGTH( L_VERSION_MINOR ), '9' );
G_ORACLE_VERSION := TO_NUMBER( L_VERSION_MAJOR || '.' || L_VERSION_MINOR, L_FORMAT_MASK );
END IF;
RETURN G_ORACLE_VERSION;
END;
FUNCTION GET_CURRENT_INSTANCE
RETURN NUMBER
IS
BEGIN
IF G_CURRENT_INSTANCE IS NULL THEN
EXECUTE IMMEDIATE 'SELECT USERENV (''INSTANCE'') FROM DUAL'
INTO G_CURRENT_INSTANCE;
END IF;
RETURN G_CURRENT_INSTANCE;
END;
FUNCTION GET_INSTANCE_LIST
RETURN TYP_NUMBER_TABLE
IS
L_TEMP_INSTANCE_LIST TYP_NUMBER_TABLE;
BEGIN
IF G_INSTANCE_LIST.COUNT = 0 THEN
EXECUTE IMMEDIATE 'SELECT inst_number FROM v$active_instances'
INTO L_TEMP_INSTANCE_LIST;
IF L_TEMP_INSTANCE_LIST.COUNT = 0 THEN
L_TEMP_INSTANCE_LIST( 1 ) := GET_CURRENT_INSTANCE;
END IF;
FOR I IN 1..L_TEMP_INSTANCE_LIST.COUNT
LOOP
G_INSTANCE_LIST( L_TEMP_INSTANCE_LIST( I ) ) := L_TEMP_INSTANCE_LIST( I );
END LOOP;
END IF;
RETURN G_INSTANCE_LIST;
END;
FUNCTION GET_SCHEDULER_JOB_LIST
RETURN TYP_SCHEDULER_JOB_ARR
IS
TYPE L_TYP_TEMP_SCHEDULER_JOB_ARR IS TABLE OF TYP_SCHEDULER_JOB INDEX BY PLS_INTEGER;
L_TEMP_SCHEDULER_JOB_LIST L_TYP_TEMP_SCHEDULER_JOB_ARR;
L_OWNER VARCHAR2( 4000 ) := GET_OWNER;
BEGIN
IF NOT G_SCHEDULER_JOB_LIST_INIT THEN
G_SCHEDULER_JOB_LIST.DELETE;
EXECUTE IMMEDIATE 'SELECT job_name,
instance_id,
job_action,
job_type,
repeat_interval,
start_date,
enabled,
last_start_date
FROM all_scheduler_jobs
WHERE owner = :owner
AND job_name LIKE :job_prefix || ''_%'''
INTO L_TEMP_SCHEDULER_JOB_LIST
USING IN L_OWNER, IN GC_JOB_PREFIX;
FOR I IN 1..L_TEMP_SCHEDULER_JOB_LIST.COUNT
LOOP
G_SCHEDULER_JOB_LIST( L_TEMP_SCHEDULER_JOB_LIST( I ).JOB_NAME ) := L_TEMP_SCHEDULER_JOB_LIST( I );
END LOOP;
G_SCHEDULER_JOB_LIST_INIT := TRUE;
END IF;
RETURN G_SCHEDULER_JOB_LIST;
END;
FUNCTION GET_DBMS_JOB_LIST
RETURN TYP_DBMS_JOB_ARR
IS
L_TEMP_DBMS_JOB_LIST TYP_DBMS_JOB_ARR;
L_OWNER VARCHAR2( 4000 ) := GET_OWNER;
L_USER VARCHAR2( 4000 ) := GET_USER;
BEGIN
IF NOT G_DBMS_JOB_LIST_INIT THEN
G_DBMS_JOB_LIST.DELETE;
IF L_OWNER = L_USER THEN
EXECUTE IMMEDIATE 'SELECT job,
last_date,
next_date,
broken,
interval,
instance
FROM user_jobs
WHERE what = :job_action'
INTO L_TEMP_DBMS_JOB_LIST
USING IN GC_JOB_ACTION;
ELSE
EXECUTE IMMEDIATE 'SELECT job,
last_date,
next_date,
broken,
interval,
instance
FROM dba_jobs
WHERE priv_user = :priv_user AND what = :job_action'
INTO L_TEMP_DBMS_JOB_LIST
USING IN L_OWNER, IN GC_JOB_ACTION;
END IF;
FOR I IN 1..L_TEMP_DBMS_JOB_LIST.COUNT
LOOP
IF NOT G_DBMS_JOB_LIST.EXISTS( L_TEMP_DBMS_JOB_LIST( I ).INSTANCE ) THEN
燙燙燙燙?|???? G_DBMS_JOB_LIST( L_TEMP_DBMS_JOB_LIST( I ).INSTANCE ) := L_TEMP_DBMS_JOB_LIST( I );
ELSE
REMOVE_DBMS_JOB( L_TEMP_DBMS_JOB_LIST( I ).JOB );
END IF;
END LOOP;
G_DBMS_JOB_LIST_INIT := TRUE;
END IF;
RETURN G_DBMS_JOB_LIST;
END;
FUNCTION GET_OLDEST_SNAPSHOT_TIME
RETURN TIMESTAMP WITH TIME ZONE
IS
L_SNAPSHOT_TIME TIMESTAMP WITH TIME ZONE( 6 );
BEGIN
EXECUTE IMMEDIATE 'SELECT MIN (snapshot_time)
FROM quest_lucy_snapshot'
INTO L_SNAPSHOT_TIME;
RETURN L_SNAPSHOT_TIME;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not retrieve oldest snapshot time' );
RETURN NULL;
END;
PROCEDURE BEGIN_REFRESH_TIMER
IS
BEGIN
IF G_TEMP_REFRESH_TIME IS NULL THEN
G_TEMP_REFRESH_TIME := SYSTIMESTAMP;
ELSE
RAISE_APPLICATION_ERROR( -20000, 'g_temp_refresh_time was not null' );
END IF;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not begin refresh timer' );
END;
PROCEDURE END_REFRESH_TIMER( I_TIMER_NAME IN VARCHAR2 )
IS
L_INTERVAL INTERVAL DAY TO SECOND( 6 );
BEGIN
IF G_TEMP_REFRESH_TIME IS NOT NULL THEN
L_INTERVAL := SYSTIMESTAMP - G_TEMP_REFRESH_TIME;
G_REFRESH_TIMES( I_TIMER_NAME ) := ROUND( ( ( EXTRACT( DAY FROM ( L_INTERVAL ) ) * 24 * 60 * 60 ) + ( EXTRACT( HOUR FROM ( L_INTERVAL ) ) * 60 * 60 ) + ( EXTRACT( MINUTE FROM ( L_INTERVAL ) ) * 60 ) + ( EXTRACT( SECOND FROM ( L_INTERVAL ) ) ) ) * 1000000, 0 );
G_TEMP_REFRESH_TIME := NULL;
ELSE
RAISE_APPLICATION_ERROR( -20000, 'g_temp_refresh_time was null' );
END IF;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not end refresh timer' );
END;
PROCEDURE WRITE_XML( I_XML IN VARCHAR2 )
IS
L_XML VARCHAR2( 32767 );
BEGIN
L_XML := I_XML || CHR( 10 );
EXECUTE IMMEDIATE 'BEGIN
DBMS_LOB.writeappend ("' || GET_OWNER || '".quest_lucy_collector.g_snapshot_xml,
LENGTH (:l_xml),
:l_xml);
END;'
USING IN L_XML;
END;
FUNCTION GET_INDENT
RETURN VARCHAR2
IS
BEGIN
RETURN RPAD( ' ', G_INDENT_LEVEL * GC_SPACE_PER_INDENT, ' ' );
END;
PROCEDURE WRITE_XML_BEGIN_TAG( I_ELEMENT_NAME IN VARCHAR2, I_INCLUDE_REFRESH_TIME IN BOOLEAN := FALSE )
IS
L_ELEMENT VARCHAR2( 32767 );
L_REFRESH_TIME NUMBER;
BEGIN
L_ELEMENT := GET_INDENT || ' IF I_INCLUDE_REFRESH_TIME THEN
BEGIN
L_REFRESH_TIME := G_REFRESH_TIMES( I_ELEMENT_NAME );
EXCEPTION
WHEN NO_DATA_FOUND THEN
L_REFRESH_TIME := 0;
END;
L_ELEMENT := L_ELEMENT || ' RefreshTimeMicro="' || TO_CHAR( L_REFRESH_TIME, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''' ) || '"';
END IF;
L_ELEMENT := L_ELEMENT || '>';
G_INDENT_LEVEL := G_INDENT_LEVEL + 1;
WRITE_XML( L_ELEMENT );
END;
PROCEDURE WRITE_XML_END_TAG( I_ELEMENT_NAME IN VARCHAR2 )
IS
L_ELEMENT VARCHAR2( 32767 );
BEGIN
G_INDENT_LEVEL := G_INDENT_LEVEL - 1;
L_ELEMENT := GET_INDENT || '' || I_ELEMENT_NAME || '>';
WRITE_XML( L_ELEMENT );
END;
PROCEDURE WRITE_XML_ELEMENT( I_ELEMENT_NAME IN VARCHAR2, I_ELEMENT_VALUE IN VARCHAR2 )
IS
L_ELEMENT VARCHAR2( 32767 );
L_ELEMENT_VALUE VARCHAR2( 32767 );
BEGIN
IF I_ELEMENT_NAME IS NOT NULL THEN
L_ELEMENT_VALUE := REPLACE( I_ELEMENT_VALUE, CHR( 38 ), CHR( 38 ) || 'amp;' );
L_ELEMENT_VALUE := REPLACE( L_ELEMENT_VALUE, ' L_ELEMENT := GET_INDENT || '';
L_ELEMENT := L_ELEMENT || L_ELEMENT_VALUE;
L_ELEMENT := L_ELEMENT || '' || I_ELEMENT_NAME || '>';
WRITE_XML( L_ELEMENT );燙燙燙燙?|????
END IF;
END;
PROCEDURE WRITE_XML_ELEMENT( I_ELEMENT_NAME IN VARCHAR2, I_ELEMENT_VALUE IN NUMBER )
IS
BEGIN
IF I_ELEMENT_VALUE IS NOT NULL THEN
WRITE_XML_ELEMENT( I_ELEMENT_NAME, TO_CHAR( I_ELEMENT_VALUE, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''' ) );
END IF;
END;
PROCEDURE WRITE_XML_ELEMENT( I_ELEMENT_NAME IN VARCHAR2, I_ELEMENT_VALUE IN TIMESTAMP WITH TIME ZONE )
IS
BEGIN
WRITE_XML_ELEMENT( I_ELEMENT_NAME, TO_CHAR( I_ELEMENT_VALUE, 'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM' ) );
END;
PROCEDURE WRITE_XML_ELEMENT( I_ELEMENT_NAME IN VARCHAR2, I_ELEMENT_VALUE IN DATE )
IS
BEGIN
WRITE_XML_ELEMENT( I_ELEMENT_NAME, TO_CHAR( I_ELEMENT_VALUE, 'YYYY-MM-DD"T"HH24:MI:SS' ) );
END;
PROCEDURE WRITE_XML_STATS( I_STATS_NAME IN VARCHAR2, STATS IN TYP_STAT_ARR )
IS
BEGIN
IF STATS.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( I_STATS_NAME ) THEN
WRITE_XML_BEGIN_TAG( I_STATS_NAME, TRUE );
FOR I IN 1..STATS.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'Stat' );
WRITE_XML_ELEMENT( 'StatName', STATS( I ).STAT_NAME );
WRITE_XML_ELEMENT( 'Value', STATS( I ).VALUE );
WRITE_XML_END_TAG( 'Stat' );
END LOOP;
WRITE_XML_END_TAG( I_STATS_NAME );
END IF;
END;
PROCEDURE WRITE_XML_ERRORS
IS
BEGIN
IF G_ERRORS.COUNT > 0 THEN
WRITE_XML_BEGIN_TAG( 'Errors' );
FOR I IN 1..G_ERRORS.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'Error' );
WRITE_XML_ELEMENT( 'ErrorTime', G_ERRORS( I ).ERROR_TIME );
WRITE_XML_ELEMENT( 'ErrorMessage', G_ERRORS( I ).ERROR_MESSAGE );
WRITE_XML_ELEMENT( 'ErrorStack', G_ERRORS( I ).ERROR_STACK );
WRITE_XML_ELEMENT( 'ErrorBacktrace', G_ERRORS( I ).ERROR_BACKTRACE );
WRITE_XML_ELEMENT( 'CallStack', G_ERRORS( I ).CALL_STACK );
WRITE_XML_END_TAG( 'Error' );
END LOOP;
WRITE_XML_END_TAG( 'Errors' );
G_ERRORS.DELETE;
END IF;
END;
PROCEDURE WRITE_XML_DATABASE
IS
BEGIN
IF G_SNAPSHOT.DATABASE.DBID IS NOT NULL THEN
WRITE_XML_BEGIN_TAG( 'DatabaseInformation', TRUE );
WRITE_XML_ELEMENT( 'Id', G_SNAPSHOT.DATABASE.DBID );
WRITE_XML_ELEMENT( 'UniqueName', G_SNAPSHOT.DATABASE.DB_UNIQUE_NAME );
WRITE_XML_ELEMENT( 'Name', G_SNAPSHOT.DATABASE.NAME );
WRITE_XML_ELEMENT( 'PlatformName', G_SNAPSHOT.DATABASE.PLATFORM_NAME );
WRITE_XML_ELEMENT( 'OpenMode', G_SNAPSHOT.DATABASE.OPEN_MODE );
WRITE_XML_END_TAG( 'DatabaseInformation' );
END IF;
END;
PROCEDURE WRITE_XML_INSTANCE
IS
BEGIN
IF G_SNAPSHOT.INSTANCE.INSTANCE_NUMBER IS NOT NULL THEN
WRITE_XML_BEGIN_TAG( 'InstanceInformation', TRUE );
WRITE_XML_ELEMENT( 'Id', G_SNAPSHOT.INSTANCE.INSTANCE_NUMBER );
WRITE_XML_ELEMENT( 'Name', G_SNAPSHOT.INSTANCE.INSTANCE_NAME );
WRITE_XML_ELEMENT( 'HostName', G_SNAPSHOT.INSTANCE.HOST_NAME );
WRITE_XML_ELEMENT( 'Version', G_SNAPSHOT.INSTANCE.VERSION );
WRITE_XML_ELEMENT( 'StartupTime', G_SNAPSHOT.INSTANCE.STARTUP_TIME );
WRITE_XML_ELEMENT( 'Status', G_SNAPSHOT.INSTANCE.STATUS );
WRITE_XML_ELEMENT( 'Thread', G_SNAPSHOT.INSTANCE.THREAD# );
WRITE_XML_ELEMENT( 'InstanceRole', G_SNAPSHOT.INSTANCE.INSTANCE_ROLE );
WRITE_XML_END_TAG( 'InstanceInformation' );
END IF;
END;
PROCEDURE WRITE_XML_ACTIVE_INSTANCE
IS
BEGIN
IF G_SNAPSHOT.ACTIVE_INSTANCE.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'ActiveInstances' ) THEN
WRITE_XML_BEGIN_TAG( 'ActiveInstances', TRUE );
FOR I IN 1..G_SNAPSHOT.ACTIVE_INSTANCE.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'ActiveInstance' );
WRITE_XML_ELEMENT( 'Number', G_SNAPSHOT.ACTIVE_INSTANCE( I ).INST_NUMBER );
WRITE_XML_ELEMENT( 'Name', G_SNAPSHOT.ACTIVE_INSTANCE( I ).INST_NAME );
燙燙燙燙?|???? WRITE_XML_END_TAG( 'ActiveInstance' );
END LOOP;
WRITE_XML_END_TAG( 'ActiveInstances' );
END IF;
END;
PROCEDURE WRITE_XML_SYSTEM_EVENTS
IS
BEGIN
IF G_SNAPSHOT.SYSTEM_EVENT.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'SystemEvents' ) THEN
WRITE_XML_BEGIN_TAG( 'SystemEvents', TRUE );
FOR I IN 1..G_SNAPSHOT.SYSTEM_EVENT.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'SystemEvent' );
WRITE_XML_ELEMENT( 'Name', G_SNAPSHOT.SYSTEM_EVENT( I ).EVENT_NAME );
WRITE_XML_ELEMENT( 'TotalWaits', G_SNAPSHOT.SYSTEM_EVENT( I ).TOTAL_WAITS );
WRITE_XML_ELEMENT( 'TotalTimeouts', G_SNAPSHOT.SYSTEM_EVENT( I ).TOTAL_TIMEOUTS );
WRITE_XML_ELEMENT( 'TimeWaited', G_SNAPSHOT.SYSTEM_EVENT( I ).TIME_WAITED_MICRO );
WRITE_XML_END_TAG( 'SystemEvent' );
END LOOP;
WRITE_XML_END_TAG( 'SystemEvents' );
END IF;
END;
PROCEDURE WRITE_XML_DYN_COMPONENTS
IS
BEGIN
IF G_SNAPSHOT.DYNAMIC_COMPONENT.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'DynamicComponents' ) THEN
WRITE_XML_BEGIN_TAG( 'DynamicComponents', TRUE );
FOR I IN 1..G_SNAPSHOT.DYNAMIC_COMPONENT.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'DynamicComponent' );
WRITE_XML_ELEMENT( 'Name', G_SNAPSHOT.DYNAMIC_COMPONENT( I ).COMPONENT );
WRITE_XML_ELEMENT( 'CurrentSize', G_SNAPSHOT.DYNAMIC_COMPONENT( I ).CURRENT_SIZE );
WRITE_XML_END_TAG( 'DynamicComponent' );
END LOOP;
WRITE_XML_END_TAG( 'DynamicComponents' );
END IF;
END;
PROCEDURE WRITE_XML_IOSTAT_FILE
IS
BEGIN
IF G_SNAPSHOT.IOSTAT_FILE.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'IostatFiles' ) THEN
WRITE_XML_BEGIN_TAG( 'IostatFiles', TRUE );
FOR I IN 1..G_SNAPSHOT.IOSTAT_FILE.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'IostatFile' );
WRITE_XML_ELEMENT( 'FiletypeName', G_SNAPSHOT.IOSTAT_FILE( I ).FILETYPE_NAME );
WRITE_XML_ELEMENT( 'FileName', G_SNAPSHOT.IOSTAT_FILE( I ).FILE_NAME );
WRITE_XML_ELEMENT( 'TablespaceName', G_SNAPSHOT.IOSTAT_FILE( I ).TABLESPACE_NAME );
WRITE_XML_ELEMENT( 'ReadMegabytes', G_SNAPSHOT.IOSTAT_FILE( I ).READ_MEGABYTES );
WRITE_XML_ELEMENT( 'WriteMegabytes', G_SNAPSHOT.IOSTAT_FILE( I ).WRITE_MEGABYTES );
WRITE_XML_ELEMENT( 'ReadReqs', G_SNAPSHOT.IOSTAT_FILE( I ).READ_REQS );
WRITE_XML_ELEMENT( 'WriteReqs', G_SNAPSHOT.IOSTAT_FILE( I ).WRITE_REQS );
WRITE_XML_ELEMENT( 'ReadServicetime', G_SNAPSHOT.IOSTAT_FILE( I ).READ_SERVICETIME );
WRITE_XML_ELEMENT( 'WriteServicetime', G_SNAPSHOT.IOSTAT_FILE( I ).WRITE_SERVICETIME );
WRITE_XML_ELEMENT( 'SmallReadReqs', G_SNAPSHOT.IOSTAT_FILE( I ).SMALL_READ_REQS );
WRITE_XML_ELEMENT( 'SmallReadServicetime', G_SNAPSHOT.IOSTAT_FILE( I ).SMALL_READ_SERVICETIME );
WRITE_XML_END_TAG( 'IostatFile' );
END LOOP;
WRITE_XML_END_TAG( 'IostatFiles' );
END IF;
END;
PROCEDURE WRITE_XML_MEMORY_ADVISORY
IS
BEGIN
IF G_SNAPSHOT.MEMORY_ADVISORY.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'MemoryAdvisories' ) THEN
WRITE_XML_BEGIN_TAG( 'MemoryAdvisories', TRUE );
FOR I IN 1..G_SNAPSHOT.MEMORY_ADVISORY.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'MemoryAdvisory' );
WRITE_XML_ELEMENT( 'AdvisoryName', G_SNAPSHOT.MEMORY_ADVISORY( I ).ADVISORY_NAME );
WRITE_XML_ELEMENT( 'PoolName', G_SNAPSHOT.MEMORY_ADVISORY( I ).POOL_NAME );
WRITE_XML_ELEMENT( 'PoolBlockSize', G_SNAPSHOT.MEMORY_ADVISORY( I ).POOL_BLOCK_SIZE );
WRITE_XML_ELEMENT( 'MemorySizeFactor', G_SNAPSHOT.MEMORY_ADVISORY( I ).MEMORY_SIZE_FACTOR );
WRITE_XML_ELEMENT( 'MemorySize', G_SNAPSHOT.MEMORY_ADVISORY( I ).MEMORY_SIZE );
WRITE_XML_ELEMENT( 'EstdPhysicalReads', G_SNAPSHOT.MEMORY_ADVISORY( I ).ESTD_PHYSICAL_READS );
燙燙燙燙?|???? WRITE_XML_ELEMENT( 'EstdTime', G_SNAPSHOT.MEMORY_ADVISORY( I ).ESTD_TIME );
WRITE_XML_ELEMENT( 'EstdOverallocCount', G_SNAPSHOT.MEMORY_ADVISORY( I ).ESTD_OVERALLOC_COUNT );
WRITE_XML_END_TAG( 'MemoryAdvisory' );
END LOOP;
WRITE_XML_END_TAG( 'MemoryAdvisories' );
END IF;
END;
PROCEDURE WRITE_XML_PARAMETER
IS
BEGIN
IF G_SNAPSHOT.PARAMETER.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'Parameters' ) THEN
WRITE_XML_BEGIN_TAG( 'Parameters', TRUE );
FOR I IN 1..G_SNAPSHOT.PARAMETER.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'Parameter' );
WRITE_XML_ELEMENT( 'Name', G_SNAPSHOT.PARAMETER( I ).NAME );
WRITE_XML_ELEMENT( 'Value', G_SNAPSHOT.PARAMETER( I ).VALUE );
WRITE_XML_ELEMENT( 'IsDefault', G_SNAPSHOT.PARAMETER( I ).ISDEFAULT );
WRITE_XML_END_TAG( 'Parameter' );
END LOOP;
WRITE_XML_END_TAG( 'Parameters' );
END IF;
END;
PROCEDURE WRITE_XML_ARCHIVE_DEST
IS
BEGIN
IF G_SNAPSHOT.ARCHIVE_DEST.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'ArchiveDests' ) THEN
WRITE_XML_BEGIN_TAG( 'ArchiveDests', TRUE );
FOR I IN 1..G_SNAPSHOT.ARCHIVE_DEST.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'ArchiveDest' );
WRITE_XML_ELEMENT( 'Id', G_SNAPSHOT.ARCHIVE_DEST( I ).DEST_ID );
WRITE_XML_ELEMENT( 'Name', G_SNAPSHOT.ARCHIVE_DEST( I ).DEST_NAME );
WRITE_XML_ELEMENT( 'Status', G_SNAPSHOT.ARCHIVE_DEST( I ).STATUS );
WRITE_XML_ELEMENT( 'Binding', G_SNAPSHOT.ARCHIVE_DEST( I ).BINDING );
WRITE_XML_ELEMENT( 'Target', G_SNAPSHOT.ARCHIVE_DEST( I ).TARGET );
WRITE_XML_ELEMENT( 'Archiver', G_SNAPSHOT.ARCHIVE_DEST( I ).ARCHIVER );
WRITE_XML_ELEMENT( 'Schedule', G_SNAPSHOT.ARCHIVE_DEST( I ).SCHEDULE );
WRITE_XML_ELEMENT( 'Destination', G_SNAPSHOT.ARCHIVE_DEST( I ).DESTINATION );
WRITE_XML_ELEMENT( 'DelayMins', G_SNAPSHOT.ARCHIVE_DEST( I ).DELAY_MINS );
WRITE_XML_ELEMENT( 'Process', G_SNAPSHOT.ARCHIVE_DEST( I ).PROCESS );
WRITE_XML_ELEMENT( 'TransmitMode', G_SNAPSHOT.ARCHIVE_DEST( I ).TRANSMIT_MODE );
WRITE_XML_ELEMENT( 'DbUniqueName', G_SNAPSHOT.ARCHIVE_DEST( I ).DB_UNIQUE_NAME );
WRITE_XML_END_TAG( 'ArchiveDest' );
END LOOP;
WRITE_XML_END_TAG( 'ArchiveDests' );
END IF;
END;
PROCEDURE WRITE_XML_LOGFILE
IS
BEGIN
IF G_SNAPSHOT.LOGFILE.COUNT > 0 OR G_REFRESH_TIMES.EXISTS( 'Logfiles' ) THEN
WRITE_XML_BEGIN_TAG( 'Logfiles', TRUE );
FOR I IN 1..G_SNAPSHOT.LOGFILE.COUNT
LOOP
WRITE_XML_BEGIN_TAG( 'Logfile' );
WRITE_XML_ELEMENT( 'Group', G_SNAPSHOT.LOGFILE( I ).GROUP# );
WRITE_XML_ELEMENT( 'Thread', G_SNAPSHOT.LOGFILE( I ).THREAD# );
WRITE_XML_ELEMENT( 'Sequence', G_SNAPSHOT.LOGFILE( I ).SEQUENCE# );
WRITE_XML_ELEMENT( 'Bytes', G_SNAPSHOT.LOGFILE( I ).BYTES );
WRITE_XML_ELEMENT( 'Archived', G_SNAPSHOT.LOGFILE( I ).ARCHIVED );
WRITE_XML_ELEMENT( 'GroupStatus', G_SNAPSHOT.LOGFILE( I ).GROUP_STATUS );
WRITE_XML_ELEMENT( 'FirstChange', G_SNAPSHOT.LOGFILE( I ).FIRST_CHANGE# );
WRITE_XML_ELEMENT( 'FirstTime', G_SNAPSHOT.LOGFILE( I ).FIRST_TIME );
WRITE_XML_ELEMENT( 'MemberName', G_SNAPSHOT.LOGFILE( I ).MEMBER_NAME );
WRITE_XML_ELEMENT( 'MemberStatus', G_SNAPSHOT.LOGFILE( I ).MEMBER_STATUS );
WRITE_XML_END_TAG( 'Logfile' );
END LOOP;
WRITE_XML_END_TAG( 'Logfiles' );
END IF;
END;
PROCEDURE WRITE_SNAPSHOT_XML
IS
BEGIN
WRITE_XML_BEGIN_TAG( 'OracleSnapshot' );
WRITE_XML_ELEMENT( 'Id', G_SNAPSHOT.SNAPSHOT_ID );
WRITE_XML_ELEMENT( 'Time', G_SNAPSHOT.SNAPSHOT_TIME );
WRITE_XML_ELEMENT( 'PackageVersion', GC_PACKAGE_VERSION );
WRITE_XML_DATABASE;
WRITE_XML_INSTANCE;
燙燙燙燙?|???? WRITE_XML_ACTIVE_INSTANCE;
WRITE_XML_SYSTEM_EVENTS;
WRITE_XML_DYN_COMPONENTS;
WRITE_XML_IOSTAT_FILE;
WRITE_XML_MEMORY_ADVISORY;
WRITE_XML_PARAMETER;
WRITE_XML_STATS( 'OsStats', G_SNAPSHOT.OSSTAT );
WRITE_XML_STATS( 'PgaStats', G_SNAPSHOT.PGASTAT );
WRITE_XML_STATS( 'SysTimeModels', G_SNAPSHOT.SYS_TIME_MODEL );
WRITE_XML_STATS( 'SysStats', G_SNAPSHOT.SYSSTAT );
WRITE_XML_ARCHIVE_DEST;
WRITE_XML_LOGFILE;
WRITE_XML_ERRORS;
WRITE_XML_END_TAG( 'OracleSnapshot' );
END;
FUNCTION GET_NEXT_SNAPSHOT_ID
RETURN NUMBER
IS
L_SNAPSHOT_ID NUMBER;
BEGIN
EXECUTE IMMEDIATE 'LOCK TABLE "' || GET_OWNER || '".quest_lucy_snapshot IN EXCLUSIVE MODE';
EXECUTE IMMEDIATE 'SELECT NVL (MIN (snapshot_id), 0) - 1 FROM "' || GET_OWNER || '".quest_lucy_snapshot'
INTO L_SNAPSHOT_ID;
RETURN L_SNAPSHOT_ID;
END;
PROCEDURE CREATE_SEQUENCE
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE SEQUENCE "' || GET_OWNER || '".QUEST_LUCY_SEQ_SNAPSHOT_ID
INCREMENT BY 1
START WITH 1
NOCACHE';
END;
FUNCTION GET_QUEST_LUCY_SEQ_SNAPSHOT_ID
RETURN NUMBER
IS
L_SNAPSHOT_ID NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT "' || GET_OWNER || '".quest_lucy_seq_snapshot_id.NEXTVAL FROM DUAL'
INTO L_SNAPSHOT_ID;
RETURN L_SNAPSHOT_ID;
END;
FUNCTION NEW_SNAPSHOT_ID
RETURN NUMBER
IS
L_SNAPSHOT_ID NUMBER;
BEGIN
IF G_GENERATE_SNAPSHOT_ID THEN
BEGIN
L_SNAPSHOT_ID := GET_QUEST_LUCY_SEQ_SNAPSHOT_ID;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get snapshot ID from sequence' );
BEGIN
CREATE_SEQUENCE;
L_SNAPSHOT_ID := GET_QUEST_LUCY_SEQ_SNAPSHOT_ID;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not create the sequence' );
BEGIN
L_SNAPSHOT_ID := GET_NEXT_SNAPSHOT_ID;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get snapshot ID from table' );
L_SNAPSHOT_ID := NULL;
END;
END;
END;
ELSE
L_SNAPSHOT_ID := NULL;
END IF;
RETURN L_SNAPSHOT_ID;
END;
FUNCTION GET_DATABASE_REC
RETURN TYP_DATABASE
IS
L_DATABASE TYP_DATABASE;
L_PLATFORM_COL VARCHAR2( 100 );
L_DB_UNIQUE_NAME_COL VARCHAR2( 100 );
BEGIN
BEGIN_REFRESH_TIMER;
IF GET_ORACLE_VERSION < 10 THEN
L_PLATFORM_COL := 'DBMS_UTILITY.port_string';
L_DB_UNIQUE_NAME_COL := 'name';
ELSE
L_PLATFORM_COL := 'platform_name';
L_DB_UNIQUE_NAME_COL := 'db_unique_name';
END IF;
EXECUTE IMMEDIATE 'SELECT dbid,
' || L_DB_UNIQUE_NAME_COL || ',
name,
' || L_PLATFORM_COL || ',
open_mode
FROM gv$database
WHERE inst_id = :inst_id'
INTO L_DATABASE
USING IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'DatabaseInformation' );
RETURN L_DATABASE;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get DatabaseInformation' );
RETURN L_DATABASE;
END;
FUNCTION GET_INSTANCE_REC
RETURN TYP_INSTANCE
IS
L_INSTANCE TYP_INSTANCE;
BEGIN
BEGIN_REFRESH_TIMER;
EXECUTE IMMEDIATE 'SELECT instance_number,
instance_name,
host_name,
version,
startup_time,
status,
thread#,
instance_role
FROM gv$instance
WHERE inst_id = :inst_id'
INTO L_INSTANCE
燙燙燙燙?|???? USING IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'InstanceInformation' );
RETURN L_INSTANCE;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get InstanceInformation' );
RETURN L_INSTANCE;
END;
FUNCTION GET_ACTIVE_INSTANCE_REC
RETURN TYP_ACTIVE_INSTANCE_ARR
IS
L_ACTIVE_INSTANCE TYP_ACTIVE_INSTANCE_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
EXECUTE IMMEDIATE 'SELECT inst_number,
CASE
WHEN INSTR (inst_name, CHR (0)) = 0 THEN inst_name
ELSE SUBSTR (inst_name, 1, INSTR (inst_name, CHR (0)) - 1)
END
FROM v$active_instances'
INTO L_ACTIVE_INSTANCE;
END_REFRESH_TIMER( 'ActiveInstances' );
RETURN L_ACTIVE_INSTANCE;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get ActiveInstances' );
RETURN L_ACTIVE_INSTANCE;
END;
FUNCTION GET_SYSTEM_EVENT_REC
RETURN TYP_EVENT_ARR
IS
L_SYSTEM_EVENT TYP_EVENT_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
EXECUTE IMMEDIATE 'SELECT event,
total_waits,
total_timeouts,
time_waited_micro / 1000
FROM gv$system_event
WHERE inst_id = :inst_id'
INTO L_SYSTEM_EVENT
USING IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'SystemEvents' );
RETURN L_SYSTEM_EVENT;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get SystemEvents' );
RETURN L_SYSTEM_EVENT;
END;
FUNCTION GET_DYNAMIC_COMPONENT_REC
RETURN TYP_DYNAMIC_COMPONENT_ARR
IS
L_DYNAMIC_COMPONENT TYP_DYNAMIC_COMPONENT_ARR;
L_DYN_COMPONENT_TAB VARCHAR2( 100 );
BEGIN
BEGIN_REFRESH_TIMER;
IF GET_ORACLE_VERSION < 10 THEN
NULL;
ELSE
IF GET_ORACLE_VERSION < 11 THEN
L_DYN_COMPONENT_TAB := 'gv$sga_dynamic_components';
ELSE
L_DYN_COMPONENT_TAB := 'gv$memory_dynamic_components';
END IF;
EXECUTE IMMEDIATE 'SELECT component, current_size
FROM ' || L_DYN_COMPONENT_TAB || '
WHERE inst_id = :inst_id'
INTO L_DYNAMIC_COMPONENT
USING IN G_SNAPSHOT_INSTANCE;
END IF;
END_REFRESH_TIMER( 'DynamicComponents' );
RETURN L_DYNAMIC_COMPONENT;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get DynamicComponents' );
RETURN L_DYNAMIC_COMPONENT;
END;
FUNCTION GET_IOSTAT_FILE_REC
RETURN TYP_IOSTAT_FILE_ARR
IS
L_IOSTAT_FILE TYP_IOSTAT_FILE_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
IF GET_ORACLE_VERSION < 11 THEN
EXECUTE IMMEDIATE 'SELECT ''Data File'' filetype_name,
df.file_name,
ts.tablespace_name,
( (fs.phyblkrd) * ts.block_size) / 1024 / 1024 read_megabytes,
( (fs.phyblkwrt) * ts.block_size) / 1024 / 1024 write_megabytes,
fs.phyrds read_reqs,
fs.phywrts write_reqs,
fs.readtim * 10 read_servicetime,
fs.writetim * 10 write_servicetime,
fs.singleblkrds small_read_reqs,
fs.singleblkrdtim * 10 small_read_servicetime
FROM gv$filestat fs, dba_data_files df, dba_tablespaces ts
WHERE fs.inst_id = :inst_id
AND fs.file# = df.file_id
AND ts.tablespace_name = df.tablespace_name
UNION ALL
SELECT ''Temp File'' filetype_name,
tf.file_name,
ts.tablespace_name,
( (fs.phyblkrd) * ts.block_size) / 1024 / 1024 read_megabytes,
( (fs.phyblkwrt) * ts.block_size) / 1024 / 1024 write_megabytes,
fs.phyrds read_reqs,
fs.phywrts write_reqs,
燙燙燙燙?|???? fs.readtim * 10 read_servicetime,
fs.writetim * 10 write_servicetime,
fs.singleblkrds small_read_reqs,
fs.singleblkrdtim * 10 small_read_servicetime
FROM gv$tempstat fs, dba_temp_files tf, dba_tablespaces ts
WHERE fs.inst_id = :inst_id
AND tf.file_id = fs.file#
AND ts.tablespace_name = tf.tablespace_name
UNION ALL
SELECT ''Control File'' filetype_name,
NULL file_name,
NULL tablespace_name,
NULL read_megabytes,
NULL write_megabytes,
SUM (total_waits) read_reqs,
NULL write_reqs,
ROUND (SUM (time_waited_micro) / 1000) read_servicetime,
NULL write_servicetime,
SUM (total_waits) small_read_reqs,
ROUND (SUM (time_waited_micro) / 1000) small_read_servicetime
FROM gv$system_event
WHERE inst_id = :inst_id
AND event IN
(''control file parallel write'', ''control file sequential read'')
UNION ALL
SELECT ''Log File'' filetype_name,
NULL file_name,
NULL tablespace_name,
NULL read_megabytes,
NULL write_megabytes,
SUM (total_waits) read_reqs,
NULL write_reqs,
ROUND (SUM (time_waited_micro) / 1000) read_servicetime,
NULL write_servicetime,
SUM (total_waits) small_read_reqs,
ROUND (SUM (time_waited_micro) / 1000) small_read_servicetime
FROM gv$system_event
WHERE inst_id = :inst_id
AND event IN
(''log file single write'',
''log file parallel write'',
''log file sequential read'')'
INTO L_IOSTAT_FILE
USING IN G_SNAPSHOT_INSTANCE, IN G_SNAPSHOT_INSTANCE, IN G_SNAPSHOT_INSTANCE, IN G_SNAPSHOT_INSTANCE;
ELSE
EXECUTE IMMEDIATE 'SELECT ios.filetype_name,
df.file_name,
df.tablespace_name,
ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
ios.small_read_reqs + ios.large_read_reqs read_reqs,
ios.small_write_reqs + ios.large_write_reqs write_reqs,
ios.small_read_servicetime + ios.large_read_servicetime
read_servicetime,
ios.small_write_servicetime + ios.large_write_servicetime
write_servicetime,
ios.small_read_reqs,
ios.small_read_servicetime
FROM gv$iostat_file ios, dba_data_files df
WHERE ios.inst_id = :inst_id
AND ios.filetype_name = ''Data File''
AND df.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
tf.file_name,
tf.tablespace_name,
ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
ios.small_read_reqs + ios.large_read_reqs read_reqs,
ios.small_write_reqs + ios.large_write_reqs write_reqs,
ios.small_read_servicetime + ios.large_read_servicetime
read_servicetime,
ios.small_write_servicetime + ios.large_write_servicetime
write_servicetime,
ios.small_read_reqs,
ios.small_read_servicetime
FROM gv$iostat燙燙燙燙?|????_file ios, dba_temp_files tf
WHERE ios.inst_id = :inst_id
AND ios.filetype_name = ''Temp File''
AND tf.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
NULL file_name,
NULL tablespace_name,
ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
ios.small_read_reqs + ios.large_read_reqs read_reqs,
ios.small_write_reqs + ios.large_write_reqs write_reqs,
ios.small_read_servicetime + ios.large_read_servicetime
read_servicetime,
ios.small_write_servicetime + ios.large_write_servicetime
write_servicetime,
ios.small_read_reqs,
ios.small_read_servicetime
FROM gv$iostat_file ios
WHERE ios.inst_id = :inst_id
AND ios.filetype_name NOT IN (''Temp File'', ''Data File'')'
INTO L_IOSTAT_FILE
USING IN G_SNAPSHOT_INSTANCE, IN G_SNAPSHOT_INSTANCE, IN G_SNAPSHOT_INSTANCE;
END IF;
END_REFRESH_TIMER( 'IostatFiles' );
RETURN L_IOSTAT_FILE;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get IostatFiles' );
RETURN L_IOSTAT_FILE;
END;
FUNCTION GET_MEMORY_ADVISORY_REC
RETURN TYP_MEMORY_ADVISORY_ARR
IS
L_MEMORY_ADVISORY TYP_MEMORY_ADVISORY_ARR;
L_DBCACHE_ESTD_TIME_COL VARCHAR2( 100 );
L_PGA_ESTD_TIME_COL VARCHAR2( 100 );
BEGIN
BEGIN_REFRESH_TIMER;
IF GET_ORACLE_VERSION < 10 THEN
L_DBCACHE_ESTD_TIME_COL := 'NULL';
ELSE
L_DBCACHE_ESTD_TIME_COL := 'dbca.estd_physical_read_time';
END IF;
IF GET_ORACLE_VERSION < 11 THEN
L_PGA_ESTD_TIME_COL := 'NULL';
ELSE
L_PGA_ESTD_TIME_COL := 'pga.estd_time';
END IF;
EXECUTE IMMEDIATE 'SELECT ''DBCACHE'' advisory_name,
dbca.name pool_name,
dbca.block_size pool_block_size,
dbca.size_factor memory_size_factor,
dbca.size_for_estimate memory_size,
dbca.estd_physical_reads estd_physical_reads,
' || L_DBCACHE_ESTD_TIME_COL || ' estd_time,
NULL estd_overalloc_count
FROM gv$db_cache_advice dbca
WHERE dbca.inst_id = :inst_id
UNION ALL
SELECT ''PGA'',
'' '',
0,
pga.pga_target_factor,
pga.pga_target_for_estimate / 1024 / 1024,
pga.estd_extra_bytes_rw,
' || L_PGA_ESTD_TIME_COL || ' estd_time,
pga.estd_overalloc_count
FROM gv$pga_target_advice pga
WHERE pga.inst_id = :inst_id'
INTO L_MEMORY_ADVISORY
USING IN G_SNAPSHOT_INSTANCE, IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'MemoryAdvisories' );
RETURN L_MEMORY_ADVISORY;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get MemoryAdvisories' );
RETURN L_MEMORY_ADVISORY;
END;
FUNCTION GET_PARAMETER_REC
RETURN TYP_PARAMETER_ARR
IS
L_PARAMETER TYP_PARAMETER_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
EXECUTE IMMEDIATE 'SELECT name, VALUE, isdefault
FROM gv$system_parameter
WHERE inst_id = :inst_id'
INTO L_PARAMETER
USING IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'Parameters' );
RETURN L_PARAMETER;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get Parameters' );
RETURN L_PARAMETER;
END;
FUNCTION GET_OSSTAT_REC
RETURN TYP_STAT_ARR
IS
L_OSSTAT TYP_STAT_ARR;
BEGIN
BEGIN_REFRESH_TI燙燙燙燙?|????MER;
IF GET_ORACLE_VERSION < 10 THEN
NULL;
ELSE
EXECUTE IMMEDIATE 'SELECT stat_name, VALUE
FROM gv$osstat
WHERE inst_id = :inst_id'
INTO L_OSSTAT
USING IN G_SNAPSHOT_INSTANCE;
END IF;
END_REFRESH_TIMER( 'OsStats' );
RETURN L_OSSTAT;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get OsStats' );
RETURN L_OSSTAT;
END;
FUNCTION GET_PGASTAT_REC
RETURN TYP_STAT_ARR
IS
L_PGASTAT TYP_STAT_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
EXECUTE IMMEDIATE 'SELECT name, VALUE
FROM gv$pgastat
WHERE inst_id = :inst_id'
INTO L_PGASTAT
USING IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'PgaStats' );
RETURN L_PGASTAT;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get PgaStats' );
RETURN L_PGASTAT;
END;
FUNCTION GET_SYS_TIME_MODEL_REC
RETURN TYP_STAT_ARR
IS
L_SYS_TIME_MODEL TYP_STAT_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
IF GET_ORACLE_VERSION < 10 THEN
NULL;
ELSE
EXECUTE IMMEDIATE 'SELECT stat_name, VALUE / 1000 VALUE
FROM gv$sys_time_model
WHERE inst_id = :inst_id'
INTO L_SYS_TIME_MODEL
USING IN G_SNAPSHOT_INSTANCE;
END IF;
END_REFRESH_TIMER( 'SysTimeModels' );
RETURN L_SYS_TIME_MODEL;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get SysTimeModels' );
RETURN L_SYS_TIME_MODEL;
END;
FUNCTION GET_SYSSTAT_REC
RETURN TYP_STAT_ARR
IS
L_SYSSTAT TYP_STAT_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
EXECUTE IMMEDIATE 'SELECT name, VALUE
FROM gv$sysstat
WHERE inst_id = :inst_id'
INTO L_SYSSTAT
USING IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'SysStats' );
RETURN L_SYSSTAT;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get SysStats' );
RETURN L_SYSSTAT;
END;
FUNCTION GET_ARCHIVE_DEST_REC
RETURN TYP_ARCHIVE_DEST_ARR
IS
L_ARCHIVE_DEST TYP_ARCHIVE_DEST_ARR;
L_DB_UNIQUE_NAME_COL VARCHAR2( 100 );
BEGIN
BEGIN_REFRESH_TIMER;
IF GET_ORACLE_VERSION < 10 THEN
L_DB_UNIQUE_NAME_COL := 'NULL';
ELSE
L_DB_UNIQUE_NAME_COL := 'db_unique_name';
END IF;
EXECUTE IMMEDIATE 'SELECT dest_id,
dest_name,
status,
binding,
target,
archiver,
schedule,
destination,
delay_mins,
process,
transmit_mode,
' || L_DB_UNIQUE_NAME_COL || ' db_unique_name
FROM gv$archive_dest
WHERE inst_id = :inst_id AND status <> ''INACTIVE'''
INTO L_ARCHIVE_DEST
USING IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'ArchiveDests' );
RETURN L_ARCHIVE_DEST;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get ArchiveDests' );
RETURN L_ARCHIVE_DEST;
END;
FUNCTION GET_LOGFILE_REC
RETURN TYP_LOGFILE_ARR
IS
L_LOGFILE TYP_LOGFILE_ARR;
BEGIN
BEGIN_REFRESH_TIMER;
EXECUTE IMMEDIATE 'SELECT l.group#,
l.thread#,
l.sequence#,
l.bytes,
l.archived,
l.status group_status,
l.first_change#,
l.first_time,
f.MEMBER member_name,
f.status member_status
FROM gv$log l, gv$logfile f
WHERE l.inst_id = :inst_id AND f.inst_id = :inst_id AND l.group# = f.group#'
INTO L_LOGFILE
USING IN G_SNAPSH燙燙燙燙?|????OT_INSTANCE, IN G_SNAPSHOT_INSTANCE;
END_REFRESH_TIMER( 'Logfiles' );
RETURN L_LOGFILE;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not get Logfiles' );
RETURN L_LOGFILE;
END;
PROCEDURE SAVE_SNAPSHOT
IS
BEGIN
WRITE_SNAPSHOT_XML;
EXECUTE IMMEDIATE 'INSERT INTO "' || GET_OWNER || '".quest_lucy_snapshot (snapshot_id, snapshot_time, snapshot_xml)
VALUES (:snapshot_id, :snapshot_time, :snapshot_xml)'
USING IN G_SNAPSHOT.SNAPSHOT_ID, IN G_SNAPSHOT.SNAPSHOT_TIME, IN G_SNAPSHOT_XML;
COMMIT;
END;
PROCEDURE POPULATE_SNAPSHOT( I_INST_ID IN NUMBER := NULL )
IS
BEGIN
G_SNAPSHOT_INSTANCE := NVL( I_INST_ID, GET_CURRENT_INSTANCE );
G_SNAPSHOT.SNAPSHOT_ID := NEW_SNAPSHOT_ID;
G_SNAPSHOT.SNAPSHOT_TIME := SYSTIMESTAMP;
G_SNAPSHOT.DATABASE := GET_DATABASE_REC;
G_SNAPSHOT.INSTANCE := GET_INSTANCE_REC;
G_SNAPSHOT.ACTIVE_INSTANCE := GET_ACTIVE_INSTANCE_REC;
G_SNAPSHOT.SYSTEM_EVENT := GET_SYSTEM_EVENT_REC;
G_SNAPSHOT.DYNAMIC_COMPONENT := GET_DYNAMIC_COMPONENT_REC;
G_SNAPSHOT.IOSTAT_FILE := GET_IOSTAT_FILE_REC;
G_SNAPSHOT.MEMORY_ADVISORY := GET_MEMORY_ADVISORY_REC;
G_SNAPSHOT.PARAMETER := GET_PARAMETER_REC;
G_SNAPSHOT.OSSTAT := GET_OSSTAT_REC;
G_SNAPSHOT.PGASTAT := GET_PGASTAT_REC;
G_SNAPSHOT.SYS_TIME_MODEL := GET_SYS_TIME_MODEL_REC;
G_SNAPSHOT.SYSSTAT := GET_SYSSTAT_REC;
G_SNAPSHOT.ARCHIVE_DEST := GET_ARCHIVE_DEST_REC;
G_SNAPSHOT.LOGFILE := GET_LOGFILE_REC;
END;
PROCEDURE RESET_VARS
IS
BEGIN
G_SNAPSHOT := NULL;
EXECUTE IMMEDIATE 'BEGIN
"' || GET_OWNER || '".quest_lucy_collector.g_snapshot_xml := empty_clob;
DBMS_LOB.createtemporary ("' || GET_OWNER || '".quest_lucy_collector.g_snapshot_xml, TRUE);
END;';
G_INDENT_LEVEL := 0;
G_REFRESH_TIMES.DELETE;
G_TEMP_REFRESH_TIME := NULL;
G_SCHEDULER_JOB_LIST.DELETE;
G_SCHEDULER_JOB_LIST_INIT := FALSE;
G_DBMS_JOB_LIST.DELETE;
G_DBMS_JOB_LIST_INIT := FALSE;
G_INSTANCE_LIST.DELETE;
G_CURRENT_INSTANCE := NULL;
G_SNAPSHOT_INSTANCE := NULL;
G_OWNER := NULL;
G_USER := NULL;
G_SNAPSHOT_ID_LIST.DELETE;
G_GENERATE_SNAPSHOT_ID := TRUE;
END;
PROCEDURE ENABLE_ROLES
IS
L_PASSWORD_ROLE_ARR TYP_VARCHAR2_TABLE;
L_PASSWORD_ROLE_LIST VARCHAR2( 4000 );
BEGIN
BEGIN
EXECUTE IMMEDIATE 'SELECT r.role
FROM user_role_privs urp, dba_roles r
WHERE r.role = urp.granted_role AND password_required = ''YES'''
INTO L_PASSWORD_ROLE_ARR;
FOR I IN 1..L_PASSWORD_ROLE_ARR.COUNT
LOOP
IF I > 1 THEN
L_PASSWORD_ROLE_LIST := L_PASSWORD_ROLE_LIST || ',';
END IF;
L_PASSWORD_ROLE_LIST := L_PASSWORD_ROLE_LIST || '"' || L_PASSWORD_ROLE_ARR( I ) || '"';
END LOOP;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not retrieve list of password protected roles.' );
L_PASSWORD_ROLE_LIST := NULL;
END;
BEGIN
IF L_PASSWORD_ROLE_LIST IS NOT NULL THEN
EXECUTE IMMEDIATE 'SET ROLE ALL EXCEPT ' || L_PASSWORD_ROLE_LIST;
ELSE
EXECUTE IMMEDIATE 'SET ROLE ALL';
END IF;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not enable roles.' );
END;
END;
PROCEDURE GET_SAVED_SNAPSHOTS
IS
BEGIN
EXECUTE IMMEDIATE 'DECLARE
TYPE ltyp_snapshot_rows IS TABLE OF "' || GET_OWNER || '".quest_lucy_snapshot%ROWTYPE
INDEX BY PLS_INTEGER;

l_snapshots ltyp_snapshot_rows;
BEGIN
SELECT *
BULK COLLECT INTO l_snapshots
FROM "' || GET_OWNER || '".quest_lucy_snapshot
FOR UPDATE;

燙燙燙燙?|???? FOR i IN 1 .. l_snapshots.COUNT
LOOP
DBMS_LOB.append ("' || GET_OWNER || '".quest_lucy_collector.g_snapshot_xml,
l_snapshots (i).snapshot_xml);
"' || GET_OWNER || '".quest_lucy_collector.g_snapshot_id_list (i) :=
l_snapshots (i).snapshot_id;
END LOOP;
END;';
END;
PROCEDURE DELETE_SNAPSHOTS
IS
BEGIN
FORALL I IN 1..G_SNAPSHOT_ID_LIST.COUNT
EXECUTE IMMEDIATE 'DELETE FROM "' || GET_OWNER || '".quest_lucy_snapshot
WHERE snapshot_id = :snapshot_id'
USING IN G_SNAPSHOT_ID_LIST( I );
END;
FUNCTION IS_TABLE_CREATED
RETURN BOOLEAN
IS
L_NUM_ROWS NUMBER;
L_OWNER VARCHAR2( 4000 ) := GET_OWNER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT (*)
FROM all_tables
WHERE owner = :owner
AND table_name = ''QUEST_LUCY_SNAPSHOT'''
INTO L_NUM_ROWS
USING IN L_OWNER;
IF L_NUM_ROWS > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
PROCEDURE CREATE_TABLE
IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE "' || GET_OWNER || '".QUEST_LUCY_SNAPSHOT
(
SNAPSHOT_ID NUMBER (*, 0),
SNAPSHOT_TIME TIMESTAMP (6) WITH TIME ZONE NOT NULL,
SNAPSHOT_XML CLOB
)';
EXECUTE IMMEDIATE 'ALTER TABLE "' || GET_OWNER || '".QUEST_LUCY_SNAPSHOT ADD CONSTRAINT QUEST_LUCY_SNAPSHOT_PK PRIMARY KEY (SNAPSHOT_ID)';
END;
FUNCTION IS_SCHEDULER_JOB_OK( I_SCHEDULER_JOB IN TYP_SCHEDULER_JOB, I_INSTANCE IN NUMBER )
RETURN BOOLEAN
IS
BEGIN
IF I_SCHEDULER_JOB.INSTANCE_ID IS NULL OR I_SCHEDULER_JOB.INSTANCE_ID != I_INSTANCE THEN
RETURN FALSE;
END IF;
IF I_SCHEDULER_JOB.JOB_TYPE IS NULL OR I_SCHEDULER_JOB.JOB_ACTION IS NULL OR I_SCHEDULER_JOB.JOB_TYPE != GC_JOB_TYPE OR I_SCHEDULER_JOB.JOB_ACTION != GC_JOB_ACTION THEN
RETURN FALSE;
END IF;
IF I_SCHEDULER_JOB.REPEAT_INTERVAL IS NULL OR I_SCHEDULER_JOB.REPEAT_INTERVAL != GC_REPEAT_INTERVAL THEN
RETURN FALSE;
END IF;
IF I_SCHEDULER_JOB.START_DATE IS NULL OR I_SCHEDULER_JOB.START_DATE > SYSTIMESTAMP THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
FUNCTION IS_DBMS_JOB_OK( I_DBMS_JOB IN TYP_DBMS_JOB )
RETURN BOOLEAN
IS
BEGIN
IF I_DBMS_JOB.INTERVAL IS NULL OR I_DBMS_JOB.INTERVAL != GC_DBMS_JOB_INTERVAL THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END;
FUNCTION IS_ALL_SCHEDULER_JOBS_OK
RETURN BOOLEAN
IS
L_SCHEDULER_JOB_LIST TYP_SCHEDULER_JOB_ARR;
L_INSTANCE_LIST TYP_NUMBER_TABLE;
L_INSTANCE_ITERATOR PLS_INTEGER;
L_JOB_NAME VARCHAR2( 30 );
BEGIN
L_INSTANCE_LIST := GET_INSTANCE_LIST;
L_SCHEDULER_JOB_LIST := GET_SCHEDULER_JOB_LIST;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.FIRST;
WHILE L_INSTANCE_ITERATOR IS NOT NULL
LOOP
L_JOB_NAME := GC_JOB_PREFIX || '_' || L_INSTANCE_LIST( L_INSTANCE_ITERATOR );
IF NOT L_SCHEDULER_JOB_LIST.EXISTS( L_JOB_NAME ) THEN
RETURN FALSE;
END IF;
IF NOT IS_SCHEDULER_JOB_OK( L_SCHEDULER_JOB_LIST( L_JOB_NAME ), L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ) THEN
RETURN FALSE;
END IF;
IF L_SCHEDULER_JOB_LIST( L_JOB_NAME ).ENABLED IS NULL OR L_SCHEDULER_JOB_LIST( L_JOB_NAME ).ENABLED != 'TRUE' THEN
RETURN FALSE;
END IF;
IF L_SCHEDULER_JOB_LIST( L_JOB_NAME ).START_DATE < ( SYSTIMESTAMP - NUMTODSINTERVAL( GC_REPEAT_MINUTES * 2, 'MINUTE' ) ) AND ( L_SCHEDULER_JOB_LIST( L_JOB_NAME ).LAST_START_DATE IS NULL OR L_SCHEDULER_JOB_LIST( L_JOB_NAME ).LAST_START_DATE < ( SYSTIMESTAMP - NUMTODSINTERVAL( GC_REPEAT_MINUTES * 2, 'MINUTE' ) ) ) THEN
RETURN FALSE;
END IF;
L_INSTANCE_ITER燙燙燙燙?|????ATOR := L_INSTANCE_LIST.NEXT( L_INSTANCE_ITERATOR );
END LOOP;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not determine if scheduler jobs are OK' );
RETURN FALSE;
END;
FUNCTION IS_ALL_DBMS_JOBS_OK
RETURN BOOLEAN
IS
L_INSTANCE_LIST TYP_NUMBER_TABLE;
L_DBMS_JOB_LIST TYP_DBMS_JOB_ARR;
L_INSTANCE_ITERATOR PLS_INTEGER;
BEGIN
L_INSTANCE_LIST := GET_INSTANCE_LIST;
L_DBMS_JOB_LIST := GET_DBMS_JOB_LIST;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.FIRST;
WHILE L_INSTANCE_ITERATOR IS NOT NULL
LOOP
IF NOT L_DBMS_JOB_LIST.EXISTS( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ) THEN
RETURN FALSE;
END IF;
IF NOT IS_DBMS_JOB_OK( L_DBMS_JOB_LIST( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ) ) THEN
RETURN FALSE;
END IF;
IF L_DBMS_JOB_LIST( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ).BROKEN IS NULL OR L_DBMS_JOB_LIST( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ).BROKEN = 'Y' THEN
RETURN FALSE;
END IF;
IF L_DBMS_JOB_LIST( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ).LAST_DATE IS NULL OR L_DBMS_JOB_LIST( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ).LAST_DATE < ( SYSDATE - ( ( GC_REPEAT_MINUTES * 2 ) / ( 24 * 60 ) ) ) THEN
RETURN FALSE;
END IF;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.NEXT( L_INSTANCE_ITERATOR );
END LOOP;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not determine if dbms jobs are OK' );
RETURN FALSE;
END;
PROCEDURE SETUP_SCHEDULER_JOB
IS
L_JOB_NAME VARCHAR2( 30 );
L_FULL_JOB_NAME VARCHAR2( 100 );
L_SCHEDULER_JOB_LIST TYP_SCHEDULER_JOB_ARR;
L_INSTANCE_ITERATOR PLS_INTEGER;
L_INSTANCE_LIST TYP_NUMBER_TABLE;
BEGIN
L_INSTANCE_LIST := GET_INSTANCE_LIST;
L_SCHEDULER_JOB_LIST := GET_SCHEDULER_JOB_LIST;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.FIRST;
WHILE L_INSTANCE_ITERATOR IS NOT NULL
LOOP
L_JOB_NAME := GC_JOB_PREFIX || '_' || L_INSTANCE_LIST( L_INSTANCE_ITERATOR );
L_FULL_JOB_NAME := '"' || GET_OWNER || '".' || L_JOB_NAME;
IF L_SCHEDULER_JOB_LIST.EXISTS( L_JOB_NAME ) THEN
IF NOT IS_SCHEDULER_JOB_OK( L_SCHEDULER_JOB_LIST( L_JOB_NAME ), L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ) THEN
G_SCHEDULER_JOB_LIST_INIT := FALSE;
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute (
:job_name,
''INSTANCE_ID'',
:instance);
END;'
USING IN L_FULL_JOB_NAME, IN L_INSTANCE_LIST( L_INSTANCE_ITERATOR );
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute (:job_name,
''JOB_TYPE'',
:job_type);
END;'
USING IN L_FULL_JOB_NAME, IN GC_JOB_TYPE;
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute (:job_name,
''JOB_ACTION'',
:job_action);
END;'
USING IN L_FULL_JOB_NAME, IN GC_JOB_ACTION;
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute (:job_name,
''REPEAT_INTERVAL'',
:repeat_interval);
END;'
USING IN L_FULL_JOB_NAME, IN GC_REPEAT_INTERVAL;
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute (:job_name,
''START_DATE'',
燙燙燙燙?|???? SYSTIMESTAMP);
END;'
USING IN L_FULL_JOB_NAME;
END IF;
ELSE
G_SCHEDULER_JOB_LIST_INIT := FALSE;
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.create_job (
job_name => :job_name,
job_type => :job_type,
job_action => :job_action,
start_date => SYSTIMESTAMP,
repeat_interval => :repeat_interval,
enabled => FALSE,
auto_drop => FALSE);
END;'
USING IN L_FULL_JOB_NAME, IN GC_JOB_TYPE, IN GC_JOB_ACTION, IN GC_REPEAT_INTERVAL;
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.set_attribute (
:job_name,
''INSTANCE_ID'',
:instance);
END;'
USING IN L_FULL_JOB_NAME, IN L_INSTANCE_LIST( L_INSTANCE_ITERATOR );
EXECUTE IMMEDIATE 'BEGIN
DBMS_SCHEDULER.enable (:job_name);
END;'
USING IN L_FULL_JOB_NAME;
END IF;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.NEXT( L_INSTANCE_ITERATOR );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not create scheduler jobs' );
END;
PROCEDURE SETUP_DBMS_JOB
IS
L_JOB_NO NUMBER;
L_INSTANCE_LIST TYP_NUMBER_TABLE;
L_DBMS_JOB_LIST TYP_DBMS_JOB_ARR;
L_INSTANCE_ITERATOR PLS_INTEGER;
L_NLS_ENV VARCHAR2( 4000 );
L_OWNER VARCHAR2( 4000 ) := GET_OWNER;
L_USER VARCHAR2( 4000 ) := GET_USER;
BEGIN
L_INSTANCE_LIST := GET_INSTANCE_LIST;
L_DBMS_JOB_LIST := GET_DBMS_JOB_LIST;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.FIRST;
WHILE L_INSTANCE_ITERATOR IS NOT NULL
LOOP
IF NOT ( L_DBMS_JOB_LIST.EXISTS( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ) AND IS_DBMS_JOB_OK( L_DBMS_JOB_LIST( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ) ) ) THEN
IF L_DBMS_JOB_LIST.EXISTS( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ) THEN
REMOVE_DBMS_JOB( L_DBMS_JOB_LIST( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) ).JOB );
END IF;
EXECUTE IMMEDIATE 'BEGIN
DBMS_JOB.submit (
job => :job_no,
what => :job_action,
next_date => SYSDATE,
INTERVAL => :dbms_job_interval,
INSTANCE => :instance);
END;'
USING OUT L_JOB_NO, IN GC_JOB_ACTION, IN GC_DBMS_JOB_INTERVAL, IN L_INSTANCE_LIST( L_INSTANCE_ITERATOR );
IF L_OWNER != L_USER THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT nls_env
FROM user_jobs
WHERE job = :job_no'
INTO L_NLS_ENV
USING IN L_JOB_NO;
EXECUTE IMMEDIATE 'BEGIN
SYS.DBMS_IJOB.change_env (job => :job_no,
luser => :luser,
puser => :puser,
cuser => :cuser,
nlsenv => :nlsenv);
END;'
USING IN L_JOB_NO, IN L_USER, IN L_OWNER, IN L_OWNER, IN L_NLS_ENV;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not change job user' );
REMOVE_DBMS_JOB( L_JOB_NO );
END;
END IF;
G_DBMS_JOB_LIST_INIT := FALSE;
END IF;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.NEXT( L_INSTAN燙燙燙燙?|????CE_ITERATOR );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not create dbms jobs' );
END;
PROCEDURE REMOVE_ALL_DBMS_JOB
IS
L_DBMS_JOB_LIST TYP_DBMS_JOB_ARR;
L_DBMS_JOB_ITERATOR PLS_INTEGER;
BEGIN
L_DBMS_JOB_LIST := GET_DBMS_JOB_LIST;
L_DBMS_JOB_ITERATOR := L_DBMS_JOB_LIST.FIRST;
WHILE L_DBMS_JOB_ITERATOR IS NOT NULL
LOOP
REMOVE_DBMS_JOB( L_DBMS_JOB_LIST( L_DBMS_JOB_ITERATOR ).JOB );
L_DBMS_JOB_ITERATOR := L_DBMS_JOB_LIST.NEXT( L_DBMS_JOB_ITERATOR );
END LOOP;
END;
PROCEDURE REMOVE_ALL_SCHEDULER_JOB
IS
L_SCHEDULER_JOB_LIST TYP_SCHEDULER_JOB_ARR;
L_JOB_ITERATOR VARCHAR2( 30 );
BEGIN
L_SCHEDULER_JOB_LIST := GET_SCHEDULER_JOB_LIST;
L_JOB_ITERATOR := L_SCHEDULER_JOB_LIST.FIRST;
WHILE L_JOB_ITERATOR IS NOT NULL
LOOP
REMOVE_SCHEDULER_JOB( L_SCHEDULER_JOB_LIST( L_JOB_ITERATOR ).JOB_NAME );
L_JOB_ITERATOR := L_SCHEDULER_JOB_LIST.NEXT( L_JOB_ITERATOR );
END LOOP;
END;
PROCEDURE REMOVE_ALL_JOB
IS
BEGIN
IF GET_ORACLE_VERSION > 11 THEN
REMOVE_ALL_SCHEDULER_JOB;
END IF;
REMOVE_ALL_DBMS_JOB;
END;
PROCEDURE POPULATE_WRITE_ALL
IS
L_INSTANCE_LIST TYP_NUMBER_TABLE;
L_INSTANCE_ITERATOR PLS_INTEGER;
BEGIN
L_INSTANCE_LIST := GET_INSTANCE_LIST;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.FIRST;
WHILE L_INSTANCE_ITERATOR IS NOT NULL
LOOP
POPULATE_SNAPSHOT( L_INSTANCE_LIST( L_INSTANCE_ITERATOR ) );
WRITE_SNAPSHOT_XML;
L_INSTANCE_ITERATOR := L_INSTANCE_LIST.NEXT( L_INSTANCE_ITERATOR );
END LOOP;
END;
PROCEDURE TAKE_SNAPSHOT
IS
L_IS_TABLE_CREATED BOOLEAN := FALSE;
L_OLDEST_SNAPSHOT_TIME TIMESTAMP WITH TIME ZONE( 6 );
BEGIN
G_ERRORS.DELETE;
ENABLE_ROLES;
RESET_VARS;
G_INDENT_LEVEL := 2;
L_IS_TABLE_CREATED := IS_TABLE_CREATED;
IF NOT L_IS_TABLE_CREATED THEN
BEGIN
CREATE_TABLE;
L_IS_TABLE_CREATED := IS_TABLE_CREATED;
EXCEPTION
WHEN OTHERS THEN
REMOVE_ALL_JOB;
END;
END IF;
IF L_IS_TABLE_CREATED THEN
L_OLDEST_SNAPSHOT_TIME := GET_OLDEST_SNAPSHOT_TIME;
IF L_OLDEST_SNAPSHOT_TIME < ( SYSTIMESTAMP - NUMTODSINTERVAL( GC_SNAPSHOT_RETENTION_DAYS, 'DAY' ) ) THEN
REMOVE_ALL_JOB;
END IF;
POPULATE_SNAPSHOT;
SAVE_SNAPSHOT;
END IF;
RESET_VARS;
END;
FUNCTION GET_SNAPSHOTS( I_SCHEDULE_COLLECTOR IN NUMBER := 1 )
RETURN CLOB
IS
PRAGMA AUTONOMOUS_TRANSACTION;
L_USE_SAVED_SNAPSHOTS BOOLEAN := FALSE;
L_SNAPSHOT_ARR TYP_SNAPSHOT_ARR;
L_IS_TABLE_CREATED BOOLEAN := FALSE;
L_SCHEDULER_JOB_LIST TYP_SCHEDULER_JOB_ARR;
L_DBMS_JOB_LIST TYP_DBMS_JOB_ARR;
BEGIN
G_ERRORS.DELETE;
ENABLE_ROLES;
RESET_VARS;
WRITE_XML_BEGIN_TAG( 'OracleUpload xmlns="' || GC_XML_NAMESPACE || '"' );
WRITE_XML_BEGIN_TAG( 'OracleSnapshots' );
IF I_SCHEDULE_COLLECTOR = 1 THEN
L_IS_TABLE_CREATED := IS_TABLE_CREATED;
IF NOT L_IS_TABLE_CREATED THEN
BEGIN
CREATE_TABLE;
L_IS_TABLE_CREATED := IS_TABLE_CREATED;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not create table' );
END;
END IF;
IF L_IS_TABLE_CREATED THEN
IF GET_ORACLE_VERSION > 11 THEN
SETUP_SCHEDULER_JOB;
IF IS_ALL_SCHEDULER_JOBS_OK THEN
L_USE_SAVED_SNAPSHOTS := TRUE;
ELSE
SETUP_DBMS_JOB;
IF IS_ALL_DBMS_JOBS_OK THEN
L_USE_SAVED_SNAPSHOTS := TRUE;
END IF;
END IF;
BEGIN
L_SCHEDULER_JOB_LIST :=燙燙燙燙?|???? GET_SCHEDULER_JOB_LIST;
L_DBMS_JOB_LIST := GET_DBMS_JOB_LIST;
IF L_SCHEDULER_JOB_LIST.COUNT > 0 AND L_DBMS_JOB_LIST.COUNT > 0 AND IS_ALL_SCHEDULER_JOBS_OK THEN
REMOVE_ALL_DBMS_JOB;
END IF;
EXCEPTION
WHEN OTHERS THEN
SAVE_EXCEPTION( 'Could not check if there are dbms and scheduler jobs' );
END;
ELSE
SETUP_DBMS_JOB;
IF IS_ALL_DBMS_JOBS_OK THEN
L_USE_SAVED_SNAPSHOTS := TRUE;
END IF;
END IF;
GET_SAVED_SNAPSHOTS;
DELETE_SNAPSHOTS;
IF NOT L_USE_SAVED_SNAPSHOTS THEN
POPULATE_WRITE_ALL;
END IF;
ELSE
POPULATE_WRITE_ALL;
END IF;
ELSE
G_GENERATE_SNAPSHOT_ID := FALSE;
POPULATE_WRITE_ALL;
END IF;
WRITE_XML_END_TAG( 'OracleSnapshots' );
WRITE_XML_ERRORS;
WRITE_XML_END_TAG( 'OracleUpload' );
COMMIT;
RETURN G_SNAPSHOT_XML;
END;
PROCEDURE DROP_OBJECTS
IS
PRAGMA AUTONOMOUS_TRANSACTION;
TABLE_NOT_EXISTS EXCEPTION;
SEQUENCE_NOT_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT( TABLE_NOT_EXISTS, -942 );
PRAGMA EXCEPTION_INIT( SEQUENCE_NOT_EXISTS, -2289 );
BEGIN
RESET_VARS;
REMOVE_ALL_JOB;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE "' || GET_OWNER || '".QUEST_LUCY_SNAPSHOT';
EXCEPTION
WHEN TABLE_NOT_EXISTS THEN
NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE "' || GET_OWNER || '".QUEST_LUCY_SEQ_SNAPSHOT_ID';
EXCEPTION
WHEN SEQUENCE_NOT_EXISTS THEN
NULL;
END;
COMMIT;
END;
END QUEST_LUCY_COLLECTOR;
/

QUEST_LUCY_COLLECTOR.sql 太厲害了！

相關文章