| SQL Id | SQL Text |
|---|
| 0guz6b5fs6xya | begin open :1 for select md.metric_id, at.internal_metric_category, at.internal_metric_name, md.status, md.category from dbsnmp.bsln_metric_defaults md, v$threshold_types tt, v$alert_types at where md.metric_id = tt.metrics_id and tt.alert_reason_id = at.reason_id; open :2 for select hour, dbsnmp.bsln.timegroup(:3 , hour), dbsnmp.bsln.timegroup(:4 , hour), dbsnmp.bsln.timegroup(:5 , hour), dbsnmp.bsln.timegroup(:6 , hour), dbsnmp.bsln.timegroup(:7 , hour), dbsnmp.bsln.timegroup(:8 , hour), dbsnmp.bsln.timegroup(:9 , hour), dbsnmp.bsln.timegroup(:10 , hour), dbsnmp.bsln.timegroup(:11 , hour) from dbsnmp.bsln_timegroups; open :12 for select HEXTORAW(dbsnmp.bsln.baseline_guid(baseline_id)), baseline_id, baseline_name from dba_hist_baseline_metadata where baseline_id = 0 and dbid = dbsnmp.bsln_internal.this_dbid; end; |
| 0k8522rmdzg4k | select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0 |
| 0m78skf1mudnb | select audit$, properties from type_misc$ where obj#=:1 |
| 0v3dvmc22qnam | insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, nonequijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn, :coln, decode(bitand(:flag, 1), 0, 0, 1), decode(bitand(:flag, 2), 0, 0, 1), decode(bitand(:flag, 4), 0, 0, 1), decode(bitand(:flag, 8), 0, 0, 1), decode(bitand(:flag, 16), 0, 0, 1), decode(bitand(:flag, 32), 0, 0, 1), :time) |
| 0ws7ahf1d78qa | select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME') |
| 1att0guk2skhm | select lobtsn, lobrdba, lobobjid from x$lobstat where lobspcalloc > 0 and lobcurrtime >= :1 order by lobspcalloc desc |
| 1cq3qr774cu45 | insert into WRH$_IOSTAT_FILETYPE (snap_id, dbid, instance_number, filetype_id, small_read_megabytes, small_write_megabytes, large_read_megabytes, large_write_megabytes, small_read_reqs, small_write_reqs, small_sync_read_reqs, large_read_reqs, large_write_reqs, small_read_servicetime, small_write_servicetime, small_sync_read_latency, large_read_servicetime, large_write_servicetime, retries_on_error) (select :snap_id, :dbid, :instance_number, filetype_id, sum(small_read_megabytes) small_read_megabytes, sum(small_write_megabytes) small_write_megabytes, sum(large_read_megabytes) large_read_megabytes, sum(large_write_megabytes) large_write_megabytes, sum(small_read_reqs) small_read_reqs, sum(small_write_reqs) small_write_reqs, sum(small_sync_read_reqs) small_sync_read_reqs, sum(large_read_reqs) large_read_reqs, sum(large_write_reqs) large_write_reqs, sum(small_read_servicetime) small_read_servicetime, sum(small_write_servicetime) small_write_servicetime, sum(small_sync_read_latency) small_sync_read_latency, sum(large_read_servicetime) large_read_servicetime, sum(large_write_servicetime) large_write_servicetime, sum(retries_on_error) retries_on_error from v$iostat_file group by filetype_id) |
| 1qntcw2s31n15 | select * from dba_hist_baseline |
| 29npwaz6dqsva | SELECT CONFIG_VALUE_ID, CONFIG_ID, VALUE, DESCRIPTION, CLOB_VALUE, NODE_ID, OWNER, RELATED_ORG_ID FROM MN_CONFIGURATION_VALUE WHERE FEATURE = :1 AND INIT_CONFIGSET_VALUE = 1 |
| 2q93zsrvbdw48 | select grantee#, privilege#, nvl(col#, 0), max(mod(nvl(option$, 0), 2))from objauth$ where obj#=:1 group by grantee#, privilege#, nvl(col#, 0) order by grantee# |
| 2xrc1gttayzmy |
SELECT /*+ ORDERED USE_MERGE(m) */ TO_CHAR( FROM_TZ( CAST(m.end_time AS TIMESTAMP), TO_CHAR(systimestamp, 'tzr') ) AT TIME ZONE sessiontimezone, 'YYYY-MM-DD HH24:MI:SS'), SUM(CASE WHEN a.internal_metric_name = 'transactions_ps' THEN m.value ELSE 0 END) transactions_ps, SUM(CASE WHEN a.internal_metric_name = 'physreads_ps' THEN m.value ELSE 0 END) physreads_ps, SUM(CASE WHEN a.internal_metric_name = 'physreads_pt' THEN m.value ELSE 0 END) physreads_pt, SUM(CASE WHEN a.internal_metric_name = 'physwrites_ps' THEN m.value ELSE 0 END) physwrites_ps, SUM(CASE WHEN a.internal_metric_name = 'physwrites_pt' THEN m.value ELSE 0 END) physwrites_pt, SUM(CASE WHEN a.internal_metric_name = 'physreadsdir_ps' THEN m.value ELSE 0 END) physreadsdir_ps, SUM(CASE WHEN a.internal_metric_name = 'physreadsdir_pt' THEN m.value ELSE 0 END) physreadsdir_pt, SUM(CASE WHEN a.internal_metric_name = 'physwritesdir_ps' THEN m.value ELSE 0 END) physwritesdir_ps, SUM(CASE WHEN a.internal_metric_name = 'physwritesdir_pt' THEN m.value ELSE 0 END) physwritesdir_pt, SUM(CASE WHEN a.internal_metric_name = 'physreadslob_ps' THEN m.value ELSE 0 END) physreadslob_ps, SUM(CASE WHEN a.internal_metric_name = 'physreadslob_pt' THEN m.value ELSE 0 END) physreadslob_pt, SUM(CASE WHEN a.internal_metric_name = 'physwriteslob_ps' THEN m.value ELSE 0 END) physwriteslob_ps, SUM(CASE WHEN a.internal_metric_name = 'physwriteslob_pt' THEN m.value ELSE 0 END) physwriteslob_pt, SUM(CASE WHEN a.internal_metric_name = 'redosize_ps' THEN m.value ELSE 0 END) redosize_ps, SUM(CASE WHEN a.internal_metric_name = 'logons_ps' THEN m.value ELSE 0 END) logons_ps, SUM(CASE WHEN a.internal_metric_name = 'logons_pt' THEN m.value ELSE 0 END) logons_pt, SUM(CASE WHEN a.internal_metric_name = 'opncurs_ps' THEN m.value ELSE 0 END) opncurs_ps, SUM(CASE WHEN a.internal_metric_name = 'opncurs_pt' THEN m.value ELSE 0 END) opncurs_pt, SUM(CASE WHEN a.internal_metric_name = 'commits_ps' THEN m.value ELSE 0 END) commits_ps, SUM(CASE WHEN a.internal_metric_name = 'commits_pt' THEN m.value ELSE 0 END) commits_pt, SUM(CASE WHEN a.internal_metric_name = 'rollbacks_ps' THEN m.value ELSE 0 END) rollbacks_ps, SUM(CASE WHEN a.internal_metric_name = 'rollbacks_pt' THEN m.value ELSE 0 END) rollbacks_pt, SUM(CASE WHEN a.internal_metric_name = 'usercalls_ps' THEN m.value ELSE 0 END) usercalls_ps, SUM(CASE WHEN a.internal_metric_name = 'usercalls_pt' THEN m.value ELSE 0 END) usercalls_pt, SUM(CASE WHEN a.internal_metric_name = 'recurscalls_ps' THEN m.value ELSE 0 END) recurscalls_ps, SUM(CASE WHEN a.internal_metric_name = 'recurscalls_pt' THEN m.value ELSE 0 END) recurscalls_pt, SUM(CASE WHEN a.internal_metric_name = 'logreads_ps' THEN m.value ELSE 0 END) logreads_ps, SUM(CASE WHEN a.internal_metric_name = 'logreads_pt' THEN m.value ELSE 0 END) logreads_pt, SUM(CASE WHEN a.internal_metric_name = 'dbwrcheckpoints_ps' THEN m.value ELSE 0 END) dbwrcheckpoints_ps, SUM(CASE WHEN a.internal_metric_name = 'bgcheckpoints_ps' THEN m.value ELSE 0 END) bgcheckpoints_ps, SUM(CASE WHEN a.internal_metric_name = 'redowrites_ps' THEN m.value ELSE 0 END) redowrites_ps, SUM(CASE WHEN a.internal_metric_name = 'redowrites_pt' THEN m.value ELSE 0 END) redowrites_pt, SUM(CASE WHEN a.internal_metric_name = 'tabscanslong_ps' THEN m.value ELSE 0 END) tabscanslong_ps, SUM(CASE WHEN a.internal_metric_name = 'tabscanslong_pt' THEN m.value ELSE 0 END) tabscanslong_pt, SUM(CASE WHEN a.internal_metric_name = 'tabscanstotal_ps' THEN m.value ELSE 0 END) tabscanstotal_ps, SUM(CASE WHEN a.internal_metric_name = 'tabscanstotal_pt' THEN m.value ELSE 0 END) tabscanstotal_pt, SUM(CASE WHEN a.internal_metric_name = 'indxscansfull_pt' THEN m.value ELSE 0 END) indxscansfull_pt, SUM(CASE WHEN a.internal_metric_name = 'indxscansfull_ps' THEN m.value ELSE 0 END) indxscansfull_ps, SUM(CASE WHEN a.internal_metric_name = 'indxscanstotal_ps' THEN m.value ELSE 0 END) indxscanstotal_ps, SUM(CASE WHEN a.internal_metric_name = '
indxscanstotal_pt' THEN m.value ELSE 0 END) indxscanstotal_pt, SUM(CASE WHEN a.internal_metric_name = 'parses_ps' THEN m.value ELSE 0 END) parses_ps, SUM(CASE WHEN a.internal_metric_name = 'parses_pt' THEN m.value ELSE 0 END) parses_pt, SUM(CASE WHEN a.internal_metric_name = 'hardparses_ps' THEN m.value ELSE 0 END) hardparses_ps, SUM(CASE WHEN a.internal_metric_name = 'hardparses_pt' THEN m.value ELSE 0 END) hardparses_pt, SUM(CASE WHEN a.internal_metric_name = 'failedparses_ps' THEN m.value ELSE 0 END) failedparses_ps, SUM(CASE WHEN a.internal_metric_name = 'failedparses_pt' THEN m.value ELSE 0 END) failedparses_pt, SUM(CASE WHEN a.internal_metric_name = 'executions_ps' THEN m.value ELSE 0 END) executions_ps, SUM(CASE WHEN a.internal_metric_name = 'sortsdisk_ps' THEN m.value ELSE 0 END) sortsdisk_ps, SUM(CASE WHEN a.internal_metric_name = 'sortsdisk_pt' THEN m.value ELSE 0 END) sortsdisk_pt, SUM(CASE WHEN a.internal_metric_name = 'rows_psort' THEN m.value ELSE 0 END) rows_psort, SUM(CASE WHEN a.internal_metric_name = 'executeswoparse_pct' THEN m.value ELSE 0 END) executeswoparse_pct, SUM(CASE WHEN a.internal_metric_name = 'softparse_pct' THEN m.value ELSE 0 END) softparse_pct, SUM(CASE WHEN a.internal_metric_name = 'usercall_pct' THEN m.value ELSE 0 END) usercall_pct, SUM(CASE WHEN a.internal_metric_name = 'networkbytes_ps' THEN m.value ELSE 0 END) networkbytes_ps, SUM(CASE WHEN a.internal_metric_name = 'enqtimeouts_ps' THEN m.value ELSE 0 END) enqtimeouts_ps, SUM(CASE WHEN a.internal_metric_name = 'enqtimeouts_pt' THEN m.value ELSE 0 END) enqtimeouts_pt, SUM(CASE WHEN a.internal_metric_name = 'enqwaits_ps' THEN m.value ELSE 0 END) enqwaits_ps, SUM(CASE WHEN a.internal_metric_name = 'enqwaits_pt' THEN m.value ELSE 0 END) enqwaits_pt, SUM(CASE WHEN a.internal_metric_name = 'enqdeadlocks_ps' THEN m.value ELSE 0 END) enqdeadlocks_ps, SUM(CASE WHEN a.internal_metric_name = 'enqdeadlocks_pt' THEN m.value ELSE 0 END) enqdeadlocks_pt, SUM(CASE WHEN a.internal_metric_name = 'enqreqs_ps' THEN m.value ELSE 0 END) enqreqs_ps, SUM(CASE WHEN a.internal_metric_name = 'enqreqs_pt' THEN m.value ELSE 0 END) enqreqs_pt, SUM(CASE WHEN a.internal_metric_name = 'dbblkgets_ps' THEN m.value ELSE 0 END) dbblkgets_ps, SUM(CASE WHEN a.internal_metric_name = 'dbblkgets_pt' THEN m.value ELSE 0 END) dbblkgets_pt, SUM(CASE WHEN a.internal_metric_name = 'consistentreadgets_ps' THEN m.value ELSE 0 END) consistentreadgets_ps, SUM(CASE WHEN a.internal_metric_name = 'consistentreadgets_pt' THEN m.value ELSE 0 END) consistentreadgets_pt, SUM(CASE WHEN a.internal_metric_name = 'dbblkchanges_ps' THEN m.value ELSE 0 END) dbblkchanges_ps, SUM(CASE WHEN a.internal_metric_name = 'dbblkchanges_pt' THEN m.value ELSE 0 END) dbblkchanges_pt, SUM(CASE WHEN a.internal_metric_name = 'consistentreadchanges_ps' THEN m.value ELSE 0 END) consistentreadchanges_ps, SUM(CASE WHEN a.internal_metric_name = 'consistentreadchanges_pt' THEN m.value ELSE 0 END) consistentreadchanges_pt, SUM(CASE WHEN a.internal_metric_name = 'crblks_ps' THEN m.value ELSE 0 END) crblks_ps, SUM(CASE WHEN a.internal_metric_name = 'crblks_pt' THEN m.value ELSE 0 END) crblks_pt, SUM(CASE WHEN a.internal_metric_name = 'crundorecs_pt' THEN m.value ELSE 0 END) crundorecs_pt, SUM(CASE WHEN a.internal_metric_name = 'userrollbackundorec_ps' THEN m.value ELSE 0 END) userrollbackundorec_ps, SUM(CASE WHEN a.internal_metric_name = 'userrollbackundorec_pt' THEN m.value ELSE 0 END) userrollbackundorec_pt, SUM(CASE WHEN a.internal_metric_name = 'leafnodesplits_ps' THEN m.value ELSE 0 END) leafnodesplits_ps, SUM(CASE WHEN a.internal_metric_name = 'leafnodesplits_pt' THEN m.value ELSE 0 END) leafnodesplits_pt, SUM(CASE WHEN a.internal_metric_name = 'branchnodesplits_ps' THEN m.value ELSE 0 END) branchnodesplits_ps, SUM(CASE WHEN a.internal_metric_name = 'branchnodesplits_pt' THEN m.value ELSE 0 END) branchnodesplits_pt, SUM(CASE WHEN a.internal_metric_name = 'redosize_pt' THEN m.valu
e ELSE 0 END) redosize_pt, SUM(CASE WHEN a.internal_metric_name = 'crundorecs_ps' THEN m.value ELSE 0 END) crundorecs_ps, SUM(CASE WHEN a.internal_metric_name = 'dbtime_ps' THEN m.value ELSE 0 END) dbtime_ps, SUM(CASE WHEN a.internal_metric_name = 'avg_active_sessions' THEN m.value ELSE 0 END) avg_active_sessions, SUM(CASE WHEN a.internal_metric_name = 'avg_sync_singleblk_read_latency' THEN m.value ELSE 0 END) avg_block_read_latency, SUM(CASE WHEN a.internal_metric_name = 'iombs_ps' THEN m.value ELSE 0 END) iombs_ps, SUM(CASE WHEN a.internal_metric_name = 'iorequests_ps' THEN m.value ELSE 0 END) iorequests_ps FROM v$alert_types a, v$threshold_types t, v$sysmetric m WHERE a.internal_metric_category = 'instance_throughput' AND a.reason_id = t.alert_reason_id AND t.metrics_id = m.metric_id AND m.group_id = 2 AND :1 != 'BASIC' AND m.end_time <= SYSDATE GROUP BY m.end_time ORDER BY m.end_time ASC |
| 317v5hnvvd49h | UPDATE wrh$_seg_stat_obj ob SET (snap_id, owner, object_name, subobject_name, tablespace_name, object_type, index_type, base_obj#, base_object_name, base_object_owner) = (SELECT so.snap_id + 1, so.oj.owner_name, so.oj.object_name, so.oj.subobject_name, so.oj.tablespace_name, so.oj.object_type, index_type, coalesce(io.base_obj#, so.base_obj#), coalesce(io.base_object_name, so.base_object_name), coalesce(io.base_object_owner, so.base_object_owner) FROM (SELECT dbid, ts#, obj#, dataobj#, object_type, base_obj#, base_object_name, base_object_owner, snap_id, dbms_swrf_internal.awr_get_object_info( ts#, obj#, dataobj#, AWR_OBJECT_INFO_TYPE(owner, object_name, subobject_name, tablespace_name, object_type)) oj FROM wrh$_seg_stat_obj) so LEFT OUTER JOIN (SELECT decode(ind.type#, 1, 'NORMAL'|| decode(bitand(ind.property, 4), 0, '', 4, '/REV'), 2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP', 5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB', 9, 'DOMAIN') as index_type, base_obj.obj# as base_obj#, base_obj.name as base_object_name, base_owner.name as base_object_owner, ind.bo# as ind_base_obj#, ind.obj# as ind_obj#, ind.dataobj# as ind_dataobj# FROM ((sys.ind$ ind JOIN sys.obj$ base_obj ON (ind.bo# = base_obj.obj#)) JOIN sys.user$ base_owner ON (base_obj.owner# = base_owner.user#))) io ON (so.oj.object_type = 'INDEX' AND so.obj# = io.ind_obj# AND so.dataobj# = io.ind_dataobj#) WHERE ob.dbid = so.dbid AND ob.ts# = so.ts# AND ob.obj# = so.obj# AND ob.dataobj# = so.dataobj#) WHERE dbid = :dbid AND object_name NOT LIKE '%MISSING%' AND object_name NOT LIKE '%TRANSIENT%' AND (object_name, NVL(subobject_name, '!')) NOT IN (SELECT name, nvl(subname, '!') FROM obj$ WHERE ob.obj# = obj$.obj#) AND (ts#, obj#, dataobj#) in (SELECT tsn_kewrseg, objn_kewrseg, objd_kewrseg FROM x$kewrtsegstat) |
| 350myuyx0t1d6 | insert into wrh$_tablespace_stat (snap_id, dbid, instance_number, ts#, tsname, contents, status, segment_space_management, extent_management, is_backup) select :snap_id, :dbid, :instance_number, ts.ts#, ts.name as tsname, decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO', 'PERMANENT')), 1, 'TEMPORARY') as contents, decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE', 4, 'READ ONLY', 'UNDEFINED') as status, decode(bitand(ts.flags, 32), 32, 'AUTO', 'MANUAL') as segspace_mgmt, decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL') as extent_management, (case when b.active_count > 0 then 'TRUE' else 'FALSE' end) as is_backup from sys.ts$ ts, (select dfile.ts#, sum( case when bkup.status = 'ACTIVE' then 1 else 0 end ) as active_count from v$backup bkup, file$ dfile where bkup.file# = dfile.file# and dfile.status$ = 2 group by dfile.ts#) b where ts.online$ != 3 and bitand(ts.flags, 2048) != 2048 and ts.ts# = b.ts# |
| 35hatv0pj8hw7 | select decode(u.type#, 2, u.ext_username, u.name), o.name, trigger$.sys_evts, trigger$.type# from obj$ o, user$ u, trigger$ where o.type# = 12 and bitand(trigger$.property, 16) = 16 and trigger$.baseobject = :1 and trigger$.obj# = o.obj# and o.owner# = u.user# order by o.obj# |
| 36ya4s1zjwx1h | SELECT COUNT(*) FROM STATS$USER_LOG WHERE USERNAME = USER |
| 3c1kubcdjnppq | update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag, 1), 0, 0, 1), equijoin_preds = equijoin_preds + decode(bitand(:flag, 2), 0, 0, 1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag, 4), 0, 0, 1), range_preds = range_preds + decode(bitand(:flag, 8), 0, 0, 1), like_preds = like_preds + decode(bitand(:flag, 16), 0, 0, 1), null_preds = null_preds + decode(bitand(:flag, 32), 0, 0, 1), timestamp = :time where obj# = :objn and intcol# = :coln |
| 3ktacv9r56b51 | select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# |
| 3nkd3g3ju5ph1 | select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null |
| 3w4qs0tbpmxr6 | select con#, obj#, rcon#, enabled, nvl(defer, 0), spare2, spare3 from cdef$ where robj#=:1 |
| 459f3z9u4fb3u | select value$ from props$ where name = 'GLOBAL_DB_NAME' |
| 4x866jn4mj1uy | SELECT PROPAGATION_NAME, MESSAGE_DELIVERY_MODE, TOTAL_NUMBER, TOTAL_BYTES/1024 KBYTES FROM DBA_PROPAGATION P, DBA_QUEUE_SCHEDULES Q WHERE P.SOURCE_QUEUE_NAME = Q.QNAME AND P.SOURCE_QUEUE_OWNER = Q.SCHEMA AND MESSAGE_DELIVERY_MODE='BUFFERED' AND Q.DESTINATION LIKE '%'||P.DESTINATION_DBLINK||'%' |
| 501kary3x38sj | select r.apply_name apply_name, total_messages_dequeued, total_messages_spilled from gv$streams_apply_reader r, dba_apply a, dba_queues q, dba_queue_tables t where r.apply_name = a.apply_name and a.queue_name = q.name and a.queue_owner = q.owner and q.queue_table = t.queue_table and q.owner=t.owner and r.inst_id = t.owner_instance |
| 53saa2zkr6wc3 | select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1 |
| 5fkzpyqyptax3 | DECLARE instance_number NUMBER; latest_task_id NUMBER; start_time VARCHAR2(1024); end_time VARCHAR2(1024); db_id NUMBER; TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; CURSOR get_latest_task_id IS SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TASK_ID FROM (select * from dba_advisor_tasks order by task_id desc) T, dba_advisor_parameters_proj P1, dba_advisor_parameters_proj P2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START >= (sysdate - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = SYS_CONTEXT('USERENV', 'INSTANCE') AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = to_char(db_id) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1; BEGIN SELECT dbid INTO db_id from v$database; OPEN get_latest_task_id; FETCH get_latest_task_id INTO latest_task_id; CLOSE get_latest_task_id; FOR param_info IN (SELECT parameter_value, parameter_name FROM dba_advisor_parameters_proj WHERE task_id= latest_task_id AND (parameter_name='START_TIME' OR parameter_name='END_TIME') ORDER BY 2) LOOP IF param_info.parameter_name = 'END_TIME' THEN end_time := param_info.parameter_value; ELSIF param_info.parameter_name = 'START_TIME' THEN start_time := param_info.parameter_value; END IF; END LOOP; OPEN data_cursor FOR WITH recInfo AS ( SELECT /*+ NO_MERGE */ finding_id, count(r.rec_id) recCount, r.type FROM DBA_ADVISOR_RECOMMENDATIONS r WHERE task_id=latest_task_id GROUP BY r.finding_id, r.type ), f_curr AS ( SELECT /*+ NO_MERGE */ DISTINCT finding_name FROM DBA_ADVISOR_FINDINGS WHERE task_id=latest_task_id ), tasks AS ( SELECT /*+ NO_MERGE */ t.task_id, i.local_task_id, t.end_time, t.begin_time FROM DBA_ADDM_TASKS t, DBA_ADDM_INSTANCES i WHERE t.end_time>sysdate -1 AND t.task_id=i.task_id AND i.instance_number=SYS_CONTEXT('USERENV', 'INSTANCE') AND t.requested_analysis='INSTANCE' ), f_all AS ( SELECT /*+ NO_MERGE */ COUNT(fa.task_id) finding_count, fa.finding_name FROM tasks, DBA_ADVISOR_FINDINGS fa WHERE fa.task_id=tasks.task_id AND fa.type<>'INFORMATION' AND fa.type<>'WARNING' AND fa.parent=0 GROUP BY fa.finding_name ), history AS ( SELECT /*+ NO_MERGE */ finding_name, finding_count FROM f_all JOIN f_curr USING (finding_name) ) SELECT latest_task_id task_id, f.finding_id finding_id, DECODE(recInfo.type, null, 'Uncategorized', recInfo.type) rec_type, recInfo.recCount rec_count, f.perc_active_sess impact_pct, f.message message, TO_CHAR(TO_DATE(start_time , 'MM-DD-YYYY HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') start_time, TO_CHAR(TO_DATE(end_time, 'MM-DD-YYYY HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end_time, history.finding_count finding_count, f.finding_name finding_name, f.active_sessions active_sessions FROM DBA_ADDM_FINDINGS f, recInfo, history WHERE f.task_id=latest_task_id AND f.type<>'INFORMATION' AND f.type<>'WARNING' AND f.filtered<>'Y' AND f.parent=0 AND f.finding_id=recInfo.finding_id (+) AND f.finding_name=history.finding_name ORDER BY f.finding_id; :1 := data_cursor; END; |
| 5hk2758bxufj0 | SELECT column_name, nullable, data_type, char_length, data_precision, data_scale FROM user_tab_columns WHERE table_name=:1 ORDER BY column_id |
| 5n1fs4m2n2y0r | select pos#, intcol#, col#, spare1, bo#, spare2, spare3 from icol$ where obj#=:1 |
| 5rygsj4dbw6jt | insert into sys.mon_mods$ (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (:1, :2, :3, :4, :5, :6, :7) |
| 5ur69atw3vfhj | select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT', 4, 1), failover_retries, failover_delay, flags from service$ where name = :1 |
| 61a161nm1ttjj | select /*+ INDEX(TAB AQ$_AQ_EVENT_TABLE_I) */ tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.delay, tab.expiration, tab.retry_count, tab.exception_qschema, tab.exception_queue, tab.chain_no, tab.local_order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.enq_tid, tab.step_no, tab.user_data from "SYS"."AQ_EVENT_TABLE" tab where q_name = :1 and (state = :2 ) order by q_name, state, enq_time, step_no, chain_no, local_order_no for update skip locked |
| 6ajkhukk78nsr | begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end; |
| 6aq34nj2zb2n7 | select col#, grantee#, privilege#, max(mod(nvl(option$, 0), 2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# |
| 6wm3n4d7bnddg | SELECT source, (case when time_secs < 1 then 1 else time_secs end) as time_secs, operation FROM ( SELECT 1 as source, trunc((sysdate - cast(ll.log_date as date)) * 86400) as time_secs, decode(ll.operation, 'OPEN', 0 , 1 ) as operation, ll.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG ll , ( SELECT max(l.log_id) as max_log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) < ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) ) max_log WHERE ll.log_id = max_log.max_log_id UNION ALL SELECT 2 as source, trunc((sysdate - cast(l.log_date as date)) * 86400) as time_secs, decode(l.operation, 'OPEN', 0 , 1 ) as operation, l.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) <= ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) AND CAST(l.log_date AS DATE) >= ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) UNION ALL SELECT 0 as source, trunc((sysdate - et.etime) * 86400) as time_secs, 3 as operation, 0 as log_id FROM ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) et UNION ALL SELECT 0 as source, trunc((sysdate - bt.btime) * 86400) as time_secs, 2 as operation, 0 as log_id FROM ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) bt ) WHERE time_secs >= 0 ORDER BY source ASC, time_secs DESC, log_id ASC |
| 6xvp6nxs4a9n4 | select nvl(sum(space), 0) from recyclebin$ where ts# = :1 |
| 7k6zct1sya530 | insert into WRH$_STREAMS_APPLY_SUM (snap_id, dbid, instance_number, apply_name, startup_time, reader_total_messages_dequeued, reader_lag, coord_total_received, coord_total_applied, coord_total_rollbacks, coord_total_wait_deps, coord_total_wait_cmts, coord_lwm_lag, server_total_messages_applied, server_elapsed_dequeue_time, server_elapsed_apply_time) select * from (select :snap_id, :dbid, :instance_number, ac.apply_name, ac.startup_time, ar.total_messages_dequeued, ar.dequeue_time - ar.dequeued_message_create_time, ac.total_received, ac.total_applied, ac.total_rollbacks, ac.total_wait_deps, ac.total_wait_commits, ac.lwm_time - ac.lwm_message_create_time, al.total_messages_applied, al.elapsed_dequeue_time, al.elapsed_apply_time from v$streams_apply_coordinator ac, v$streams_apply_reader ar, (select apply_name, sum(total_messages_applied) total_messages_applied, sum(elapsed_dequeue_time) elapsed_dequeue_time, sum(elapsed_apply_time) elapsed_apply_time from v$streams_apply_server group by apply_name) al where al.apply_name=ac.apply_name and ar.apply_name=ac.apply_name order by ac.total_applied desc) where rownum <= 25 |
| 7ng34ruy5awxq | select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# |
| 7qjhf5dzmazsr | SELECT snap_id , OBJ#, DATAOBJ# FROM (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.snap_id , t1.OBJN_KEWRSEG OBJ#, t1.OBJD_KEWRSEG DATAOBJ# FROM X$KEWRTSEGSTAT t1, WRH$_SEG_STAT_OBJ t2 WHERE t2.dbid(+) = :dbid AND t2.OBJ#(+) = t1.OBJN_KEWRSEG AND t2.DATAOBJ#(+) = t1.OBJD_KEWRSEG) WHERE nvl(snap_id, 0) < :snap_id |
| 7wt7phk4xns75 | select a.capture_name streams_process_name, a.status streams_process_status, 'CAPTURE' streams_process_type, COUNT(a.error_message) from dba_capture a group by a.capture_name, a.status union all select a.propagation_name streams_process_name, a.status streams_process_status, 'PROPAGATION' streams_process_type, COUNT(a.error_message) from dba_propagation a group by a.propagation_name, a.status union all select a.apply_name streams_process_name, a.status streams_process_status, 'APPLY' streams_process_type, COUNT(a.error_message) from dba_apply a group by a.apply_name, a.status |
| 7z7x3166pt4km | SELECT 'SCHEMA' type, username owner, username object_name, null column_name, null column_id, null data_type FROM all_users WHERE rownum <=50 and username like :1 union all SELECT 'ARGUMENT' type, null owner, a.owner||'.'||a.package_name||'.'||a.object_name||'.'||a.overload object_name, a.argument_name||' '||a.data_type column_name, a.position column_id, null data_type FROM all_arguments a, all_objects o WHERE a.object_id=o.object_id and rownum <=50 and argument_name is not null and o.object_name = :2 union all SELECT object_type type, SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE rownum <=50 and object_name not like 'BIN$%' and object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') and object_name like :3 |
| 81uvdbt46vt5p | DECLARE /* Yannick */ i NUMBER; sql_stmt VARCHAR2(200); max_id number; BEGIN SELECT MAX(id) INTO max_id FROM yjaquier.test1; i:=1; DBMS_RANDOM.SEED(SYSDATE); FOR i IN 1..max_id LOOP sql_stmt:='SELECT /* Yannick */ descr FROM yjaquier.test1 WHERE id=' || ROUND(DBMS_RANDOM.VALUE(1, max_id)); EXECUTE IMMEDIATE sql_stmt; END LOOP; END; |
| 83taa7kaw59c1 | select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/*MAXSB1MINAL*/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0) from col$ where obj#=:1 order by intcol# |
| 87vs8t2c6ra1c | select metric_id, value from v$sysmetric where intsize_csec > 5900 and group_id = 2 and metric_id in (2135, 2155, 2153, 2075) |
| 8gt45jpvt1ywh | INSERT INTO mn_hist_task (ver_start_date, ver_end_date, task_end_date, session_id, query_time, query_count, date_created, member_id_updated, task_id, bytes_returned, name, components, task_start_date, thread_type, jvm_time, id, class, member_name, server_time, action, pobjs, node_name, ver_state, resource_key, ver_num, component_time, mgr_id, realm_num, date_updated, pobjs_time, member_id_created) VALUES (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 ) |
| 8jk9z1dwpkpb3 | SELECT mn_member.ver_num, mn_member.mgr_id, mn_member.member_id FROM mn_member WHERE (NOT (mn_member.realm_number IN (0, 1))) |
| 8swypbbr0m372 | select order#, columns, types from access$ where d_obj#=:1 |
| 96g93hntrzjtr | select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 |
| 9gkq7rruycsjp | select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296), 256) defhscflags, mod(spare3, 256) interval_dty, rowid, defmaxsize from partobj$ where obj# = :1 |
| 9nh7uqswpdc6h | select OID, Customer_Oid, Customer_Name, Customer_Location, ACCOUNT_NUM, Interface_Name, MN_Created_DEC, DEC_Code, STATUS , customer_alias_oid from dataload.ST_ACC_ALIAS_DATA where STATUS = :1 order by interface_id |
| 9tgj4g8y4rwy8 | select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0), NVL(bitmapranges, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3 |
| 9vqmgu6c7011u | select a.capture_name capture_name, total_messages_captured, total_messages_enqueued from gv$streams_capture a, dba_capture b, dba_queues c, dba_queue_tables d where a.capture_name = b.capture_name and b.queue_name=c.name and b.queue_owner=c.owner and c.queue_table=d.queue_table and c.owner=d.owner and d.owner_instance=a.inst_id |
| 9zzuyxx4pjhsx | select p_obj#, flags, code, audit$ from edition$ where obj#=:1 |
| a32bhg8ny1y4u | select * from dba_tab_statistics where owner='YJAQUIER' and table_name='TEST1' |
| a3kxyqjurdtp8 | insert into mgmt_db_size_gtt SELECT free.tablespace_name, SUM(free.sz) as sz FROM (SELECT tablespace_name, NVL(sum(bytes)/1048576, 0) sz FROM sys.dba_free_space GROUP BY tablespace_name UNION ALL SELECT tablespace_name, NVL(SUM(bytes)/1048576, 0) sz FROM dba_undo_extents WHERE status='EXPIRED' GROUP BY tablespace_name) free GROUP BY free.tablespace_name |
| avv5dcc9zx2qb | SELECT 'SCHEMA' type, username owner, username object_name, null column_name, null column_id, null data_type FROM all_users WHERE rownum <=50 and username like :1 union all SELECT object_type type, SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE rownum <=50 and object_name not like 'BIN$%' and object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') and object_name like :2 |
| b3zgx1xckrwxu | select host, userid, password, flag, authusr, authpwd, passwordx, authpwdx from link$ where owner#=:1 and name=:2 |
| b4mrsfc9ttham | WITH snaps as (SELECT :1 dbid, MIN(snap_id) as bid, MAX(snap_id) as eid FROM DBA_HIST_SNAPSHOT WHERE dbid = :2 AND end_interval_time >= :3 AND end_interval_time <= :4 ) SELECT t.task_id, t.end_time as end_time FROM DBA_ADDM_TASKS t, snaps s, DBA_ADDM_INSTANCES i WHERE t.dbid = :5 AND t.dbid = s.dbid AND t.begin_snap_id >= s.bid - 1 AND t.begin_snap_id < s.eid AND t.how_created = 'AUTO' AND t.requested_analysis = 'INSTANCE' AND t.task_id = i.task_id AND i.instance_number = :6 |
| bgba4tpdcpgun | declare t_user_count NUMBER; l_program v$session.program%TYPE; l_osuser v$session.program%TYPE; begin select count(*) into t_user_count from stats$user_log where username = USER; IF t_user_count = 0 THEN insert into stats$user_log ( username, LAST_LOGON_DAY ) values( USER, sysdate ); ELSE UPDATE stats$user_log SET LAST_LOGON_DAY = SYSDATE WHERE username = USER; END IF; exception when others then raise_application_error(-20008, 'Problem in connection due to Logon_Trig. Contact DB-SL Team.'); end; |
| bqfx5q2jas08u | SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TASK_ID FROM (SELECT * FROM DBA_ADVISOR_TASKS ORDER BY TASK_ID DESC) T, DBA_ADVISOR_PARAMETERS_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START >= (SYSDATE - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = SYS_CONTEXT('USERENV', 'INSTANCE') AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = TO_CHAR(:B1 ) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1 |
| bzgpy0vxtvf63 | SELECT event#, sql_id, sql_plan_hash_value, sql_opcode, session_id, session_serial#, module, action, client_id, DECODE(wait_time, 0, 'W', 'C'), 1, time_waited, service_hash, user_id, program, sample_time, p1, p2, p3, current_file#, current_obj#, current_block#, qc_session_id, qc_instance_id, sql_child_number, plsql_object_id, plsql_subprogram_id, qc_session_serial# FROM v$active_session_history WHERE sample_time > :1 AND sample_time <= :2 |
| cm5vu20fhtnq1 | select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 |
| cyg8zddx2x8h6 | select FUNCTION_NAME, SMALL_READ_MBPS, SMALL_WRITE_MBPS, LARGE_READ_MBPS, LARGE_WRITE_MBPS, SMALL_READ_IOPS, SMALL_WRITE_IOPS, LARGE_READ_IOPS, LARGE_WRITE_IOPS, AVG_WAIT_TIME from V$IOFUNCMETRIC where intsize_csec > 5900 order by function_name |
| d2687zmqyq4av | select metric_id, value from v$sysmetric where intsize_csec > 5900 and group_id = 2 and metric_id in (2148, 2149, 2137, 2138, 2141, 2140, 2139, 2091, 2090, 2089, 2088, 2087, 2142, 2144) |
| d6yky1ddz2m2q | UPDATE STATS$USER_LOG SET LAST_LOGON_DAY = SYSDATE WHERE USERNAME = USER |
| db78fxqxwxt7r | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket |
| dc1876qva6b70 | select qi.oid from dataload.quote q, dataload.quote_item qi, dataload.quote_contract_mapping qcm, dataload.mn_ht_contract mhc, dataload.part p, dataload.mn_ht_contract_li mhcl, dataload.quote_contract_item_mapping qcim, dataload.exporter_info ei where q.oid = qi.quote_oid and qcm.contract_oid = mhc.contract_id and p.oid = qi.part_oid and qcm.quote_oid = q.oid and mhc.contract_id = mhcl.contract_id and qi.custom_field1 in ('On Hold', 'Deleted', 'Rejected') and mhcl.line_status = 30 and q.quote_type_oid = 4 and qcim.contract_item_oid = mhcl.contract_line_item_id and qcim.quote_item_oid = qi.oid and mhc.contract_status not in ('TERM') and ei.TYPE ='CANCEL_CONTRACT_ITEM' and qi.MODIFIED_DATE >= ei.LAST_EXPORT |
| djunbuupchm4s | LOCK TABLE event_info IN EXCLUSIVE MODE |
| f1yky4nd9n9s6 | select end_time, wait_class#, (time_waited_fg)/(intsize_csec/100), (time_waited)/(intsize_csec/100), 0 from v$waitclassmetric union all select end_time, -1, sum(case when metric_name = 'CPU Usage Per Sec' then value else 0 end) fg, sum(case when metric_name = 'Background CPU Usage Per Sec' then value else 0 end) bg, sum(case when metric_name = 'Average Active Sessions' then value else 0 end) dbt from v$sysmetric where group_id = 2 and metric_name in ('Background CPU Usage Per Sec', 'CPU Usage Per Sec', 'Average Active Sessions') group by end_time order by end_time, wait_class# |
| fuyrx4r46bgxw | select capture_name streams_name, 'capture' streams_type , (available_message_create_time- capture_message_create_time)*86400 latency, nvl(total_messages_enqueued, 0) total_messages from gv$streams_capture union all select propagation_name streams_name, 'propagation' streams_type, last_lcr_latency latency , total_msgs total_messages from gv$propagation_sender where propagation_name is not null union all select server_name streams_name, 'apply' streams_type, (send_time-last_sent_message_create_time)*86400 latency, nvl(total_messages_sent, 0) total_messages from gv$xstream_outbound_server where committed_data_only='NO' union all SELECT distinct apc.apply_name as STREAMS_NAME, 'apply' as STREAMS_TYPE, CASE WHEN aps.state != 'IDLE' THEN nvl((aps.apply_time - aps.create_time)*86400, -1) WHEN apc.state != 'IDLE' THEN nvl((apc.apply_time - apc.create_time)*86400, -1) WHEN apr.state != 'IDLE' THEN nvl((apr.apply_time - apr.create_time)*86400, -1) ELSE 0 END as STREAMS_LATENCY, nvl(aps.TOTAL_MESSAGES_APPLIED, 0) as TOTAL_MESSAGES FROM ( SELECT apply_name, state, apply_time, applied_message_create_time as create_time, total_messages_applied FROM ( SELECT apply_name, state, apply_time, applied_message_create_time, MAX(applied_message_create_time) OVER (PARTITION BY apply_name) as max_create_time, SUM(total_messages_applied) OVER (PARTITION BY apply_name) as total_messages_applied FROM gv$streams_apply_server ) WHERE MAX_CREATE_TIME||'X' = APPLIED_MESSAGE_CREATE_TIME||'X' ) aps, ( SELECT c.apply_name, state, c.hwm_time as apply_time, hwm_message_create_time as create_time, total_applied FROM gv$streams_apply_coordinator c, dba_apply p WHERE p.apply_name = c.apply_name and p.apply_name in (select server_name from dba_xstream_outbound) union SELECT c.apply_name, state, c.lwm_time as apply_time, lwm_message_create_time as create_time, total_applied FROM gv$streams_apply_coordinator c, dba_apply p WHERE p.apply_name = c.apply_name and p.apply_name not in (select server_name from dba_xstream_outbound) ) apc, ( SELECT apply_name, state, dequeue_time as apply_time, dequeued_message_create_time as create_time FROM gv$streams_apply_reader ) apr WHERE apc.apply_name = apr.apply_name AND apr.apply_name = aps.apply_name |
| gdvfxf7k4scsn | select r.apply_name apply_name, r.total_received total_received, r.total_assigned total_assigned, r.total_applied total_applied from gv$streams_apply_coordinator r, dba_apply a, dba_queues q, dba_queue_tables t where r.apply_name = a.apply_name and a.queue_name = q.name and a.queue_owner = q.owner and q.queue_table = t.queue_table and q.owner=t.owner and r.inst_id = t.owner_instance |
| grwydz59pu6mc | select text from view$ where rowid=:1 |