[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_CDC
Source
1 PROCEDURE DBMS_FEATURE_CDC
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 num_autolog number := 0;
7 num_hotlog number := 0;
8 num_sync number := 0;
9 num_dist number := 0;
10 num_hotmine number := 0;
11
12 num_auto_sets number := 0;
13 num_hot_sets number := 0;
14 num_sync_sets number := 0;
15 num_dist_sets number := 0;
16 num_mine_sets number := 0;
17
18 num_auto_tabs number := 0;
19 num_hot_tabs number := 0;
20 num_sync_tabs number := 0;
21 num_dist_tabs number := 0;
22 num_mine_tabs number := 0;
23
24 num_auto_subs number := 0;
25 num_hot_subs number := 0;
26 num_sync_subs number := 0;
27 num_dist_subs number := 0;
28 num_mine_subs number := 0;
29
30 feature_usage varchar2(2000);
31
32 begin
33 --initialize
34 aux_count := 0;
35
36 /* get the number of total change tables and dump in aux_count */
37 begin
38 execute immediate 'select count(*) from sys.cdc_change_Tables$'
39 into aux_count;
40 exception
41 when others then
42 aux_count := 0;
43 end;
44
45 if (aux_count > 0) then
46 feature_boolean := 1;
47 else
48 feature_boolean := 0;
49 feature_info := to_clob('CDC usage not detected');
50 return;
51 end if;
52
53 /* get data for AUTOLOG */
54 begin
55 execute immediate 'select count(*) from sys.cdc_change_sources$
56 where BITAND(source_type, 2) = 2'
57 into num_autolog;
58 exception
59 when others then
60 num_autolog := 0;
61 end;
62
63 if (num_autolog > 0 ) then
64
65 begin
66 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
67 sys.cdc_change_sets$ b
68 where BITAND(a.source_type, 2) = 2 AND
69 b.change_source_name = a.source_name'
70 into num_auto_sets;
71 exception
72 when others then
73 num_auto_sets := 0;
74 end;
75
76 begin
77 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
78 sys.cdc_change_sets$ b, sys.cdc_change_tables$ c
79 where BITAND(a.source_type, 2) = 2 AND
80 b.change_source_name = a.source_name AND
81 c.change_set_name = b.set_name'
82 into num_auto_tabs;
83 exception
84 when others then
85 num_auto_tabs := 0;
86 end;
87
88 begin
89 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
90 sys.cdc_change_sets$ b, sys.cdc_subscribers$ c
91 where BITAND(a.source_type, 2) = 2 AND
92 b.change_source_name = a.source_name AND
93 c.set_name = b.set_name'
94 into num_auto_subs;
95 exception
96 when others then
97 num_auto_subs := 0;
98 end;
99
100 end if;
101
102 /* get data for HOTLOG */
103 begin
104 execute immediate 'select count(*) from sys.cdc_change_sources$
105 where BITAND(source_type, 4) = 4'
106 into num_hotlog;
107 exception
108 when others then
109 num_hotlog := 0;
110 end;
111
112 if (num_hotlog > 0 ) then
113
114 begin
115 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
116 sys.cdc_change_sets$ b
117 where BITAND(a.source_type, 4) = 4 AND
118 b.change_source_name = a.source_name'
119 into num_hot_sets;
120 exception
121 when others then
122 num_hot_sets := 0;
123 end;
124
125 begin
126 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
127 sys.cdc_change_sets$ b, sys.cdc_change_tables$ c
128 where BITAND(a.source_type, 4) = 4 AND
129 b.change_source_name = a.source_name AND
130 c.change_set_name = b.set_name'
131 into num_hot_tabs;
132 exception
133 when others then
134 num_hot_tabs := 0;
135 end;
136
137 begin
138 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
139 sys.cdc_change_sets$ b, sys.cdc_subscribers$ c
140 where BITAND(a.source_type, 4) = 4 AND
141 b.change_source_name = a.source_name AND
142 c.set_name = b.set_name'
143 into num_hot_subs;
144 exception
145 when others then
146 num_hot_subs := 0;
147 end;
148
149 end if;
150
151 /* get data for SYNCHRONOUS */
152 begin
153 execute immediate 'select count(*) from sys.cdc_change_sources$
154 where BITAND(source_type, 8) = 8'
155 into num_sync;
156 exception
157 when others then
158 num_sync := 0;
159 end;
160
161 if (num_sync > 0 ) then
162
163 begin
164 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
165 sys.cdc_change_sets$ b
166 where BITAND(a.source_type, 8) = 8 AND
167 b.change_source_name = a.source_name'
168 into num_sync_sets;
169 exception
170 when others then
171 num_sync_sets := 0;
172 end;
173
174 begin
175 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
176 sys.cdc_change_sets$ b, sys.cdc_change_tables$ c
177 where BITAND(a.source_type, 8) = 8 AND
178 b.change_source_name = a.source_name AND
179 c.change_set_name = b.set_name'
180 into num_sync_tabs;
181 exception
182 when others then
183 num_sync_tabs := 0;
184 end;
185
186 begin
187 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
188 sys.cdc_change_sets$ b, sys.cdc_subscribers$ c
189 where BITAND(a.source_type, 8) = 8 AND
190 b.change_source_name = a.source_name AND
191 c.set_name = b.set_name'
192 into num_sync_subs;
193 exception
194 when others then
195 num_sync_subs := 0;
196 end;
197
198 end if;
199
200 /* get data for DISTRIBUTED HOTLOG */
201 begin
202 execute immediate 'select count(*) from sys.cdc_change_sources$
203 where BITAND(source_type, 64) = 64'
204 into num_dist;
205 exception
206 when others then
207 num_dist := 0;
208 end;
209
210 if (num_dist > 0 ) then
211
212 begin
213 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
214 sys.cdc_change_sets$ b
215 where BITAND(a.source_type, 64) = 64 AND
216 b.change_source_name = a.source_name'
217 into num_dist_sets;
218 exception
219 when others then
220 num_dist_sets := 0;
221 end;
222
223 begin
224 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
225 sys.cdc_change_sets$ b, sys.cdc_change_tables$ c
226 where BITAND(a.source_type, 64) = 64 AND
227 b.change_source_name = a.source_name AND
228 c.change_set_name = b.set_name'
229 into num_dist_tabs;
230 exception
231 when others then
232 num_dist_tabs := 0;
233 end;
234
235 begin
236 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
237 sys.cdc_change_sets$ b, sys.cdc_subscribers$ c
238 where BITAND(a.source_type, 64) = 64 AND
239 b.change_source_name = a.source_name AND
240 c.set_name = b.set_name'
241 into num_dist_subs;
242 exception
243 when others then
244 num_dist_subs := 0;
245 end;
246
247 end if;
248
249 /* get data for DISTRIBUTED HOTMINING */
250 begin
251 execute immediate 'select count(*) from sys.cdc_change_sources$
252 where BITAND(source_type, 128) = 128'
253 into num_hotmine;
254 exception
255 when others then
256 num_hotmine := 0;
257 end;
258
259 if (num_hotmine > 0 ) then
260
261 begin
262 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
263 sys.cdc_change_sets$ b
264 where BITAND(a.source_type, 128) = 128 AND
265 b.change_source_name = a.source_name'
266 into num_mine_sets;
267 exception
268 when others then
269 num_mine_sets := 0;
270 end;
271
272 begin
273 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
274 sys.cdc_change_sets$ b, sys.cdc_change_tables$ c
275 where BITAND(a.source_type, 128) = 128 AND
276 b.change_source_name = a.source_name AND
277 c.change_set_name = b.set_name'
278 into num_mine_tabs;
279 exception
280 when others then
281 num_mine_tabs := 0;
282 end;
283
284 begin
285 execute immediate 'select count(*) from sys.cdc_change_sources$ a,
286 sys.cdc_change_sets$ b, sys.cdc_subscribers$ c
287 where BITAND(a.source_type, 128) = 128 AND
288 b.change_source_name = a.source_name AND
289 c.set_name = b.set_name'
290 into num_mine_subs;
291 exception
292 when others then
293 num_mine_subs := 0;
294 end;
295
296 end if;
297
298
299 feature_usage := 'autolog - source: ' || to_char(num_autolog) ||', '||
300 'sets: ' || to_char(num_auto_sets) ||', '||
301 'tables: ' || to_char(num_auto_tabs) ||', '||
302 'subscriptions: ' || to_char(num_auto_subs) ||', '||
303 'hotlog - source: ' || to_char(num_hotlog) ||', '||
304 'sets: ' || to_char(num_hot_sets) ||', '||
305 'tables: ' || to_char(num_hot_tabs) ||', '||
306 'subscriptions: ' || to_char(num_hot_subs) ||', '||
307 'sync - source: ' || to_char(num_sync) ||', '||
308 'sets: ' || to_char(num_sync_sets) ||', '||
309 'tables: ' || to_char(num_sync_tabs) ||', '||
310 'subscriptions: ' || to_char(num_sync_subs) ||', '||
311 'distributed - source: ' || to_char(num_dist) ||', '||
312 'sets: ' || to_char(num_dist_sets) ||', '||
313 'tables: ' || to_char(num_dist_tabs) ||', '||
314 'subscriptions: ' || to_char(num_dist_subs) ||', '||
315 'HotMine - source: ' || to_char(num_hotmine) ||', '||
316 'sets: ' || to_char(num_mine_sets) ||', '||
317 'tables: ' || to_char(num_mine_tabs) ||', '||
318 'subscriptions: ' || to_char(num_mine_subs);
319
320 feature_info := to_clob(feature_usage);
321
322 end;