1 PROCEDURE DBMS_FEATURE_SFDEDUP_USR
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_count NUMBER;
7 BEGIN
8 -- initialize
9 feature_info := NULL;
10 feature_count := 0;
11
12 -- skip internal usage by flashback archive
13 select count(*) into feature_count from (
14 select l.obj#, l.lobj#, l.lobj#, l.lobj#, 'U' fragtype
15 from tab$ t, lob$ l, obj$ o
16 where l.obj#=t.obj# and
17 decode(bitand(l.property, 2048), 0, 'NO', 'YES')='YES' and
18 decode(bitand(l.flags, 458752), 0, 'NO', 'YES')='YES' and
19 decode(bitand(t.property, 8589934592), 0, 'NO', 'YES')='NO' and
20 o.obj# = t.obj# and
21 o.owner# not in (select user# from user$
22 where name in ('SYS', 'SYSTEM', 'XDB'))
23 union
24 select pl.tabobj#, pl.lobj#, fragobj#, parentobj#, fragtype$
25 from lobfrag$ lf, partlob$ pl, tab$ t, obj$ o
26 where decode(bitand(lf.fragpro, 2048), 0, 'NO', 'YES')='YES' and
27 decode(bitand(lf.fragflags, 458752), 0, 'NO', 'YES')='YES' and
28 lf.parentobj#=pl.lobj# and pl.tabobj#=t.obj# and
29 decode(bitand(t.property, 8589934592), 0, 'NO', 'YES')='NO' and
30 o.obj# = t.obj# and
31 o.owner# not in (select user# from user$
32 where name in ('SYS', 'SYSTEM', 'XDB'))
33 union
34 select l.obj#, lc.lobj#, fragobj#, parentobj#, fragtype$
35 from lobfrag$ lf, lobcomppart$ lc, lob$ l, tab$ t, obj$ o
36 where decode(bitand(lf.fragpro, 2048), 0, 'NO', 'YES')='YES' and
37 decode(bitand(lf.fragflags, 458752), 0, 'NO', 'YES')='YES' and
38 lf.parentobj#=lc.partobj# and l.lobj#=lc.lobj# and
39 t.obj#=l.obj# and
40 decode(bitand(t.property, 8589934592), 0, 'NO', 'YES')='NO' and
41 o.obj# = t.obj# and
42 o.owner# not in (select user# from user$
43 where name in ('SYS', 'SYSTEM', 'XDB'))
44 );
45
46 feature_boolean := feature_count;
47 aux_count := feature_count;
48
49 END;