% perlBin scriptsDir %perlBin%/perl %scriptsDir%/fullTbsp.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID targetTbspCondition targetSegCondition (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) % MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME name % MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME name % perlBin scriptsDir %perlBin%/perl %scriptsDir%/fullTbsp.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID targetTbspCondition targetSegCondition ='DICTIONARY' (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) perlBin scriptsDir %perlBin%/perl %scriptsDir%/problemTbsp.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID targetTbspCondition targetSegCondition (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) MachineName Port SID UserName password Role 0 AND ds_count > 0 UNION ALL SELECT tablespace_name, 4, 0, 0 FROM dba_tablespaces WHERE pct_increase != 0 AND extent_management = 'DICTIONARY' UNION ALL SELECT tablespace_name, 5, 0, 0 FROM dba_tablespaces WHERE extent_management = 'DICTIONARY' AND initial_extent > 0 AND MOD(next_extent, initial_extent) != 0 ]]> MachineName Port SID UserName password Role 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) AND decode (bitand (t.property, 1), 1, 1, 0) = 0 AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND decode (bitand (t.property, 1024), 1024, 1, 0) = 0 UNION ALL SELECT /*+ ordered */ 2, u.name, o.name, ' ', ' ', 2, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.ind$ i, sys.seg$ s,sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = i.obj# AND i.ts# = tts.ts# AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AND i.type# not in (4, 8) UNION ALL SELECT /*+ ordered */ 3, u.name, o.name, ' ', ' ', 12, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.clu$ c, sys.seg$ s, sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = c.obj# AND c.ts# = tts.ts# AND c.file# = s.file# AND c.block# = s.block# AND c.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', ' ', 1, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.ind$ i, sys.seg$ s, sys.tab$ t, sys.obj$ tn,sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = i.obj# AND i.ts# = tts.ts# AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND i.bo# = tn.obj# AND tn.obj# = t.obj# AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AND i.type# = 4 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, o.name, o.subname, ' ', 3, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabpart$ tp, sys.seg$ s, sys.tab$ t, sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = tp.obj# AND tp.ts# = tts.ts# AND tp.file# = s.file# AND tp.block# = s.block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 2, u.name, o.name, o.subname, ' ', 4, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indpart$ ip, sys.seg$ s, sys.ind$ i, sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = ip.obj# AND ip.ts# = tts.ts# AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# not in (4, 8) AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', ' ', 5, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tab$ t, sys.seg$ s, sys.tab$ iot,sys.obj$ tn, sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = t.obj# AND t.ts# = tts.ts# AND t.file# = s.file# AND t.block# = s.block# AND t.ts# = s.ts# AND tn.obj# = t.bobj# AND tn.obj# = iot.obj# AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', decode(bitand(c.property, 1), 1, substr(ac.name,0,500), c.name), 8, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.lob$ l, sys.seg$ s, sys.col$ c, sys.attrcol$ ac, sys.obj$ tn, sys.user$ u WHERE tn.owner# = u.user# AND tn.obj# = c.obj# AND c.obj# = l.obj# AND c.intcol# = l.intcol# AND l.ts# = tts.ts# AND l.file# = s.file# AND l.block# = s.block# AND l.ts# = s.ts# AND c.obj# = ac.obj#(+) AND c.intcol# = ac.intcol#(+) AND bitand(c.property,32768) != 32768 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, o.subname, ' ', 3, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indpart$ ip, sys.seg$ s, sys.ind$ i, sys.obj$ tn, sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = ip.obj# AND ip.ts# = tts.ts# AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# = 4 AND i.bo# = tn.obj# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tbo.name, o.subname, ' ', 13, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabpart$ tp, sys.seg$ s, sys.tab$ t, sys.obj$ tbo, sys.obj$ o, sys.user$ u WHERE o.owner# = u.user# AND o.obj# = tp.obj# AND tp.ts# = tts.ts# AND tp.file# = s.file# AND tp.block# = s.block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 32), 32, 1, 0) = 1 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 AND tbo.obj# = t.bobj# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tbo.name, po.subname, decode(bitand(c.property, 1), 1, substr(a.name,0,500), c.name), 8, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.lobfrag$ lf, sys.seg$ s, sys.lob$ l, sys.partobj$ pobj, sys.obj$ po, sys.obj$ tbo, sys.attrcol$ a, sys.col$ c, sys.user$ u WHERE tbo.owner# = u.user# AND pobj.obj# = tbo.obj# AND lf.ts# = tts.ts# AND lf.file# = s.file# AND lf.block# = s.block# AND lf.ts# = s.ts# AND mod(pobj.spare2, 256) = 0 AND tbo.obj# = c.obj# AND c.obj# = l.obj# AND c.intcol# = l.intcol# AND l.lobj# = lf.parentobj# AND lf.tabfragobj# = po.obj# AND bitand(c.property,32768) != 32768 AND c.obj# = a.obj#(+) AND c.intcol# = a.intcol#(+) AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, bo.name, o.subname, ' ', 9, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabsubpart$ tsp,sys.seg$ s, sys.obj$ bo,sys.obj$ o,sys.user$ u WHERE o.owner# = u.user# AND o.obj# = tsp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 2 AND tsp.ts# = tts.ts# AND tsp.file# = s.file# AND tsp.block# = s.block# AND tsp.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 2, u.name, bo.name, o.subname, ' ', 10, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indsubpart$ isp, sys.seg$ s, sys.user$ u, sys.obj$ o, sys.obj$ bo WHERE o.owner# = u.user# AND o.obj# = isp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 1 AND isp.ts# = tts.ts# AND isp.file# = s.file# AND isp.block# = s.block# AND isp.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) ) where USER_NAME NOT IN ('MGMT_VIEW','OUTLN','ANONYMOUS','DMSYS','LBACSYS','WKPROXY','TRACESVR','SCOTT','ADAMS','BLAKE','CLARK','JONES','HR','PM','OE','SH','IX') AND USER_NAME NOT IN ('CTXSYS','DMSYS','MDSYS','OLAPSYS','ORDPLUGINS','ORDSYS','PERFSTAT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','WKSYS','WMSYS','XDB') AND USER_NAME NOT LIKE 'FLOWS!_%' escape '!' AND rownum < 201 AND LENGTH(lob_column) <= 30 ]]> MachineName Port SID UserName password Role 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) AND decode (bitand (t.property, 1), 1, 1, 0) = 0 AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND decode (bitand (t.property, 1024), 1024, 1, 0) = 0 UNION ALL SELECT /*+ ordered */ 2, u.name, o.name, ' ', ' ', 2, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.ind$ i, sys.seg$ s,sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = i.obj# AND i.ts# = tts.ts# AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AND i.type# not in (4, 8) UNION ALL SELECT /*+ ordered */ 3, u.name, o.name, ' ', ' ', 12, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.clu$ c, sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = c.obj# AND c.ts# = tts.ts# AND c.file# = s.file# AND c.block# = s.block# AND c.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', ' ', 1, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.ind$ i, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" tn,sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = i.obj# AND i.ts# = tts.ts# AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND i.bo# = tn.obj# AND tn.obj# = t.obj# AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AND i.type# = 4 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, o.name, o.subname, ' ', 3, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabpart$ tp, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = tp.obj# AND tp.ts# = tts.ts# AND tp.file# = s.file# AND tp.block# = s.block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 2, u.name, o.name, o.subname, ' ', 4, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indpart$ ip, sys.seg$ s, sys.ind$ i, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = ip.obj# AND ip.ts# = tts.ts# AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# not in (4, 8) AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', ' ', 5, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tab$ t, sys.seg$ s, sys.tab$ iot,sys."_CURRENT_EDITION_OBJ" tn, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = t.obj# AND t.ts# = tts.ts# AND t.file# = s.file# AND t.block# = s.block# AND t.ts# = s.ts# AND tn.obj# = t.bobj# AND tn.obj# = iot.obj# AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', decode(bitand(c.property, 1), 1, substr(ac.name,0,500), c.name), 8, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.lob$ l, sys.seg$ s, sys.col$ c, sys.attrcol$ ac, sys."_CURRENT_EDITION_OBJ" tn, sys.user$ u WHERE tn.owner# = u.user# AND u.type# !=2 AND tn.obj# = c.obj# AND c.obj# = l.obj# AND c.intcol# = l.intcol# AND l.ts# = tts.ts# AND l.file# = s.file# AND l.block# = s.block# AND l.ts# = s.ts# AND c.obj# = ac.obj#(+) AND c.intcol# = ac.intcol#(+) AND bitand(c.property,32768) != 32768 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, o.subname, ' ', 3, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indpart$ ip, sys.seg$ s, sys.ind$ i, sys."_CURRENT_EDITION_OBJ" tn, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = ip.obj# AND ip.ts# = tts.ts# AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# = 4 AND i.bo# = tn.obj# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tbo.name, o.subname, ' ', 13, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabpart$ tp, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" tbo, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = tp.obj# AND tp.ts# = tts.ts# AND tp.file# = s.file# AND tp.block# = s.block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 32), 32, 1, 0) = 1 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 AND tbo.obj# = t.bobj# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tbo.name, po.subname, decode(bitand(c.property, 1), 1, substr(a.name,0,500), c.name), 8, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.lobfrag$ lf, sys.seg$ s, sys.lob$ l, sys.partobj$ pobj, sys."_CURRENT_EDITION_OBJ" po, sys."_CURRENT_EDITION_OBJ" tbo, sys.attrcol$ a, sys.col$ c, sys.user$ u WHERE tbo.owner# = u.user# AND u.type# !=2 AND pobj.obj# = tbo.obj# AND lf.ts# = tts.ts# AND lf.file# = s.file# AND lf.block# = s.block# AND lf.ts# = s.ts# AND mod(pobj.spare2, 256) = 0 AND tbo.obj# = c.obj# AND c.obj# = l.obj# AND c.intcol# = l.intcol# AND l.lobj# = lf.parentobj# AND lf.tabfragobj# = po.obj# AND bitand(c.property,32768) != 32768 AND c.obj# = a.obj#(+) AND c.intcol# = a.intcol#(+) AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, bo.name, o.subname, ' ', 9, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabsubpart$ tsp,sys.seg$ s, sys."_CURRENT_EDITION_OBJ" bo,sys."_CURRENT_EDITION_OBJ" o,sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = tsp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 2 AND tsp.ts# = tts.ts# AND tsp.file# = s.file# AND tsp.block# = s.block# AND tsp.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 2, u.name, bo.name, o.subname, ' ', 10, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#,name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags,3)=0)) AND bitand(flags,2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indsubpart$ isp, sys.seg$ s, sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" bo WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = isp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 1 AND isp.ts# = tts.ts# AND isp.file# = s.file# AND isp.block# = s.block# AND isp.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) ) where rownum < 201 AND LENGTH(lob_column) <= 30]]> MachineName Port SID UserName password Role SystemTs# SysauxTs# MachineName Port SID UserName password Role SystemTs# SysauxTs# MachineName Port SID UserName password Role perlBin scriptsDir metricName %perlBin%/perl %scriptsDir%/spacePurge.pl %metricName% em_result= | perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dg/dgDatabases.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory perlBin scriptsDir AL32UTF8 %perlBin%/perl %scriptsDir%/db/dg/dgDatabases.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dg/dgGetPrimary.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory perlBin scriptsDir AL32UTF8 %perlBin%/perl %scriptsDir%/db/dg/dgDatabases.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dg/dgObserverStatus.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory = db_version_11) then select count(1) into l_valid_count from v$database where database_role='PRIMARY' and FS_FAILOVER_STATUS !='DISABLED'; if l_valid_count > 0 then l_valid_count := sys.dbms_drs.get_property_obj(0, 'ObserverHB'); else l_valid_count := -1; end if; end if; if l_valid_count = -1 then obs_status := 'Fast-start failover disabled'; elsif l_valid_count = 0 then select fs_failover_observer_host into l_observer_host from v$database; obs_status := 'Observer is running normally on ' || l_observer_host; elsif l_valid_count > 0 then obs_status := 'Error Fast-Start Failover observer is no longer observing this database'; else obs_status := 'Error ' || l_valid_count; end if; open data_cursor for select obs_status from dual; :1 := data_cursor; end; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dg/dgPerf.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory = db_version_102) THEN SELECT max(to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS')) time INTO l_fsfo_time from v$dataguard_status where message='Switchover: Complete - Database mounted as primary'; SELECT STANDBY_BECAME_PRIMARY_SCN,initcap(FS_FAILOVER_STATUS),database_role into p_scn,l_status,l_role from v$database; -- If FSFO status is reinstate required, then we know an FSFO occurred. IF (l_role = 'PRIMARY' AND (instr(l_status,'Reinstate',1) != 0)) THEN -- An FSFO occurred, make sure there is a valid timestamp. If not (e.g. bounce required) use the startup time. IF (l_fsfo_time IS NULL OR (length(l_fsfo_time) = 0)) THEN SELECT to_char(startup_time, 'YYYY-MM-DD HH24:MI:SS') time INTO l_fsfo_time from v$instance; END IF; l_scn := p_scn; END IF; END IF; OPEN data_cursor FOR SELECT l_scn, l_fsfo_time, l_status from dual; :1 := data_cursor; END; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dg/dgCollector.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory perlBin scriptsDir %perlBin%/perl %scriptsDir%/db/dg/dgCollector.pl em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory TRUE MachineName Port SID UserName password Role MachineName Port SID UserName password Role = 1 THEN OPEN data_cursor FOR 'SELECT /*+ all_rows */ o.type# object_type, u.name object_owner, o.name object_name ' || 'FROM sys.obj$ o, sys.user$ u ' || 'WHERE u.user# = o.owner# AND o.status NOT IN (0,1) AND o.type# NOT IN (28,29,30) ' || 'AND ( ((u.name=''SYS'' or u.name=''PUBLIC'') ' || 'and (o.name NOT LIKE ''DBA_HIST_%'' and o.name != ''DBMS_SWRF_REPORT_INTERNAL'')) ' || 'or (u.name !=''SYS'' and u.name != ''PUBLIC'') ) ' || 'UNION ALL ' || 'SELECT * FROM ( ' || 'SELECT o.type# object_type, u.name object_owner, nvl(l.longname, o.name) object_name ' || 'FROM sys.obj$ o, javasnm l, sys.user$ u ' || 'WHERE o.name = l.short(+) AND o.type# IN (28,29,30) AND o.owner# = u.user# AND o.status NOT IN (0,1)) ' || 'WHERE LENGTH(object_name) <= 1000'; ELSE OPEN data_cursor FOR 'SELECT /*+ all_rows */ o.type# object_type, u.name object_owner, o.name object_name ' || 'FROM sys.obj$ o, sys.user$ u ' || 'WHERE u.user# = o.owner# AND o.status NOT IN (0,1) ' || 'AND ( ((u.name=''SYS'' or u.name=''PUBLIC'') ' || 'and (o.name NOT LIKE ''DBA_HIST_%'' and o.name != ''DBMS_SWRF_REPORT_INTERNAL'')) ' || 'or (u.name !=''SYS'' and u.name != ''PUBLIC'') ) '; END IF; :1 := data_cursor; END; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role = 1 THEN OPEN data_cursor FOR 'SELECT /*+ all_rows */ o.type# object_type, u.name object_owner, o.name object_name ' || 'FROM sys."_CURRENT_EDITION_OBJ" o, sys.user$ u ' || 'WHERE u.user# = o.owner# AND o.status NOT IN (0,1) AND o.type# NOT IN (28,29,30) ' || 'AND u.type# !=2 ' || 'AND ( ((u.name=''SYS'' or u.name=''PUBLIC'') ' || 'and (o.name NOT LIKE ''DBA_HIST_%'' and o.name != ''DBMS_SWRF_REPORT_INTERNAL'')) ' || 'or (u.name !=''SYS'' and u.name != ''PUBLIC'') ) ' || 'UNION ALL ' || 'SELECT * FROM ( ' || 'SELECT o.type# object_type, u.name object_owner, nvl(l.longname, o.name) object_name ' || 'FROM sys."_CURRENT_EDITION_OBJ" o, javasnm l, sys.user$ u ' || 'WHERE o.name = l.short(+) AND o.type# IN (28,29,30) AND u.type# !=2 AND o.owner# = u.user# ' || 'AND o.status NOT IN (0,1)) ' || 'WHERE LENGTH(object_name) <= 1000'; ELSE OPEN data_cursor FOR 'SELECT /*+ all_rows */ o.type# object_type, u.name object_owner, o.name object_name ' || 'FROM sys."_CURRENT_EDITION_OBJ" o, sys.user$ u ' || 'WHERE u.user# = o.owner# AND o.status NOT IN (0,1) ' || 'AND u.type# !=2 ' || 'AND ( ((u.name=''SYS'' or u.name=''PUBLIC'') ' || 'and (o.name NOT LIKE ''DBA_HIST_%'' and o.name != ''DBMS_SWRF_REPORT_INTERNAL'')) ' || 'or (u.name !=''SYS'' and u.name != ''PUBLIC'') ) '; END IF; :1 := data_cursor; END; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role perlBin scriptsDir tsNames schemas wasteMinValue rcMinValue ckWasteSpc ckRowChain minSegSize %perlBin%/perl %scriptsDir%/wastedSpace.pl %tsNames% %schemas% %wasteMinValue% %rcMinValue% %ckWasteSpc% %ckRowChain% %minSegSize% em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory MachineName Port SID UserName password Role MachineName Port SID UserName password monitorUser Role 2 SQL_CURSOR ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME Object_Causing 0 AND space_limit >= space_used AND space_used >= space_reclaimable THEN 100 * (space_limit - (space_used - space_reclaimable))/space_limit ELSE NULL END free_space FROM v$recovery_file_dest WHERE name IS NOT NULL AND rownum = 1 ]]> MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME Recovery_Area ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME Object_Causing MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role 0 then case when (select value from v$option where parameter ='Oracle Database Vault') ='TRUE' then 1 -- Enabled else 0 --Disabled end when (select count(*) from dba_users where username ='DVSYS' and user_id = 1279990) = 0 then case when (select value from v$option where parameter ='Oracle Database Vault') = 'TRUE' then -1 -- Not Configured else -2 -- Not Installed end end dv_status_code FROM v$database a, v$version b, v$instance c, global_name e, v$nls_parameters nlsp1, v$nls_parameters nlsp2, database_properties p WHERE b.banner LIKE '%Oracle%' AND nlsp1.parameter = 'NLS_CHARACTERSET' AND nlsp2.parameter = 'NLS_NCHAR_CHARACTERSET' AND p.property_name = 'DEFAULT_TEMP_TABLESPACE' ]]> MachineName Port SID UserName password Role MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%/oracle_db_files.pl db_redologs em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome agentStateDir perlBin scriptsDir %perlBin%/perl %scriptsDir%/oracle_db_files.pl db_datafiles em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome agentStateDir perlBin scriptsDir %perlBin%/perl %scriptsDir%/oracle_db_files.pl db_datafiles_10gR2 em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome agentStateDir perlBin scriptsDir %perlBin%/perl %scriptsDir%/oracle_db_files.pl db_datafiles_11gR2 em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome agentStateDir MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%/oracle_db_files.pl db_controlfiles em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome agentStateDir MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role % % % = db_version_102) THEN IF (length(l_flash_recovery_area) > 0) THEN EXECUTE IMMEDIATE 'SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED FROM V$FLASH_RECOVERY_AREA_USAGE)' INTO l_usable_area; EXECUTE IMMEDIATE 'SELECT SUM(PERCENT_SPACE_USED),SUM(PERCENT_SPACE_RECLAIMABLE) from v$flash_recovery_area_usage' INTO l_space_used, l_reclaimable_area; l_nonreclaimable_area := l_space_used - l_reclaimable_area; END IF; END IF; -- Oldest Flashback Time BEGIN SELECT to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') INTO l_oldest_flashback_time FROM v$flashback_database_log; EXCEPTION WHEN OTHERS THEN l_oldest_flashback_time := ''; END; OPEN data_cursor FOR SELECT l_log_mode, l_flash_recovery_area, l_usable_area, l_reclaimable_area, l_nonreclaimable_area, l_flashback_on, l_oldest_flashback_time, l_flash_recovery_size from dual; :1 := data_cursor; END; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role = db_version_92) THEN EXECUTE IMMEDIATE 'SELECT nvl(sum(blocks), 0) FROM v$database_block_corruption' INTO l_corrupt_data_blocks; END IF; OPEN data_cursor FOR SELECT l_missing_media_files, l_corrupt_data_blocks, l_datafiles_need_recovery from dual; :1 := data_cursor; END; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role 'EXECUTE' ]]> MachineName Port SID UserName password Role MachineName Port SID UserName password Role 9 ' || 'AND U.astatus = M.status# ' || 'AND U.type# = 1 ' || 'AND rownum <= '||'decode( '||max_row||', ''-1'' , 2147483647, '||max_row||') '; oms_ver_short := substr(oms_ver,1,4) ; if oms_ver_short = '10.1' then sql_stmt2 := 'select ''default user account'', '||'U.name || ''('' || LOWER( M.status ) || '')'' '||sql_stmt1 ; else sql_stmt2 := 'select ''default_user_account'', U.name, M.status '||sql_stmt1 ; end if ; OPEN data_cursor FOR sql_stmt2; :3 := data_cursor; END ; ]]> well_known_ac OMS_VERSION 3 SQL_CURSOR MachineName Port SID UserName password Role %scriptsDir%/db/dfltAccPwd.sql default_pwd MachineName Port SID UserName password Role pub_sys_priv MachineName Port SID UserName password Role OMS_VERSION public_execute_UTL_max public_execute_UTL_SMTP_max public_execute_UTL_HTTP_max public_execute_UTL_TCP_max 6 SQL_CURSOR MachineName Port SID UserName password Role ''DEFAULT'' union SELECT ''Unlimited_login_attempts'',up.profile,def.limit FROM dba_profiles up,dba_profiles def where up.resource_name = ''FAILED_LOGIN_ATTEMPTS'' AND up.resource_name = def.resource_name AND up.limit=def.profile AND up.limit=''DEFAULT'' ) '|| 'WHERE rownum <= '||'decode( '||max_row||', ''-1'' , 2147483647, '||max_row||') '|| 'ORDER BY profile' ; END IF ; OPEN data_cursor FOR sql_stmt; :4 := data_cursor; END ; ]]> failed_login_at OMS_VERSION 4 SQL_CURSOR MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%/segAdv.pl em_result= | NAME UserName password Role OracleHome agentStateDir GUID MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory MachineName Port SID UserName password Role MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl EXEMPT_ACCESS_POLICY em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'EXEMPT_ACCESS_POLICY' from ( select null p, name c from system_privilege_map where name = 'EXEMPT ACCESS POLICY' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl DBA_ROLE em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'DBA_ROLE' from ( select null p, role c from dba_roles where role in ('DBA') union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl CREATE_PRIVILEGE em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'CREATE_PRIVILEGE' from ( select null p, name c from system_privilege_map where name like '%CREATE%' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl CATALOG_ROLE em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'CATALOG_ROLE' from ( select null p, role c from dba_roles where role like '%_CATALOG_%' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl BECOME_USER em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'BECOME_USER' from ( select null p, name c from system_privilege_map where name = 'BECOME USER' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl AUDIT_SYSTEM em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'AUDIT_SYSTEM' from ( select null p, name c from system_privilege_map where name = 'AUDIT SYSTEM' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl ANY_DICTIONARY em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'ANY_DICTIONARY' from ( select null p, name c from system_privilege_map where name like '%ANY DICTIONARY%' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl CONNECT_PRIVILEGE em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'CONNECT_PRIVILEGE' from ( select null p, role c from dba_roles where role in ('RESOURCE', 'CONNECT') union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl ALL_PRIVILEGES em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'ALL_PRIVILEGES' from ( select null p, name c from system_privilege_map where name = 'GRANT ANY PRIVILEGE' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl ANY_PRIVILEGE em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'ANY_PRIVILEGE' from ( select null p, name c from system_privilege_map where name like '%ANY%' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) start with p is null connect by p = prior c ) where rownum < 200 ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/esaPrivReports.pl POWER_PRIVILEGE em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome '),3,512) path, c, 'POWER_PRIVILEGE' from ( select null p, name c from system_privilege_map where name in ('ALTER SESSION','ALTER SYSTEM','CREATE PROCEDURE','CREATE LIBRARY') union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum < 200 start with p is null connect by p = prior c ]]> MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/dbaGroupAssign.pl em_result= | NAME Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role db_link_pwd MachineName Port SID UserName password Role dba_ts_quotas MachineName Port SID UserName password Role 'DEFAULT' union select 'password_life_time',def.limit,up.profile FROM dba_profiles up,dba_profiles def where up.resource_name = 'PASSWORD_LIFE_TIME' AND up.resource_name = def.resource_name AND up.limit=def.profile AND up.limit='DEFAULT' ) where rownum <= decode((select life_time from pwd),'-1',2147483647,(select life_time from pwd)) union select 'password_reuse_max',limit,profile from ( SELECT 'password_reuse_max',limit,profile FROM dba_profiles WHERE resource_name = 'PASSWORD_REUSE_MAX' AND limit <>'DEFAULT' union select 'password_reuse_max',def.limit,up.profile FROM dba_profiles up,dba_profiles def where up.resource_name = 'PASSWORD_REUSE_MAX' AND up.resource_name = def.resource_name AND up.limit=def.profile AND up.limit='DEFAULT' ) where rownum <= decode((select reuse_max from pwd),'-1',2147483647,(select reuse_max from pwd)) union select 'password_reuse_time',limit,profile from( SELECT 'password_reuse_time',limit,profile FROM dba_profiles WHERE resource_name = 'PASSWORD_REUSE_TIME' AND limit <>'DEFAULT' union select 'password_reuse_time',def.limit,up.profile FROM dba_profiles up,dba_profiles def where up.resource_name = 'PASSWORD_REUSE_TIME' AND up.resource_name = def.resource_name AND up.limit=def.profile AND up.limit='DEFAULT' ) where rownum <= decode((select reuse_time from pwd),'-1',2147483647,(select reuse_time from pwd)) union select 'password_lock_time',limit,profile from ( SELECT 'password_lock_time',limit,profile FROM dba_profiles WHERE resource_name = 'PASSWORD_LOCK_TIME' AND limit <>'DEFAULT' union select 'password_lock_time',def.limit,up.profile FROM dba_profiles up,dba_profiles def where up.resource_name = 'PASSWORD_LOCK_TIME' AND up.resource_name = def.resource_name AND up.limit=def.profile AND up.limit='DEFAULT' ) where rownum <= decode((select lock_time from pwd),'-1',2147483647,(select lock_time from pwd)) union SELECT 'password_grace_time',limit,profile from( SELECT 'password_grace_time',limit,profile FROM dba_profiles WHERE resource_name = 'PASSWORD_GRACE_TIME' AND limit <>'DEFAULT' union select 'password_grace_time',def.limit,up.profile FROM dba_profiles up,dba_profiles def where up.resource_name = 'PASSWORD_GRACE_TIME' AND up.resource_name = def.resource_name AND up.limit=def.profile AND up.limit='DEFAULT' ) where rownum <= decode((select grace_time from pwd),'-1',2147483647,(select grace_time from pwd)) union select 'password_complexity_fn',limit,profile from( SELECT 'password_complexity_fn',limit,profile FROM sys.dba_profiles WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION' AND limit <>'DEFAULT' UNION SELECT 'password_complexity_fn',def.limit,up.profile FROM dba_profiles up,dba_profiles def WHERE up.resource_name = 'PASSWORD_VERIFY_FUNCTION' AND up.resource_name = def.resource_name AND up.limit=def.profile AND up.limit='DEFAULT' ) where rownum <= decode((select pwd_cmplx_fn from pwd),'-1',2147483647,(select pwd_cmplx_fn from pwd)) ]]> password_life_time password_reuse_max password_reuse_time password_lock_time password_grace_time password_complexity_fn MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/dbDataFilePerm.pl db_data_perm nt_db_data_perm em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/dbControlFilesPerm.pl db_control nt_db_control em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome perlBin scriptsDir %perlBin%/perl %scriptsDir%//db/tableAccess.pl access_aud_table_max access_user_history_max access_source_table_max access_link_table_max access_user_table_max access_sql_text_max access_sql_summary_max access_all_source_max access_dba_roles_max access_dba_sysprivs_max access_dba_roleprivs_max access_dba_tabprivs_max access_dba_users_max access_role_roleprivs_max access_user_tabprivs_max access_user_roleprivs_max em_result= | NAME UserName password Role MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) OracleHome insert_failure_max MachineName Port SID UserName password Role '),3,512) path, c from ( select null p, name c from system_privilege_map where name = 'SELECT ANY TABLE' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum <= decode((select select_tab from max1),'-1',2147483647,(select select_tab from max1)) start with p is null connect by p = prior c union select 'select_privilege', substr(SYS_CONNECT_BY_PATH(c, '->'),3,512) path, c from ( select null p, view_name c from dba_views where view_name like 'DBA_%' union select granted_role p, grantee c from dba_role_privs union select table_name p, grantee c from dba_tab_privs where privilege = 'SELECT' ) where ((c = 'PUBLIC') or (exists (select 'w' from dba_users where username=c))) and rownum <= decode((select select_priv from max1),'-1',2147483647,(select select_priv from max1)) start with p is null connect by p = prior c ]]> select_table_max select_privilege_max MachineName Port SID UserName password Role 'CREATE SESSION' union select granted_role p, grantee c from dba_role_privs union select privilege p, grantee c from dba_sys_privs ) where exists (select 'w' from proxy_users where proxy = c) start with p is null connect by p = prior c) where rownum <= :1 order by c ]]> proxy_account_max MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%/failedLogin.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory currentIntervalMin perlBin scriptsDir %perlBin%/perl %scriptsDir%/failedLogin.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory agentStateDir GUID HISTORICAL updateStateFile 293 THEN ret_msg := 'User Defined Metric ' || udm_id || ' returned a value of ' || SUBSTR(string_value, 0, 292) || '.'; ELSE ret_msg := 'User Defined Metric ' || udm_id || ' returned a value of ' || string_value || '.'; END IF; OPEN data_cursor FOR SELECT udm_id, null, string_value, ret_msg FROM DUAL; ELSE -- WRONG TYPE err_msg := udm_id || ': Wrong type!'; raise_application_error(-20101, err_msg); END IF; :4 := data_cursor; END; ]]> ID valuetype sqlstmt 4 SQL_CURSOR UserName password Role MachineName Port SID ID sqlstmt keytype 4 SQL_CURSOR UserName password Role MachineName Port SID valuetype ID sqlstmt keytype 4 SQL_CURSOR UserName password Role MachineName Port SID valuetype OracleHome perlBin scriptsDir ARG SWEEP OracleHome SID AdrBase AdrHome agentStateDir %perlBin%/perl -I %OracleHome%/lib %scriptsDir%/adrviewer.pl "%ARG%" em_result= ARGLIST 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 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,'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,chr(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; ]]> GUID 2 SQL_CURSOR UserName password Role MachineName Port SID 1 SQL_CURSOR MachineName Port SID UserName password Role 0; SELECT NVL(SUM(FAILURES), 0) into total_prop_errors FROM SYS.DBA_QUEUE_SCHEDULES; sqlstmt := 'select '||cap_count||' CAPTURE_COUNT, '||apply_count||' APPLY_COUNT, '||propagation_count||' PROP_COUNT, '||cap_error_count||' CAPTURE_ERROR_COUNT, '||apply_error_count||' APPLY_ERROR_COUNT,'||prop_error_count||' PROP_ERROR_COUNT,'||total_prop_errors||' TOTAL_PROP_ERRORS from dual'; OPEN data_cursor FOR sqlstmt; :1 := data_cursor; end; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role 1 SQL_CURSOR MachineName Port SID UserName password Role 1 SQL_CURSOR MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME capture_aborted ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME apply_aborted ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME propagation_aborted Do not include sys/system/sysman/dbsnmp schema as these might contain system queues for rec in (select owner, name, queue_table from dba_queues where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') ) LOOP dbms_output.put_line('+++++ Begin Processing for: ' ||rec.owner||' , '||rec.name|| ', '||rec.queue_table); begin -- Calculate the average message age per queue and store them in the AQ_MNTR_MSGS_QUEUE table begin fromClause := rec.owner||'.AQ$'||rec.queue_table; finalQry := 'select queue, avg(sysdate-ENQ_TIME) * 86400 AVERAGE_MSG_AGE from ' || fromClause ||' group by queue' ; open test_cursor for finalQry; loop lc_var1 := ''; lc_var2 := 0; fullQName := ''; fetch test_cursor into lc_var1 ,lc_var2 ; fullQName := rec.owner||'.'||lc_var1; begin IF lc_var2 != 0 THEN totalProcessed := null; totalReceived := null; finalPerc := null; execute immediate 'insert into AQ_MNTR_MSGS_QUEUE values (:1, :2, :3, :4, :5, :6, :7)' using fullQName, rec.owner, lc_var1, lc_var2, totalProcessed, totalReceived, finalPerc; commit; END IF; exception WHEN OTHERS THEN rollback; DBMS_OUTPUT.PUT_LINE(' ***** Unique constaint error ***** '); end; exit when test_cursor %notfound; dbms_output.put_line('FETCHED DATA: ' ||lc_var1||' , '||lc_var2|| ', '||fullQName); end loop; lc_var1 := ''; lc_var2 := 0; fullQName := ''; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating average age ***** '); end; -- Average message age per queue end -- Messages processed % per queue. Calculate and update the AQ_MNTR_MSGS_QUEUE table begin fullQName := ''; fullQName := rec.owner||'.'||rec.name; begin select B.CNUM_MSGS receivedB, B.CNUM_MSGS-B.EXPIRED_MSGS-B.NUM_MSGS processedB INTO receivedB, processedB from V$BUFFERED_QUEUES B where B.QUEUE_SCHEMA = rec.owner and B.QUEUE_NAME = rec.name; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** FIRST QUERY RETURNED NO DATA***** '); end; begin select P.ENQUEUED_MSGS receivedP , P.DEQUEUED_MSGS processedP INTO receivedP, processedP from V$PERSISTENT_QUEUES P where P.QUEUE_SCHEMA = rec.owner and P.QUEUE_NAME = rec.name; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** SECOND QUERY RETURNED NO DATA***** '); end; totalProcessed := processedB + processedP; totalReceived := receivedB + receivedP; IF totalReceived != 0 THEN finalPerc := (totalProcessed/totalReceived) * 100; execute immediate 'select count(*) from AQ_MNTR_MSGS_QUEUE where schema = :1 and queue_name = :2' into lc_var3 using rec.owner, rec.name; IF lc_var3 = 0 THEN avg_msg_age := null; execute immediate 'insert into AQ_MNTR_MSGS_QUEUE values (:1, :2, :3, :4, :5, :6, :7)' using fullQName, rec.owner, rec.name, avg_msg_age, totalProcessed, totalReceived, finalPerc; ELSE execute immediate 'update AQ_MNTR_MSGS_QUEUE set totalProcessed = :1, totalReceived = :2, proc_perc = :3 where FULL_QUEUE_NAME = :4 ' using totalProcessed, totalReceived, finalPerc, fullQName; END IF; commit; END IF; -- Reset after using receivedB := 0; processedB := 0; receivedP := 0; processedP := 0; totalReceived := 0; totalProcessed := 0; finalPerc := 0; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating processed % ***** '); end; -- Messages processed % per queue end exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in main for loop ***** '); end; dbms_output.put_line('+++++ END Processing +++++ '); end loop; lv_sql_stmt:='select schema, queue_name, average_msg_age, totalProcessed, totalReceived, proc_perc from AQ_MNTR_MSGS_QUEUE'; open data_cursor for lv_sql_stmt; :1 := data_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('>>>>>> Exception in main begin block <<<<<<<'); end; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role Do not include sys/system/sysman/dbsnmp schema as these might contain system queues for rec in (select owner, name, queue_table from dba_queues where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') ) LOOP dbms_output.put_line('+++++ Begin Processing for: ' ||rec.owner||' , '||rec.name|| ', '||rec.queue_table); begin -- Calculate the average message age per queue and store them in the AQ_MNTR_MSGS_SUBS table begin fromClause := rec.owner||'.AQ$'||rec.queue_table; finalQry := 'select queue, consumer_name, address, avg(sysdate-ENQ_TIME) * 86400 AVERAGE_MSG_AGE from ' || fromClause ||' group by queue, consumer_name, address' ; DBMS_OUTPUT.PUT_LINE(finalQry); begin open test_cursor for finalQry; loop lc_var1 := ''; lc_var2 := ''; lc_var3 := ''; lc_var4 := 0; pk := ''; fetch test_cursor into lc_var1 ,lc_var2, lc_var3, lc_var4; pk := rec.owner||':'||lc_var1||':'||lc_var2||':'||lc_var3; DBMS_OUTPUT.PUT_LINE('pk1: '||pk); begin IF lc_var4 != 0 THEN totalProcessed := null; totalReceived := null; finalPerc := null; execute immediate 'insert into AQ_MNTR_MSGS_SUBS values (:1, :2, :3, :4, :5, :6, :7, :8, :9)' using pk, rec.owner, lc_var1, lc_var2, lc_var3, lc_var4, totalProcessed, totalReceived, finalPerc; commit; END IF; exception WHEN OTHERS THEN rollback; DBMS_OUTPUT.PUT_LINE(' ***** Unique constaint error ***** '); end; exit when test_cursor %notfound; dbms_output.put_line('FETCHED DATA: ' ||lc_var1||' , '||lc_var2|| ', '||lc_var3||', '||lc_var4||', '||pk); end loop; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Could not open cursor ***** '); end; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating average age ***** '); end; -- Average message age per queue end -- Messages processed % per queue per subscriber. Calculate and update the AQ_MNTR_MSGS_SUBS table begin qtable := rec.owner||'.AQ$_'||rec.queue_table||'_S'; qry1 := 'select subscriber_id, name, address from ' || qtable || ' where queue_name='''||rec.name||''''; DBMS_OUTPUT.PUT_LINE(qry1); open test_cursor for qry1; loop subs_id := 0; subs_name := ''; subs_address := ''; fetch test_cursor into subs_id, subs_name, subs_address; pk := rec.owner||':'||rec.name||':'||subs_name||':'||subs_address; DBMS_OUTPUT.PUT_LINE('pk2: '||pk); begin begin execute immediate 'SELECT ENQUEUED_MSGS, DEQUEUED_MSGS from V$PERSISTENT_SUBSCRIBERS where QUEUE_SCHEMA = :1 and QUEUE_NAME = :2 and SUBSCRIBER_ID = :3 ' into receivedP, processedP using rec.owner, rec.name, subs_id; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** FIRST QUERY RETURNED NO DATA***** '); end; begin execute immediate 'SELECT CNUM_MSGS, (CNUM_MSGS-EXPIRED_MSGS-NUM_MSGS) from V$BUFFERED_SUBSCRIBERS where QUEUE_SCHEMA = :1 and QUEUE_NAME = :2 and SUBSCRIBER_ID = :3 ' into receivedB, processedB using rec.owner, rec.name, subs_id; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** SECOND QUERY RETURNED NO DATA***** '); end; totalProcessed := processedB + processedP; totalReceived := receivedB + receivedP; IF totalReceived != 0 THEN finalPerc := (totalProcessed/totalReceived) * 100; execute immediate 'select count(*) from AQ_MNTR_MSGS_SUBS where schema = :1 and queue_name = :2' into lc_var5 using rec.owner, rec.name; IF lc_var5 = 0 THEN avg_msg_age := null; execute immediate 'insert into AQ_MNTR_MSGS_SUBS values (:1, :2, :3, :4, :5, :6, :7, :8, :9)' using pk, rec.owner, rec.name, subs_name, subs_address, avg_msg_age, totalProcessed, totalReceived, finalPerc; ELSE execute immediate 'update AQ_MNTR_MSGS_SUBS set totalProcessed = :1, totalReceived = :2, proc_perc = :3 where ID = :4 ' using totalProcessed, totalReceived, finalPerc, pk; END IF; commit; END IF; -- Reset after using pk := ''; receivedB := 0; processedB := 0; receivedP := 0; processedP := 0; totalReceived := 0; totalProcessed := 0; finalPerc := 0; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Insert into AQ_MNTR_MSGS_SUBS failed might be due to unique key constraint ***** '); end; exit when test_cursor %notfound; end loop; close test_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating processed % ***** '); end; -- Messages processed % per queue end exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in main for loop ***** '); end; dbms_output.put_line('+++++ END Processing +++++ '); end loop; lv_sql_stmt:='select schema, queue_name, subs_name, subs_address, average_msg_age_subs, totalProcessed, totalReceived, proc_perc from AQ_MNTR_MSGS_SUBS'; open data_cursor for lv_sql_stmt; :1 := data_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('>>>>>> Exception in main begin block <<<<<<<'); end; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role Do not include sys/system/sysman/dbsnmp schema as these might contain system queues for rec in (select owner, name, queue_table from dba_queues where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') ) loop DBMS_OUTPUT.PUT_LINE('+++++ BEGIN +++++'); begin qtable := rec.owner||'.AQ$_'||rec.queue_table||'_S'; qtable2 := rec.owner||'.AQ$_'||rec.queue_table||'_I'; qtable3 := rec.owner||'.AQ$'||rec.queue_table; DBMS_OUTPUT.PUT_LINE(qtable||','||qtable2||','||qtable3||','||rec.name); -- initialise to null b4 usage msgids := msgid_tab(); n := 0; finalMsgIds := ''; qry1 := 'select subscriber_id, name, address from ' || qtable || ' where queue_name='''||rec.name||''''; open test_cursor for qry1; loop subs_id := ''; subs_name := ''; subs_address := ''; fetch test_cursor into subs_id, subs_name, subs_address; DBMS_OUTPUT.PUT_LINE(subs_id||','||subs_name||','||subs_address); pk := rec.owner||':'||rec.name||':'||subs_name||':'||subs_address; begin subs_msgid := ''; qry2 := 'select msgid from ' || qtable2 || ' where SUBSCRIBER# = '|| subs_id ||' and rownum <=1'; open test_cursor2 for qry2; fetch test_cursor2 into subs_msgid; close test_cursor2; subs_msgid := nvl(subs_msgid, 'EMPTY'); IF subs_msgid != 'EMPTY' THEN msgids.EXTEND(1); n := n+1; msgids(n) := subs_msgid; DBMS_OUTPUT.PUT_LINE('Message fetched: '||subs_msgid); DBMS_OUTPUT.PUT_LINE('Message inserted into msgids(n): '||msgids(n)); END IF; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** construction of msgid array might have failed ***** '); end; exit when test_cursor %notfound; end loop; close test_cursor; DBMS_OUTPUT.PUT_LINE('Messages count: '||msgids.count); /* Do all the processing only if there are messages in the queue/subscriber */ /* Processing BEGIN */ IF msgids.count > 0 THEN begin qtable4 := 'AQ$_'||rec.queue_table||'_I'; ind_col_count := 0; sort_order := ''; EXECUTE IMMEDIATE 'select column_name from dba_ind_columns where TABLE_OWNER = :1 and table_name = :2 and index_name like ''%TOP%''' BULK COLLECT INTO cols using rec.owner, qtable4; if(cols.last <> 0) THEN FOR i IN cols.first..cols.last loop if i=4 then sort_order := cols(i)||','; elsif i=cols.last then sort_order := sort_order || cols(i); elsif i > 4 and i < cols.last then sort_order := sort_order||cols(i)||','; end if; end loop; end if; DBMS_OUTPUT.PUT_LINE('+++++$$$$$: Final sort_order: '||sort_order); exception WHEN others THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('Error in calculating sort order....'); end; begin finalMsgIds := ''; FOR k IN 1..msgids.count loop if k = msgids.count then finalMsgIds := finalMsgIds ||DBMS_ASSERT.ENQUOTE_LITERAL(msgids(k)); else finalMsgIds := finalMsgIds ||DBMS_ASSERT.ENQUOTE_LITERAL(msgids(k))||','; end if; end loop; DBMS_OUTPUT.PUT_LINE('+++++$$$$$: finalMsgIds: '||finalMsgIds ); exception WHEN others THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('Error in calculating finalMsgIds....'); end; begin subs_msgid := ''; qry3 := 'select msgid from ' || qtable2 || ' where msgid in ( '|| finalMsgIds ||' ) order by '|| sort_order; -- qry3 := 'select msgid from ' || qtable2 || ' where msgid in ( '|| finalMsgIds ||' ) '; DBMS_OUTPUT.PUT_LINE('qry3: '||qry3); open test_cursor2 for qry3; fetch test_cursor2 into subs_msgid; close test_cursor2; DBMS_OUTPUT.PUT_LINE('+++++$$$$$: FINAL subs_msgid: '||subs_msgid); exception WHEN others THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('Error in getting FINAL subs_msgid....'); end; begin first_msg_age := 0; first_msg_state := ''; qry4 := 'select (sysdate-enq_time) * 86400, msg_state from '|| qtable3 ||' where msg_id ='''||subs_msgid||''''; open test_cursor3 for qry4; fetch test_cursor3 into first_msg_age, first_msg_state ; IF first_msg_age != 0 THEN execute immediate 'insert into AQ_MNTR_MSGS_PERSQ values (:1, :2, :3, :4)' using rec.owner, rec.name, first_msg_age, first_msg_state; commit; END IF; exception WHEN others THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('Error in inserting into AQ_MNTR_MSGS_PERSQ....'); end; END IF; /* Processing END */ DBMS_OUTPUT.PUT_LINE('+++++ END +++++'); EXCEPTION WHEN others THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('INSIDE THE MAIN LOOP >>>> NO DATA CONTINUE....'); end; end loop; lv_sql_stmt:='select schema, queue_name, first_message_age, first_msg_state from AQ_MNTR_MSGS_PERSQ'; open data_cursor for lv_sql_stmt; :1 := data_cursor; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Failed to create AQ_MNTR_MSGS_PERSQ ! ! ! ! ! ***** '); end; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role Do not include sys/system/sysman/dbsnmp schema as these might contain system queues for rec in (select owner, name, queue_table from dba_queues where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') ) loop begin qtable := rec.owner||'.AQ$_'||rec.queue_table||'_S'; qtable2 := rec.owner||'.AQ$_'||rec.queue_table||'_I'; qtable3 := rec.owner||'.AQ$'||rec.queue_table; DBMS_OUTPUT.PUT_LINE(qtable||','||qtable2||','||qtable3); qry1 := 'select subscriber_id, name, address from ' || qtable || ' where queue_name='''||rec.name||''''; open test_cursor for qry1; loop subs_id := ''; subs_name := ''; subs_address := ''; fetch test_cursor into subs_id, subs_name, subs_address; DBMS_OUTPUT.PUT_LINE(qtable||','||rec.name||','||subs_id||','||subs_name||','||subs_address); pk := rec.owner||':'||rec.name||':'||subs_name||':'||subs_address; begin begin -- execute immediate 'select msgid from :1 where SUBSCRIBER# = :2 and and rownum <=1 ' into subs_msgid using qtable2, subs_id; qry2 := 'select msgid from ' || qtable2 || ' where SUBSCRIBER# = '|| subs_id ||' and rownum <=1'; open test_cursor2 for qry2; subs_msgid := ''; fetch test_cursor2 into subs_msgid; qry3 := 'select (sysdate-enq_time) * 86400, msg_state from '|| qtable3 ||' where msg_id ='''||subs_msgid||''''; open test_cursor3 for qry3; first_msg_age := 0; first_msg_state := ''; fetch test_cursor3 into first_msg_age, first_msg_state ; IF first_msg_age != 0 THEN execute immediate 'insert into AQ_MNTR_MSGS_PERSQSUBS values (:1, :2, :3, :4, :5, :6, :7)' using pk, rec.owner, rec.name, subs_name, subs_address, first_msg_age, first_msg_state; commit; END IF; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** FIRST QUERY RETURNED NO DATA***** '); end; -- Reset after using first_msg_age := 0; first_msg_state := ''; close test_cursor2; close test_cursor3; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Insert into AQ_MNTR_MSGS_PERSQSUBS failed might be due to unique key constraint ***** '); end; exit when test_cursor %notfound; end loop; close test_cursor; EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE('INSIDE THE MAIN LOOP >>>> NO DATA CONTINUE....'); end; end loop; lv_sql_stmt:='select schema, queue_name, subs_name, subs_address, first_message_age, first_msg_state from AQ_MNTR_MSGS_PERSQSUBS'; open data_cursor for lv_sql_stmt; :1 := data_cursor; exception WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(' ***** Failed to create AQ_MNTR_MSGS_PERSQSUBS ! ! ! ! ! ***** '); end; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role >>> NO DATA CONTINUE....'); END; --execute immediate 'select schema, queue_name, buffq_first_message_age, buffq_first_message_state from AQ_MNTR_MSGS_BUFFQ'; end loop; lv_sql_stmt:='select schema, queue_name, buffq_first_message_age, buffq_first_message_state from AQ_MNTR_MSGS_BUFFQ'; open data_cursor for lv_sql_stmt; :1 := data_cursor; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Failed to create AQ_MNTR_MSGS_BUFFQ ! ! ! ! ! ***** '); end; ]]> 1 SQL_CURSOR MachineName Port SID UserName password Role MachineName Port SID UserName password Role % % MachineName Port SID UserName password Role % perlBin scriptsDir %perlBin%/perl %scriptsDir%/streams/pstats.pl em_result= | NAME PROPAGATION UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) VersionCategory % perlBin scriptsDir %perlBin%/perl %scriptsDir%/streams/pstats.pl em_result= | NAME APPLY UserName password Role OracleHome MachineName Port SID VersionCategory (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) % % MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role MachineName Port SID UserName password Role perlBin scriptsDir %perlBin%/perl %scriptsDir%/dv/realmviolations.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) agentStateDir GUID perlBin scriptsDir %perlBin%/perl %scriptsDir%/dv/commandruleviolations.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) agentStateDir GUID perlBin scriptsDir %perlBin%/perl %scriptsDir%/dv/realmconfissue.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) perlBin scriptsDir %perlBin%/perl %scriptsDir%/dv/commandruleconfissue.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) perlBin scriptsDir %perlBin%/perl %scriptsDir%/dv/policychanges.pl em_result= | NAME UserName password Role OracleHome MachineName Port SID (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) agentStateDir GUID 1 SQL_CURSOR MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName % % MachineName Port SID UserName password Role ALERT_QUE MachineName Port SID UserName password Role InstanceName OBJECT_NAME id scriptsDir perlBin em_result= em_warning= | OracleHome OracleHome SID UserName password perlBin scriptsDir OracleHome Role MachineName Port (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%))) scriptsDir perlBin em_result= em_warning= | OracleHome OracleHome VersionCategory VersionCategory SID SID UserName password perlBin scriptsDir OracleHome Role MachineName Port (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%MachineName%)(Port=%Port%))(CONNECT_DATA=(SID=%SID%)))