[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_DEFERRED_SEG_CRT
Source
1 PROCEDURE DBMS_FEATURE_DEFERRED_SEG_CRT
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 feature_usage VARCHAR2(1000);
7 table_count NUMBER;
8 index_count NUMBER;
9 lob_count NUMBER;
10 tabpart_count NUMBER;
11 indpart_count NUMBER;
12 lobpart_count NUMBER;
13 tabsubpart_count NUMBER;
14 indsubpart_count NUMBER;
15 lobsubpart_count NUMBER;
16 total_segments NUMBER;
17 total_def_segments NUMBER;
18 BEGIN
19 -- initialize
20 feature_boolean := 0;
21 aux_count := 0;
22 feature_info := NULL;
23 feature_usage := NULL;
24 table_count := 0;
25 index_count := 0;
26 lob_count := 0;
27 tabpart_count := 0;
28 indpart_count := 0;
29 lobpart_count := 0;
30 tabsubpart_count := 0;
31 indsubpart_count := 0;
32 lobsubpart_count := 0;
33 total_segments := 0;
34 total_def_segments := 0;
35
36 -- check to see if DSC parameter is turned on
37 select count(*) into feature_boolean from v$system_parameter where
38 name = 'deferred_segment_creation' and value = 'TRUE';
39
40 -- Regardless of the value of the parameter, compute the number of
41 -- objects that do not yet have segments created
42
43 -- non-partitioned tables
44 -- select count(*) into table_count from dba_tables where
45 -- segment_created = 'NO';
46
47 select count(*) into table_count from
48 ( select decode(bitand(t.property, 17179869184), 17179869184, 'NO',
49 decode(bitand(t.property, 32), 32, 'N/A', 'YES')) x
50 from tab$ t
51 )
52 where x = 'NO';
53
54 -- non-partitioned indexes
55 -- select count(*) into index_count from dba_indexes where
56 -- segment_created = 'NO';
57
58 select count(*) into index_count from
59 ( select decode(bitand(i.flags, 67108864), 67108864, 'NO','?') x
60 from ind$ i
61 )
62 where x = 'NO';
63
64 -- non-partitioned lobs
65 -- select count(*) into lob_count from dba_lobs where
66 -- segment_created = 'NO';
67
68 select count(*) into lob_count from
69 ( select decode(bitand(l.property, 4096), 4096, 'NO','?') x
70 from lob$ l
71 )
72 where x = 'NO';
73
74 -- table partitions
75 -- select count(*) into tabpart_count from dba_tab_partitions where
76 -- segment_created = 'NO';
77
78 select count(*) into tabpart_count from
79 ( select decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES') x
80 from tabpart$ tp
81 ) where x = 'NO';
82
83 -- index partitions
84 -- select count(*) into indpart_count from dba_ind_partitions where
85 -- segment_created = 'NO';
86
87 select count(*) into indpart_count from
88 ( select decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES') x
89 from indpart$ ip
90 ) where x = 'NO';
91
92 -- lob partitions
93 -- select count(*) into lobpart_count from dba_lob_partitions where
94 -- segment_created = 'NO';
95
96 select count(*) into lobpart_count from
97 ( select decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') x
98 from lobfrag$ lf where lf.fragtype$='P'
99 ) where x = 'NO';
100
101 -- table sub-partitions
102 -- select count(*) into tabsubpart_count from dba_tab_subpartitions where
103 -- segment_created = 'NO';
104
105 select count(*) into tabsubpart_count from
106 ( select decode(bitand(tsp.flags, 65536), 65536, 'NO', 'YES') x
107 from tabsubpart$ tsp
108 ) where x = 'NO';
109
110 -- index sub-partitions
111 -- select count(*) into indsubpart_count from dba_ind_subpartitions where
112 -- segment_created = 'NO';
113
114 select count(*) into indsubpart_count from
115 ( select decode(bitand(isp.flags, 65536), 65536, 'NO', 'YES') x
116 from indsubpart$ isp
117 ) where x = 'NO';
118
119 -- lob sub-partitions
120 -- select count(*) into lobsubpart_count from dba_lob_subpartitions where
121 -- segment_created = 'NO';
122
123 select count(*) into lobsubpart_count from
124 ( select decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') x
125 from lobfrag$ lf where lf.fragtype$='S'
126 ) where x = 'NO';
127
128 -- Total segments of objects which can have deferred segment creation
129 -- select count(*) into total_segments from dba_segments where
130 -- segment_type IN ('TABLE',
131 -- 'INDEX',
132 -- 'LOBSEGMENT',
133 -- 'LOBINDEX',
134 -- 'TABLE PARTITION',
135 -- 'INDEX PARTITION',
136 -- 'LOB PARTITION' );
137
138 select count(*) into total_segments from seg$ where type# in (5,6,8);
139
140 -- Total # of segments whose creation is deferred
141 total_def_segments := table_count + index_count + lob_count +
142 tabpart_count + indpart_count + lobpart_count +
143 tabsubpart_count + indsubpart_count + lobsubpart_count;
144
145 feature_usage := feature_usage || 'Deferred Segment Creation ' ||
146 ' Parameter:' || feature_boolean ||
147 ' Total Deferred Segments:' || total_def_segments ||
148 ' Total Created Segments:' || total_segments ||
149 ' Table Segments:' || table_count ||
150 ' Index Segments:' || index_count ||
151 ' Lob Segments:' || lob_count ||
152 ' Table Partition Segments:' || tabpart_count ||
153 ' Index Partition Segments:' || indpart_count ||
154 ' Lob Partition Segments:' || lobpart_count ||
155 ' Table SubPartition Segments:' || tabsubpart_count ||
156 ' Index SubPartition Segments:' || indsubpart_count ||
157 ' Lob SubPartition Segments:' || lobsubpart_count;
158
159 -- update feature_boolean if even one segment is uncreated
160 if (total_def_segments > 0) then
161 feature_boolean := feature_boolean+1;
162 end if;
163
164 feature_info := to_clob(feature_usage);
165 aux_count := total_def_segments;
166
167 END dbms_feature_deferred_seg_crt;