| 8b5vzx9k2t7s9 |
/* OracleOEM */
DECLARE
TYPE data_cursor_type IS REF CURSOR;
data_cursor data_cursor_type;
x clob := null;
pos1 INTEGER := 1;
pos2 INTEGER := 4000;
statData clob := null;
sizeData clob := null;
objectsData clob := null;
tmp VARCHAR2(4000);
partitionName VARCHAR2(4000);
jobName VARCHAR2(500);
idx_name VARCHAR2(4000);
part_name VARCHAR2(4000);
tmp_str VARCHAR2(4000);
guid RAW(16);
idx_guid RAW(16);
cursor idx_cur IS
select owner, job_name, comments
from dba_scheduler_jobs where job_name like 'EM_IDX_STAT_JOB%' and
upper(owner) = 'DBSNMP';
idx_rec idx_cur%ROWTYPE;
BEGIN
OPEN idx_cur;
FETCH idx_cur into idx_rec;
guid := :1;
IF idx_cur%FOUND THEN
dbms_lob.createtemporary(statData, false);
dbms_lob.createtemporary(sizeData, false);
dbms_lob.createtemporary(objectsData, false);
idx_name := substr(idx_rec.comments, 1, instr(idx_rec.comments, '|')-1);
tmp_str := substr(idx_rec.comments, instr(idx_rec.comments, '|')+1);
part_name := substr(tmp_str, 1, instr(tmp_str, '|')-1);
idx_guid := substr(tmp_str, instr(tmp_str, '|')+1);
if guid = idx_guid THEN
if part_name != 'null' then
partitionName := part_name;
end if;
ctx_report.index_stats(idx_name, x, partitionName, true, 5);
pos1 := dbms_lob.instr(x, 'indexed documents:');
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1,
pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'allocated docids:');
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, '$I rows:');
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'unique tokens:');
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'average $I rows per token:');
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'average size per token:');
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'average frequency per token:');
IF pos1 > 0 T
HEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'token type:');
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'unique tokens:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'total rows:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'average rows:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'total size:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END
IF;
pos1 := dbms_lob.instr(x, 'average size:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'average frequency:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'total size of $I data:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'estimated row fragmentation:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'garbage docids:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(statData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'estimated garbage size:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, ch
r(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
dbms_lob.writeappend(statData, pos2-pos1, tmp);
END IF;
/*dbms_lob.freetemporary(x); */
/* Gather Index Objects Data */
ctx_report.describe_index(idx_name, x);
pos1 := 1;
pos1 := dbms_lob.instr(x, 'datastore:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'filter:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'section group:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'lexer:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'wordlist:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x,
pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'stemmer:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'fuzzy_match:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'stoplist:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
pos1 := dbms_lob.instr(x, 'storage:', pos1);
IF pos1 > 0 THEN
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(objectsData, pos2-pos1+1, tmp);
END IF;
/* Gather Size Data */
ctx_report.index_size(idx_name, x, partitionName);
pos1 := 1;
LOOP
pos1 := dbms_lob.instr(x, 'INDEX (', pos1);
EXIT WHEN pos1 < 1;
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(sizeData, pos2-pos1+1, tmp);
pos1 := dbms_lob.instr(x, 'TABLE NAME:', pos1);
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(sizeData, pos2-pos1+1, tmp);
pos1 := dbms_lob.instr(x, 'TABLESPACE NAME:', pos1);
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(sizeData, pos2-pos1+1, tmp);
pos1 := dbms_lob.instr(x, 'BLOCKS ALLOCATED:', pos1);
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(sizeData, pos2-pos1+1, tmp);
pos1 := dbms_lob.instr(x, 'BLOCKS USED:', pos1);
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(sizeData, pos2-pos1+1, tmp);
pos1 := dbms_lob.instr(x, 'BYTES ALLOCATED:', pos1);
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(sizeData, pos2-pos1+1, tmp);
pos1 := dbms_lob.instr(x, 'BYTES USED:', pos1);
pos2 := dbms_lob.instr(x, chr(10), pos1);
tmp := dbms_lob.substr(x, pos2-pos1, pos1);
tmp := tmp || '|';
dbms_lob.writeappend(sizeData, pos2-pos1+1, tmp);
END LOOP;
OPEN data_cursor FOR
SELECT idx_name
, partitionName, dbms_lob.substr(statData), dbms_lob.substr(sizeData), dbms_lob.substr(objectsData) FROM DUAL;
:2 := data_cursor;
jobName := idx_rec.owner || '.' || idx_rec.job_name;
dbms_scheduler.drop_job(job_name => jobName, force => true);
dbms_lob.freetemporary(sizeData);
dbms_lob.freetemporary(statData);
dbms_lob.freetemporary(objectsData);
dbms_lob.freetemporary(x);
end if;
ELSE
OPEN data_cursor FOR
select 'dummy', 'dummy', 'dummy', 'dummy', 'dummy' from dual;
:2 := data_cursor;
END IF;
END;
|