[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_USER_MVS
Source
1 PROCEDURE DBMS_FEATURE_USER_MVS
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 num_mv number; -- total number of user mvs (user mvs of all types)
7 num_ondmd number; -- on-demand user mvs
8 num_cmplx number; -- complex user mvs (mvs that can't be fast refreshed)
9 num_mav number; -- (user) mavs
10 num_mjv number; -- (user) mjvs
11 num_mav1 number; -- (user) mav1s
12 num_oncmt number; -- on-commit user mvs
13 num_enqrw number; -- rewrite enabled (user) mvs
14 num_rmt number; -- remote (user) mvs
15 num_pk number; -- pk (user) mvs
16 num_rid number; -- rowid (user) mvs
17 num_obj number; -- object (user) mvs
18 num_ofprf number; -- out-of-place refresh
19 num_sync number; -- sync refresh mvs
20 feature_usage varchar2(1000);
21 user_mv_test varchar2(100);
22
23 BEGIN
24 -- initialize
25 num_mv := 0;
26 num_ondmd := 0;
27 num_cmplx := 0;
28 num_mav := 0;
29 num_mjv := 0;
30 num_mav1 := 0;
31 num_oncmt := 0;
32 num_enqrw := 0;
33 num_rmt := 0;
34 num_pk := 0;
35 num_rid := 0;
36 num_obj := 0;
37 num_ofprf := 0;
38 num_sync := 0;
39 user_mv_test := ' s.sowner not in (''SYS'', ''SYSTEM'', ''SH'', ''SYSMAN'')';
40
41 feature_boolean := 0;
42 aux_count := 0;
43
44 /* get the user mv count (user mvs of all types) */
45 execute immediate 'select count(*) from dba_mviews
46 where owner not in (''SYS'', ''SYSTEM'', ''SH'', ''SYSMAN'')'
47 into num_mv;
48
49 if (num_mv > 0)
50 then
51
52 /* get number of rowid (user) mvs */
53 execute immediate 'select count(*) from snap$ s
54 where bitand(s.flag, 16) = 16 and' || user_mv_test
55 into num_rid;
56
57 /* get number of pk (user) mvs */
58 execute immediate 'select count(*) from snap$ s
59 where bitand(s.flag, 32) = 32 and' || user_mv_test
60 into num_pk;
61
62 /* get number of on-demand user mvs */
63 execute immediate 'select count(*) from snap$ s
64 where bitand(s.flag, 64) = 64 and' || user_mv_test
65 into num_ondmd;
66
67 /* get number of complex user mvs (mvs that can't be fast refreshed) */
68 execute immediate 'select count(*) from snap$ s
69 where bitand(s.flag, 256) = 256 and' || user_mv_test
70 into num_cmplx;
71
72 /* get number of (user) mavs */
73 execute immediate 'select count(*) from snap$ s
74 where bitand(s.flag, 4096) = 4096 and' || user_mv_test
75 into num_mav;
76
77 /* get number of (user) mjvs */
78 execute immediate 'select count(*) from snap$ s
79 where bitand(s.flag, 8192) = 8192 and' || user_mv_test
80 into num_mjv;
81
82 /* get number of (user) mav1s */
83 execute immediate 'select count(*) from snap$ s
84 where bitand(s.flag, 16384) = 16384 and' || user_mv_test
85 into num_mav1;
86
87 /* get number of on-commit user mvs */
88 execute immediate 'select count(*) from snap$ s
89 where bitand(s.flag, 32768) = 32768 and' || user_mv_test
90 into num_oncmt;
91
92 /* get number of rewrite enabled (user) mvs */
93 execute immediate 'select count(*) from snap$ s
94 where bitand(s.flag, 1048576) = 1048576 and' ||
95 user_mv_test
96 into num_enqrw;
97
98 /* get number of remote (user) mvs */
99 execute immediate 'select count(*) from snap$ s
100 where s.mlink is not null and' || user_mv_test
101 into num_rmt;
102
103 /* get number of object (user) mvs */
104 execute immediate 'select count(*) from snap$ s
105 where bitand(s.flag, 536870912) = 536870912 and' ||
106 user_mv_test
107 into num_obj;
108
109 /* get number of SyncRefresh mvs */
110 execute immediate 'select sum(count#) from mv_refresh_usage_stats$
111 where refresh_method# = ''SYNC'''
112
113 into num_sync;
114
115 /* get number of out-of-place refreshes */
116 execute immediate 'select sum(count#) from SYS.MV_REFRESH_USAGE_STATS$
117 where OUT_OF_PLACE# = ''YES'''
118 into num_ofprf;
119
120 feature_boolean := 1;
121
122 feature_usage := 'total number of user mvs (user mvs of all types):' || to_char(num_mv) ||
123 ',' || ' num of (user) mavs:' || to_char(num_mav) ||
124 ',' || ' num of (user) mjvs:' || to_char(num_mjv) ||
125 ',' || ' num of (user) mav1s:' || to_char(num_mav1) ||
126 ',' || ' num of on-demand user mvs:' || to_char(num_ondmd) ||
127 ',' || ' num of on-commit user mvs:' || to_char(num_oncmt) ||
128 ',' || ' num of remote (user) mvs:' || to_char(num_rmt) ||
129 ',' || ' num of pk (user) mvs:' || to_char(num_pk) ||
130 ',' || ' num of rowid (user) mvs:' || to_char(num_rid) ||
131 ',' || ' num of object (user) mvs:' || to_char(num_obj) ||
132 ',' || ' num of rewrite enabled (user) mvs:' || to_char(num_enqrw) ||
133 ',' || ' num of complex user mvs:' || to_char(num_cmplx) ||
134 ',' || ' num of out-of-place refreshes:' || to_char(num_ofprf) ||
135 ',' || ' num of SyncRefresh mvs:' || to_char(num_sync) ||
136 '.';
137
138 feature_info := to_clob(feature_usage);
139 else
140 feature_info := to_clob('User MVs do not exist.');
141 end if;
142
143 end;