-
Notifications
You must be signed in to change notification settings - Fork 64
/
cs_dbc_snapshot_log_v.sql
executable file
·121 lines (121 loc) · 3.24 KB
/
cs_dbc_snapshot_log_v.sql
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
----------------------------------------------------------------------------------------
--
-- File name: cs_dbc_snapshot_log_v.sql
--
-- Purpose: DBC Snapshot Log Report
--
-- Author: Carlos Sierra
--
-- Version: 2020/12/06
--
-- Usage: Execute connected to CDB
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_dbc_snapshot_log_v.sql
--
-- Notes: Developed and tested on 12.1.0.2.
--
---------------------------------------------------------------------------------------
SET HEA ON LIN 2490 PAGES 100 TAB OFF FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF LONG 240000 LONGC 2400 NUM 20 SERVEROUT OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
--
COL view_owner NEW_V view_owner NOPRI;
SELECT owner AS view_owner FROM dba_views WHERE view_name = 'DBC_SNAPSHOT_LOG_V';
--
COL table_name FOR A30;
COL region_acronym FOR A3 HEA 'RGN';
COL locale FOR A6;
COL runs FOR 999,990;
COL avg_secs FOR 999,990;
COL secs_per_day FOR 999,999,990;
COL total_rows_processed FOR 999,999,999,990;
COL errors FOR 99,990;
COL seconds FOR 999,990;
COL last_rows_processed FOR 999,999,999,990;
COL last_error_stack FOR A100;
--
BREAK ON REPORT;
COMPUTE SUM LABEL "TOTAL" OF runs secs_per_day total_rows_processed errors seconds last_rows_processed ON REPORT;
--
PRO
PRO Source (from CDB)
PRO ~~~~~~
SELECT --region_acronym,
--locale,
--db_name,
table_name,
runs,
first_time,
avg_secs,
secs_per_day,
errors,
total_rows_processed,
last_begin_time,
last_end_time,
seconds,
last_rows_processed,
last_error_stack
FROM &&view_owner..dbc_snapshot_log_v
/
--
COL min_since_last FOR 9,999,990.00 HEA 'MINUTES_AGO';
COL min_until_next FOR 9,999,990.00 HEA 'MINUTES_2GO';
COL avg_rows FOR 999,999,999,990;
COL to_key FOR A14;
COL error_message FOR A100;
--
BREAK ON REPORT;
COMPUTE SUM LABEL "TOTAL" OF runs total_rows_processed errors seconds ON REPORT;
--
PRO
PRO Target (into OMR)
PRO ~~~~~~
SELECT s.collect_name AS table_name,
s.collections AS runs,
s.first_time,
s.last_time,
s.status,
s.min_since_last,
s.min_until_next,
s.errors,
s.total_rows AS total_rows_processed,
s.avg_rows,
s.elap_sec AS seconds,
s.avg_sec AS avg_secs,
--from_key,
s.to_key,
h.error_message
FROM &&view_owner..iod_metadata_ctl_summ s, &&view_owner..iod_metadata_ctl_hist h
WHERE h.collect_name = s.collect_name
AND h.start_time = s.last_time
ORDER BY s.collect_name
/
--
CLEAR BREAK;
--
COL job_name FOR A30;
COL job_action FOR A40;
COL repeat_interval FOR A30;
COL enabled FOR A7;
COL last_start_date FOR A19;
COL last_run_secs FOR 999,990.000;
COL next_run_date FOR A19;
--
PRO
PRO DBA_SCHEDULER_JOBS
PRO ~~~~~~~~~~~~~~~~~~
SELECT job_name,
job_type,
job_action,
repeat_interval,
enabled,
state,
run_count,
EXTRACT(SECOND FROM last_run_duration) last_run_secs,
TO_CHAR(last_start_date, 'YYYY-MM-DD"T"HH24:MI:SS') last_start_date,
TO_CHAR(next_run_date, 'YYYY-MM-DD"T"HH24:MI:SS') next_run_date
FROM dba_scheduler_jobs
WHERE job_name LIKE 'IOD_META%'
ORDER BY
job_name
/