1 PROCEDURE dbms_feature_partition_user
2 (is_used OUT number, data_ratio OUT number, clob_rest OUT clob)
3 AS
4 BEGIN
5 -- initialize
6 is_used := 0;
7 data_ratio := 0;
8 clob_rest := NULL;
9
10 FOR crec IN (select num||':'||idx_or_tab||':'||ptype||':'||subptype||':'||pcnt||':'||subpcnt||':'||
11 pcols||':'||subpcols||':'||idx_flags||':'||
12 idx_type||':'||idx_uk||':'||rpcnt||':'||rsubpcnt||':'|| def_segment_creation||':'||
13 partial_idx||':'||orphaned_entries ||':'||zonemap||':'||attrcluster||':'||
14 subpartemp || '|' my_string
15 from (select dense_rank() over (order by decode(bo#,null,pobj#,bo#)) NUM,
16 idx_or_tab,
17 ptype, pcols, pcnt, rpcnt,
18 subptype, subpcols, subpartemp, subpcnt, rsubpcnt,
19 idx_flags, idx_type, idx_uk, orphaned_entries,
20 def_segment_creation, partial_idx,
21 zonemap, attrcluster
22 from
23 ( select /*+ full(o) */ o.obj#, i.bo#, p.obj# pobj#,
24 decode(o.type#,1,'I',2,'T',null) IDX_OR_TAB,
25 is_xml ||
26 decode(p.parttype, 1, case when bitand(p.flags,64)=64 then
27 -- INTERVAL-REF, 12c
28 case when bitand(p.flags,32)=32 then 'INT-REF'
29 else 'INTERVAL' end
30 else 'RANGE' end
31 ,2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REF'
32 ,p.parttype||'-?') ||
33 decode(bitand(p.flags,32),32,' (PARENT)') PTYPE,
34 decode(mod(p.spare2, 256), 0, null, 1, 'RANGE', 2, 'HASH', 3,'SYSTEM'
35 , 4, 'LIST', 5, 'REF'
36 , p.spare2||'-?') SUBPTYPE,
37 p.partcnt PCNT,
38 mod(trunc(p.spare2/65536), 65536) SUBPCNT,
39 p.partkeycols PCOLS,
40 case mod(trunc(p.spare2/256), 256)
41 when 0 then null
42 else mod(trunc(p.spare2/256), 256) end SUBPCOLS,
43 case when bitand(p.flags,1) = 1 then
44 case when bitand(p.flags,2) = 2 then 'LP'
45 else 'L' end
46 when bitand(p.flags,2) = 2 then 'GP'
47 end IDX_FLAGS,
48 decode(i.type#, 1, 'NORMAL'||
49 decode(bitand(i.property, 4), 0, '', 4, '/REV'),
50 2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
51 5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
52 9, 'DOMAIN') ||
53 case when bitand(i.property,16) = 16
54 then '-FUNC' end IDX_TYPE,
55 decode(i.property, null,null,
56 decode(bitand(i.property, 1), 0, 'NU',
57 1, 'U', '?')) IDX_UK,
58 -- real partition and subpartition count
59 case when bitand(p.flags,64)=64 then op.xnumpart else p.partcnt end RPCNT,
60 osp.numsubpart RSUBPCNT,
61 -- deferred segments
62 case o.type#
63 when 1 then --index
64 decode(ip_seg_off,null,isp_seg_off,ip_seg_off)
65 when 2 then --table
66 decode(tp_seg_off,null,tsp_seg_off,tp_seg_off)
67 else null end DEF_SEGMENT_CREATION,
68 -- partial indexing
69 case o.type#
70 when 1 then --index
71 decode(bitand(i.flags, 8388608), 8388608, 'PARTIAL', 'FULL')||'-'||
72 -- overload field with count of all [sub]partitions with indexing off
73 decode(ip_idx_off,null,isp_idx_off,ip_idx_off)
74 when 2 then --table
75 decode(bitand(p.flags,8192),8192,'OFF','ON')||'-'||
76 -- overload field with count of all [sub]partitions with indexing off
77 decode(tp_idx_off,null,tsp_idx_off,tp_idx_off)
78 else null end PARTIAL_IDX,
79 null ORPHANED_ENTRIES,
80 decode(zonemap,null,'N',zonemap) ZONEMAP,
81 decode(attrcluster,null,'N',attrcluster) ATTRCLUSTER,
82 st_part SUBPARTEMP
83 from partobj$ p, obj$ o, user$ u, ind$ i,
84 ( select distinct obj#, 'XML-' as is_xml from opqtype$ where type=1) xml,
85 -- real subpartition count for tables and indexes
86 ( select /* NO_MERGE FULL(tsp) FULL(tcp) */ tcp.bo#, count(*) numsubpart
87 from tabsubpart$ tsp, tabcompart$ tcp
88 where tcp.obj# = tsp.pobj#
89 group by tcp.bo#
90 union all
91 select /* NO_MERGE FULL(isp) FULL(icp) */ icp.bo#, count(*) numsubpart
92 from indsubpart$ isp, indcompart$ icp
93 where icp.obj# = isp.pobj#
94 group by icp.bo#) osp,
95 -- real partition count for tables and indexes
96 ( select tp.bo#, count(*) xnumpart
97 from tabpart$ tp
98 group by tp.bo#
99 union all
100 select ip.bo#, count(*) xnumpart
101 from indpart$ ip
102 group by ip.bo#) op,
103 -- details table partitions: partial indexing and deferred segments
104 ( select tp.bo#,
105 -- number or partitions with indexing off
106 sum(decode(bitand(tp.flags, 2097152), 2097152, 1, 0)) tp_idx_off,
107 -- number or partitions with deferred segment creation
108 sum(decode(bitand(tp.flags, 65536), 65536, 1, 0)) tp_seg_off
109 from tabpart$ tp
110 group by tp.bo#) pxd,
111 -- details table subpartitions: partial indexing and deferred segments
112 ( select tcp.bo#,
113 -- number or subpartitions with indexing off
114 sum(decode(bitand(tsp.flags, 2097152), 2097152, 1, 0)) tsp_idx_off,
115 -- number or subpartitions with deferred segment creation
116 sum(decode(bitand(tsp.flags, 65536), 65536, 1, 0)) tsp_seg_off
117 from tabsubpart$ tsp, tabcompart$ tcp
118 where tcp.obj# = tsp.pobj#
119 group by tcp.bo#) spxd,
120 -- details index partitions: partial indexing and deferred segments
121 ( select ip.bo#,
122 -- number or partitions with indexing off
123 sum(decode(bitand(ip.flags, 1), 1, 1, 0)) ip_idx_off,
124 -- number or partitions with deferred segment creation
125 sum(decode(bitand(ip.flags, 65536), 65536, 1, 0)) ip_seg_off
126 from indpart$ ip
127 group by ip.bo#) ipd,
128 -- details index subpartitions: partial indexing and deferred segments
129 ( select icp.bo#,
130 -- number or subpartitions with indexing off
131 sum(decode(bitand(isp.flags, 1), 1, 1, 0)) isp_idx_off,
132 -- number or subpartitions with deferred segment creation
133 sum(decode(bitand(isp.flags, 65536), 65536, 1, 0)) isp_seg_off
134 from indsubpart$ isp, indcompart$ icp
135 where icp.obj# = isp.pobj#
136 group by icp.bo#) ispd,
137 -- attribute clustering
138 ( select c.clstobj#, 'Y-'||
139 -- kind of attribute clustering
140 case when ctable is not null
141 then 'MT-' else 'ST-' end
142 ||
143 case when clstfunc = 1 then 'I-' -- interleaved
144 else 'L-' end -- linear
145 ||to_char(decode(ctable, null,0,ctable)+1)||'-'||ccol as ATTRCLUSTER
146 from sys.clst$ c,
147 -- count of tables and columns used for attribute clustering
148 -- no detailed breakdown of columns per row
149 -- table count does not include fact table for hierarchical attr. clustering
150 ( select clstobj#, count(intcol#) ccol
151 from sys.clstkey$
152 group by clstobj#) k,
153 ( select clstobj#, count(*) ctable
154 from sys.clstjoin$
155 group by clstobj#) kt
156 where c.clstobj# = k.clstobj#
157 and c.clstobj# = kt.clstobj#(+)) attrcl,
158 -- zone maps
159 (select detailobj#, zonemap from
160 (select sd.detailobj#, flags, 'Y-'||
161 -- single table zonemap or hierarchical zonemap
162 decode(bitand(sn.flag3, 1024),
163 0, 'ST', 'MT') ||
164 -- number of tables and columns in zonemap (aggr, no detailed breakdown)
165 -- table count does not include fact table for hierarch. zonemap
166 '-'|| count(distinct sd.detailobj#) over (partition by sd.sumobj#) ||
167 '-'|| sa.zmcol as ZONEMAP
168 from sys.sumdetail$ sd, sys.sum$ s, sys.snap$ sn,
169 ( select sumobj#, count(*) zmcol
170 from sys.sumagg$
171 where aggfunction = 18
172 group by sumobj#) sa
173 where s.obj# = sd.sumobj#
174 and s.obj# = sa.sumobj#
175 and s.containernam(+) = sn.vname) v
176 where bitand(v.flags, 2) = 2 /* zonemap fact table */
177 ) zm,
178 ( select bo#, count(*) st_part
179 from defsubpart$
180 group by bo# ) spt
181 where o.obj# = i.obj#(+)
182 and o.owner# = u.user#
183 and p.obj# = o.obj#
184 and p.obj# = xml.obj#(+)
185 and p.obj# = osp.bo#(+)
186 and p.obj# = op.bo#(+)
187 and p.obj# = pxd.bo#(+)
188 and p.obj# = spxd.bo#(+)
189 and p.obj# = ipd.bo#(+)
190 and p.obj# = ispd.bo#(+)
191 and p.obj# = spt.bo#(+)
192 and o.obj# = attrcl.clstobj#(+)
193 and o.obj# = zm.detailobj#(+)
194 -- bug 14369338, exclude AUDSYS
195 and u.name not in ('SYS','SYSTEM','SH','SYSMAN','AUDSYS')
196 -- fix bug 3074607 - filter on obj$
197 and o.type# in (1,2,19,20,25,34,35)
198 -- exclude flashback data archive tables
199 -- fix bug 14666795
200 and o.name not like 'SYS_FBA%'
201 -- crystal clear identification of FBA tables deemed as too expensive
202 -- and o.obj# not in (select obj# from tab$ where bitand(property,8589934592)=8589934592)
203 -- exclude change tables
204 and o.obj# not in ( select obj# from cdc_change_tables$)
205 -- exclude local partitioned indexes on change tables
206 and i.bo# not in ( select obj# from cdc_change_tables$)
207 union all
208 -- global nonpartitioned indexes on partitioned tables
209 select o.obj#, i.bo#, p.obj# pobj#,
210 'I' IDX_OR_TAB,
211 null,null,null,null,
212 case cols when 0 then null
213 else cols end PCOLS,null,
214 'GNP' IDX_FLAGS,
215 decode(i.type#, 1, 'NORMAL'||
216 decode(bitand(i.property, 4), 0, '', 4, '/REV'),
217 2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
218 5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
219 9, 'DOMAIN') ||
220 case when bitand(i.property,16) = 16 then '-FUNC' end IDX_TYPE,
221 decode(i.property, null,null,
222 decode(bitand(i.property, 1), 0, 'NU',
223 1, 'U', '?')) IDX_UK,
224 null, null,
225 null DEF_SEGMENT_CREATION,
226 -- decode(bitand(p.flags,6144),4096,'YES',2048,'NO','NONE') DEF_SEGMENT_CREATION,
227 decode(bitand(i.flags, 8388608), 8388608, 'PARTIAL', 'FULL') PARTIAL_IDX,
228 decode(bitand(i.flags, 268435456), 268435456, 'YES', 'NO') ORPHANED_ENTRIES,
229 NULL ZONEMAP, NULL ATTRCLUSTER,
230 NULL SUBPARTEMP
231 from partobj$ p, user$ u, obj$ o, ind$ i
232 where p.obj# = i.bo#
233 -- exclude global nonpartitioned indexes on change tables
234 and i.bo# not in ( select obj# from cdc_change_tables$)
235 -- exclude flashback data archive tables
236 and o.name not like 'SYS_FBA%'
237 and o.owner# = u.user#
238 and p.obj# = o.obj#
239 -- nonpartitioned index
240 and bitand(i.property, 2) <>2
241 -- bug 14369338, exclude AUDSYS
242 and u.name not in ('SYS','SYSTEM','SH','SYSMAN','AUDSYS'))
243 order by num, idx_or_tab desc
244 )) LOOP
245
246 if (is_used = 0) then
247 is_used:=1;
248 end if;
249
250 clob_rest := clob_rest||crec.my_string;
251 end loop;
252
253 if (is_used = 1) then
254 select pcnt into data_ratio
255 from
256 (
257 SELECT c1, TRUNC((ratio_to_report(sum_blocks) over())*100,2) pcnt
258 FROM
259 (
260 select decode(p.obj#,null,'REST','PARTTAB') c1, sum(s.blocks) sum_blocks
261 from tabpart$ p, seg$ s
262 where s.file#=p.file#(+)
263 and s.block#=p.block#(+)
264 and s.type#=5
265 group by decode(p.obj#,null,'REST','PARTTAB')
266 )
267 )
268 where c1 = 'PARTTAB';
269 end if;
270 end;
[Home] [Help]