1 package body dbms_objects_utils is
2
3 -- This procedure upgrades the type dictionary images from 8.0 to 8.1. The
4 -- dictionary tables that could be in 8.0 are kottd$, kottb$, kottbx$,
5 -- kotad$ and kotmd$, which existed in 8.0 or 8.1, when 8.0 compatibility
6 -- was possible, kotadx$ was created in 9iR2 when we required 8.1 minimum
7 -- compatibility.
8
9 procedure upgrade_dict_image is
10
11 oldimg number;
12 origcnt number;
13 inscnt number;
14 begin
15
16 oldimg := 0;
17 -- first make sure that there are dictionary tables in 8.0 format
18 execute immediate 'select bitand(flags, 128) from coltype$ where
19 obj#=(select obj# from obj$ where name=''KOTTD$'' and owner#=0)' into oldimg;
20 if (oldimg = 0) then
21 dbms_output.put_line ('No type dictionary table to upgrade');
22 return;
23 end if;
24
25 execute immediate 'alter session set events ''22372 trace name
26 context forever''';
27
28 -- upgrade kottd$
29
30 dbms_output.put_line('Upgrading kottd$ ...');
31 execute immediate 'create table kottd_temp$ of kottd';
32 execute immediate 'select count(*) from kottd$' into origcnt;
33 execute immediate 'insert into kottd_temp$(sys_nc_oid$, sys_nc_rowinfo$)
34 select sys_nc_oid$, sys_nc_rowinfo$ from kottd$';
35 commit;
36 execute immediate 'update coltype$ set flags=flags-bitand(flags,128)
37 where obj#=(select obj# from obj$ where name=''KOTTD$'' and owner#=0)';
38 commit;
39 execute immediate 'alter system flush shared_pool';
40 --error expected discarding it
41 -- it is just to build the cursor
42 execute immediate 'begin insert into kottd$(sys_nc_oid$, sys_nc_rowinfo$)
43 select sys_nc_oid$, sys_nc_rowinfo$ from kottd_temp$;
44 exception when others then null; end;';
45 execute immediate 'delete from kottd$';
46 execute immediate 'begin insert into kottd$(sys_nc_oid$, sys_nc_rowinfo$)
47 select sys_nc_oid$, sys_nc_rowinfo$ from kottd_temp$;
48 exception when others then null; end;';
49 execute immediate 'select count(*) from kottd$' into inscnt;
50 if(origcnt = inscnt) then
51 commit;
52 dbms_output.put_line('kottd$ Upgraded ...');
53 execute immediate 'drop table kottd_temp$';
54 else
55 dbms_output.put_line('Upgrade failed for kottd$ ...');
56 rollback;
57 return;
58 end if;
59 -- now kottb$
60
61 dbms_output.put_line('Upgrading kottb$ ...');
62 origcnt := 0;
63 inscnt := 0;
64
65 execute immediate 'create table kottb_temp$ of kottb';
66 execute immediate 'insert into kottb_temp$(sys_nc_oid$, sys_nc_rowinfo$)
67 select sys_nc_oid$, sys_nc_rowinfo$ from kottb$';
68 execute immediate 'select count(*) from kottb$' into origcnt;
69 commit;
70 execute immediate 'update coltype$ set flags=flags-bitand(flags,128)
71 where obj#=(select obj# from obj$ where name=''KOTTB$'' and owner#=0)';
72 commit;
73 execute immediate 'alter system flush shared_pool';
74 execute immediate 'begin insert into kottb$(sys_nc_oid$, sys_nc_rowinfo$)
75 select sys_nc_oid$, sys_nc_rowinfo$ from kottb_temp$ ; exception when
76 others then null;end;';
77 execute immediate 'delete from kottb$';
78 execute immediate 'begin insert into kottb$(sys_nc_oid$, sys_nc_rowinfo$)
79 select sys_nc_oid$, sys_nc_rowinfo$ from kottb_temp$ ; exception when
80 others then null;end;';
81 execute immediate 'select count(*) from kottb$' into inscnt;
82 if(inscnt = origcnt) then
83 commit;
84 dbms_output.put_line('kottb$ Upgraded ...');
85 execute immediate 'drop table kottb_temp$';
86 else
87 dbms_output.put_line('Upgrade failed for kottb$ ...');
88 rollback;
89 return;
90 end if;
91
92
93 -- now kottbx$
94
95 dbms_output.put_line('Upgrading kottbx$ ...');
96 origcnt := 0;
97 inscnt := 0;
98
99 execute immediate 'create table kottbx_temp$ of kottbx';
100 execute immediate 'insert into kottbx_temp$(sys_nc_oid$, sys_nc_rowinfo$)
101 select sys_nc_oid$, sys_nc_rowinfo$ from kottbx$';
102 execute immediate 'select count(*) from kottbx$' into origcnt;
103 commit;
104 execute immediate 'update coltype$ set flags=flags-bitand(flags,128)
105 where obj#=(select obj# from obj$ where name=''KOTTBX$'' and owner#=0)';
106 execute immediate 'alter system flush shared_pool';
107 execute immediate 'begin insert into kottbx$(sys_nc_oid$, sys_nc_rowinfo$)
108 select sys_nc_oid$, sys_nc_rowinfo$ from kottbx_temp$ ; exception when
109 others then null; end;';
110 commit;
111 execute immediate 'delete from kottbx$';
112 execute immediate 'begin insert into kottbx$(sys_nc_oid$, sys_nc_rowinfo$)
113 select sys_nc_oid$, sys_nc_rowinfo$ from kottbx_temp$ ; exception when
114 others then null; end;';
115 execute immediate 'select count(*) from kottbx$' into inscnt;
116 if(inscnt = origcnt) then
117 commit;
118 dbms_output.put_line('kottbx$ Upgraded ...');
119 execute immediate 'drop table kottbx_temp$';
120 else
121 dbms_output.put_line('Upgrade failed for kottbx$ ...');
122 rollback;
123 return;
124 end if;
125
126 -- now kotad$
127 dbms_output.put_line('Upgrading kotad$ ...');
128 origcnt := 0;
129 inscnt := 0;
130
131 execute immediate 'create table kotad_temp$ of kotad';
132 execute immediate 'insert into kotad_temp$(sys_nc_oid$, sys_nc_rowinfo$)
133 select sys_nc_oid$, sys_nc_rowinfo$ from kotad$';
134 commit;
135 execute immediate 'select count(*) from kotad$' into origcnt;
136 execute immediate 'update coltype$ set flags=flags-bitand(flags,128)
137 where obj#=(select obj# from obj$ where name=''KOTAD$'' and owner#=0)';
138 execute immediate 'alter system flush shared_pool';
139
140 execute immediate 'begin insert into kotad$(sys_nc_oid$, sys_nc_rowinfo$)
141 select sys_nc_oid$, sys_nc_rowinfo$ from kotad_temp$; exception when others
142 then null; end;';
143 commit;
144 execute immediate 'delete from kotad$';
145 execute immediate 'begin insert into kotad$(sys_nc_oid$, sys_nc_rowinfo$)
146 select sys_nc_oid$, sys_nc_rowinfo$ from kotad_temp$; exception when others
147 then null; end;';
148 execute immediate 'select count(*) from kotad$' into inscnt;
149
150 if(inscnt = origcnt) then
151 commit;
152 dbms_output.put_line('kotad$ Upgraded ...');
153 execute immediate 'drop table kotad_temp$';
154 else
155 dbms_output.put_line('Upgrade failed for kotad$ ...');
156 rollback;
157 return;
158 end if;
159
160
161 -- now kotmd$
162
163 dbms_output.put_line('Upgrading kotmd$ ...');
164 origcnt := 0;
165 inscnt := 0;
166
167 execute immediate 'create table kotmd_temp$ of kotmd';
168 execute immediate 'insert into kotmd_temp$(sys_nc_oid$, sys_nc_rowinfo$)
169 select sys_nc_oid$, sys_nc_rowinfo$ from kotmd$';
170 commit;
171 execute immediate 'select count(*) from kotmd$' into origcnt;
172 execute immediate 'update coltype$ set flags=flags-bitand(flags,128)
173 where obj#=(select obj# from obj$ where name=''KOTMD$'' and owner#=0)';
174 execute immediate 'alter system flush shared_pool';
175 execute immediate 'begin insert into kotmd$(sys_nc_oid$, sys_nc_rowinfo$)
176 select sys_nc_oid$, sys_nc_rowinfo$ from kotmd_temp$; exception when others
177 then null; end;';
178 execute immediate 'delete from kotmd$';
179 execute immediate 'begin insert into kotmd$(sys_nc_oid$, sys_nc_rowinfo$)
180 select sys_nc_oid$, sys_nc_rowinfo$ from kotmd_temp$; exception when others
181 then null; end;';
182 execute immediate 'select count(*) from kotmd$' into inscnt;
183 if(inscnt = origcnt) then
184 commit;
185 dbms_output.put_line('kotmd$ Upgraded ...');
186 execute immediate 'drop table kotmd_temp$';
187 else
188 dbms_output.put_line('Upgrade failed for kotmd$ ...');
189 rollback;
190 return;
191 end if;
192
193 -- reset the event
194 execute immediate 'alter session set events ''22372 trace name
195 context off''';
196
197 end; /* end of procedure upgrade_dict_image */
198
199 -- Procedure delete_orphan_typeidcols
200 -- A little background on the problem - There is a bug in drop attribute
201 -- code which leaves the typeid column in col$. I fixed that in 12g. But
202 -- these orphaned rows causes issues for datapump, so we need a way to clean
203 -- them up, so here it is.
204 -- This will clean up all orphaned entries in col$.
205 -- IMPORTANT NOTE: This will not work if the columns are only marked unused.
206 -- So you will need to drop unsued columns from any table that you want to
207 -- clean up. I am not doing that in this function as that can take quite a
208 -- while if there is a lot of data to be deleted.
209 procedure delete_orphan_typeidcols
210 is
211
212 objno number;
213 intcolno number;
214
215 -- Query to get the orphaned rows
216 cursor c1 is
217 select distinct c.obj#, c.intcol# from col$ c, coltype$ t where
218 bitand(c.property, 33554432)=33554432 and c.obj#=t.obj# and
219 c.intcol# not in (select typidcol# from coltype$ t1 where c.obj#=t1.obj# and
220 typidcol# is not null);
221
222 begin
223
224 OPEN c1;
225 LOOP
226 -- get the orphoned typeid row
227 FETCH C1 into objno, intcolno;
228 EXIT WHEN c1%notfound;
229 -- delete from col$
230 DELETE from col$ where obj#=objno and intcol#=intcolno;
231 END LOOP;
232 CLOSE c1;
233
234 -- all done
235 COMMIT;
236
237 end; /* end of delete_orphan_typeidcols */
238
239 -- Procedure fix_kottd_images
240 -- For a while there was a bug introduced that caused predefined types to be
241 -- created in 8.0 image format into an 8.1 dictionary table. The timeframe of
242 -- the bug was such that it impacted databases created in 9i, but was upgraded
243 -- to 10g, before the fix went in 2007. In this case we ended up with 3 8.0
244 -- images corresponding to the types binary float, binary double and urowid.
245 -- This images will cause problem if we use these types as ADT attributes or
246 -- collection elements. If that happens, run the following procedure to upgrade
247 -- the images to 8.1 image format
248
249 procedure fix_kottd_images
250 is
251
252 oldimg number;
253
254 begin
255 oldimg := 0;
256 -- first make sure that kottd$ is in 8.1 image format
257 execute immediate 'select bitand(flags, 128) from coltype$ where
258 obj#=(select obj# from obj$ where name=''KOTTD$'' and owner#=0)' into oldimg;
259 if (oldimg = 128) then
260 dbms_output.put_line ('kottd$ is in 8.0 image format');
261 return;
262 end if;
263
264 -- event needed to create packed image typed tables
265 execute immediate 'alter session set events ''22372 trace name
266 context forever''';
267
268 -- create a temp kottd$ table in 8.1 image format
269 execute immediate 'create table kottd_temp$ of kottd';
270 -- now temporarily put the kottd$ in 8.0 image format, so that the image
271 -- gets converted
272 execute immediate 'update coltype$ set flags=flags+128
273 where obj#=(select obj# from obj$ where name=''KOTTD$'' and owner#=0)';
274 commit;
275 execute immediate 'alter system flush shared_pool';
276 -- now copy the 3 images into the temp table
277 execute immediate 'insert into kottd_temp$(sys_nc_oid$, sys_nc_rowinfo$)
278 select sys_nc_oid$, sys_nc_rowinfo$ from kottd$ where sys_nc_oid$=''00000000000000000000000000000044'' or sys_nc_oid$=''00000000000000000000000000000045'' or sys_nc_oid$=''00000000000000000000000000000046''';
279 -- now delete these 3 from kottd$
280 execute immediate 'delete from kottd$ where sys_nc_oid$=''00000000000000000000000000000044'' or sys_nc_oid$=''00000000000000000000000000000045'' or sys_nc_oid$=''00000000000000000000000000000046''';
281 -- move kottd$ back to 8.1 image format
282 execute immediate 'update coltype$ set flags=flags-128
283 where obj#=(select obj# from obj$ where name=''KOTTD$'' and owner#=0)';
284 commit;
285 execute immediate 'alter system flush shared_pool';
286 -- copy the updated images back
287 execute immediate 'insert into kottd$(sys_nc_oid$, sys_nc_rowinfo$)
288 select sys_nc_oid$, sys_nc_rowinfo$ from kottd_temp$ where sys_nc_oid$=''00000000000000000000000000000044'' or sys_nc_oid$=''00000000000000000000000000000045'' or sys_nc_oid$=''00000000000000000000000000000046''';
289 commit;
290 -- all done, now drop the temp table
291 execute immediate 'drop table kottd_temp$';
292
293 -- reset the event
294 execute immediate 'alter session set events ''22372 trace name
295 context off''';
296
297 end; /* end of procedure fix_kottd_images */
298
299 end; /* end of package dbms_objects_utils */
[Home] [Help]