1 procedure DBMS_FEATURE_XDB
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 num_xdb_res number := 0;
7 num_xdb_rc number := 0;
8 num_xdb_acl number := 0;
9 num_xdb_schemas number := 0;
10 num_sb_tbl number := 0;
11 num_xdb_tbl number := 0;
12 num_xdb_vw number := 0;
13 num_nsb_tbl number := 0;
14 num_sb_vw number := 0;
15 num_nsb_vw number := 0;
16 num_st_or number := 0;
17 num_st_lob number := 0;
18 num_st_clob number := 0;
19 num_st_bin number := 0;
20 feature_usage varchar2(1000);
21 TYPE cursor_t IS REF CURSOR;
22 cursor_objtype cursor_t;
23 total_count number := 0;
24 flag number := 0;
25 objtype number := 0;
26
27 begin
28 /* get number of non system resources from resource_view */
29 execute immediate q'[select count(*)
30 from xdb.xdb$resource e, sys.user$ u
31 where to_number(utl_raw.cast_to_binary_integer(e.xmldata.ownerid)) =
32 u.user# and u.name not in ('XDB', 'SYS', 'MDSYS', 'EXFSYS', 'ORDSYS',
33 'ORDDATA', 'OE', 'SH', 'HR', 'SCOTT') and u.name not like 'APEX_%'
34 and u.name not like 'FLOWS_%']'
35 into num_xdb_res;
36
37 /* get number of non system xml schemas registered */
38 execute immediate q'[select count(*)
39 from dba_xml_schemas
40 where owner not in ('XDB', 'SYS', 'MDSYS', 'EXFSYS', 'ORDSYS', 'ORDDATA',
41 'OE', 'SH', 'HR', 'SCOTT') and owner not like 'APEX_%'
42 and owner not like 'FLOWS_%']' into num_xdb_schemas ;
43
44 /* count non system, SB and NSB xml columns */
45 OPEN cursor_objtype FOR q'[
46 select count(*), o.type#, bitand(p.flags, 2)
47 from sys.opqtype$ p, sys.obj$ o, sys.user$ u
48 where o.obj# = p.obj# and p.type = 1 and
49 (o.type# = 2 or o.type# = 4) and
50 o.owner# = u.user# and
51 u.name not in ('XDB', 'SYS', 'MDSYS', 'EXFSYS', 'ORDSYS',
52 'ORDDATA', 'OE', 'SH', 'HR', 'SCOTT') and
53 u.name not like 'APEX_%'
54 and u.name not like 'FLOWS_%'
55 group by (bitand(p.flags, 2), o.type#)]';
56
57 LOOP
58 BEGIN
59 FETCH cursor_objtype INTO total_count, objtype, flag;
60 EXIT WHEN cursor_objtype%NOTFOUND;
61
62
63 /* get number of non schema based tables */
64 IF (flag = 0) and (objtype = 2) THEN
65 num_nsb_tbl := total_count;
66 END IF;
67
68 /* get number of non shema based views */
69 IF (flag = 0) and (objtype = 4) THEN
70 num_nsb_vw := total_count;
71 END IF;
72
73 /* get number of schema based tables */
74 IF (flag = 2) and (objtype = 2) THEN
75 num_sb_tbl := total_count;
76 END IF;
77
78 /* get number of schema based views */
79 IF (flag = 2) and (objtype = 4) THEN
80 num_sb_vw := total_count;
81 END IF;
82 END;
83 END LOOP;
84
85
86 num_xdb_vw := num_nsb_vw + num_sb_vw;
87 num_xdb_tbl := num_nsb_tbl + num_sb_tbl;
88
89 if (num_xdb_res > 0) or (num_xdb_schemas > 0) or
90 (num_xdb_vw > 0) or (num_xdb_tbl > 0) then
91
92 /* xdb is being used by user */
93 OPEN cursor_objtype FOR q'[
94 select count(*), bitand(p.flags, 69)
95 from sys.opqtype$ p, sys.user$ u, sys.obj$ o
96 where p.type = 1 and
97 (bitand(p.flags, 1) = 1 or bitand(p.flags, 4) = 4 or
98 bitand(p.flags, 68) = 68) and
99 p.obj# = o.obj# and
100 o.owner# = u.user# and
101 u.name not in ('XDB', 'SYS', 'MDSYS', 'EXFSYS', 'ORDSYS',
102 'ORDDATA', 'OE', 'SH', 'HR', 'SCOTT') and
103 u.name not like 'APEX_%'
104 and u.name not like 'FLOWS_%'
105 group by (bitand(p.flags, 69))]';
106
107 LOOP
108 BEGIN
109 FETCH cursor_objtype INTO total_count, flag;
110 EXIT WHEN cursor_objtype%NOTFOUND;
111
112 /* get number of xmltype columns stored as object */
113 IF flag = 1 THEN
114 num_st_or := total_count;
115 END IF;
116
117 /* get number of xmltype columns stored as lob */
118 IF flag = 4 THEN
119 num_st_clob := total_count;
120 END IF;
121
122 /* get number of xmltype columns stored as binary */
123 IF flag = 68 THEN
124 num_st_bin := total_count;
125 END IF;
126 END;
127 END LOOP;
128
129 /* get number of resconfigs */
130 execute immediate 'select count(*) from xdb.xdb$resconfig' into
131 num_xdb_rc;
132 /* get number of acls */
133 execute immediate 'select count(*) from xdb.xdb$acl' into
134 num_xdb_acl;
135
136
137 feature_boolean := 1;
138 aux_count := 0;
139
140 feature_usage := chr(10) ||
141 '<xdb_feature_usage>'||
142 chr(10)||chr(32)||chr(32)||
143 '<user_resources> '|| to_char(num_xdb_res) ||
144 ' </user_resources>'||
145 chr(10) ||chr(32)||chr(32)||
146 '<user_schemas> '|| to_char(num_xdb_schemas) ||
147 ' </user_schemas>'||
148 chr(10)||chr(32)||chr(32)||
149 '<user_SB_columns> '|| to_char(num_sb_tbl) ||
150 ' </user_SB_columns>'||
151 chr(10)||chr(32)||chr(32)||
152 '<user_NSB_columns> '|| to_char(num_nsb_tbl) ||
153 ' </user_NSB_columns>'||
154 chr(10)||chr(32)||chr(32)||
155 '<user_SB_views> '|| to_char(num_sb_vw) ||
156 ' </user_SB_views>'||
157 chr(10)||chr(32)||chr(32)||
158 '<user_NSB_views> '|| to_char(num_nsb_vw) ||
159 ' </user_NSB_views>'||
160 chr(10)||chr(32)||chr(32)||
161 '<user_OR_cols> '|| to_char(num_st_or) ||
162 ' </user_OR_cols>'||
163 chr(10)||chr(32)||chr(32)||
164 '<user_CLOB_cols> '|| to_char(num_st_clob) ||
165 ' </user_CLOB_cols>'||
166 chr(10)||chr(32)||chr(32)||
167 '<user_BINARY_cols> '|| to_char(num_st_bin) ||
168 ' </user_BINARY_cols>'||
169 chr(10)||chr(32)||chr(32)||
170 '<all_resconfigs> '|| to_char(num_xdb_rc) ||
171 ' </all_resconfigs>'||
172 chr(10)||chr(32)||chr(32)||
173 '<all_acls> '|| to_char(num_xdb_acl) ||
174 ' </all_acls>'||
175 chr(10) ||
176 '</xdb_feature_usage>';
177
178 feature_info := to_clob(feature_usage);
179 else
180 feature_boolean := 0;
181 aux_count := 0;
182 feature_info :=
183 to_clob('<xdb_feature_usage>SYSTEM</xdb_feature_usage>');
184 end if;
185
186 end;
[Home] [Help]