[Home] [Help]
15: g_locked_objects.delete; happens when commit_aw happens
16: */
17: procedure get_workspace_lock(p_objects dbms_sql.varchar2_table,p_options varchar2) is
18: Begin
19: --we have bsc_aw_utility.add_option so that if "exclusive lock" is passed as an option we can hold it in bsc_aw_utility.g_options
20: bsc_aw_utility.add_option(p_options,null,',');
21: g_locked_objects.delete;
22: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' then
23: g_locked_objects:=p_objects;
16: */
17: procedure get_workspace_lock(p_objects dbms_sql.varchar2_table,p_options varchar2) is
18: Begin
19: --we have bsc_aw_utility.add_option so that if "exclusive lock" is passed as an option we can hold it in bsc_aw_utility.g_options
20: bsc_aw_utility.add_option(p_options,null,',');
21: g_locked_objects.delete;
22: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' then
23: g_locked_objects:=p_objects;
24: end if;
18: Begin
19: --we have bsc_aw_utility.add_option so that if "exclusive lock" is passed as an option we can hold it in bsc_aw_utility.g_options
20: bsc_aw_utility.add_option(p_options,null,',');
21: g_locked_objects.delete;
22: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' then
23: g_locked_objects:=p_objects;
24: end if;
25: if g_locked_objects.count=0 then --no objects. have to go for full lock
26: if g_debug then
40: another session locking
41: */
42: procedure get_workspace_lock(p_mode varchar2,p_options varchar2) is
43: Begin
44: bsc_aw_utility.add_option(p_options,null,',');
45: g_locked_objects.delete;
46: get_lock(p_mode,p_options);
47: if upper(p_mode)='RW' then
48: create_default_elements; --only in rw mode
58: l_nowait varchar2(10);
59: l_exit boolean;
60: l_current_sessions current_sessions_tb;
61: Begin
62: bsc_aw_utility.add_option(p_options,null,',');
63: l_name:=get_aw_workspace_name;
64: if nvl(bsc_aw_utility.get_parameter_value(p_options,'create workspace',','),'N')='Y' then
65: if bsc_aw_md_api.check_workspace(l_name)='N' then
66: create_workspace(p_options);
60: l_current_sessions current_sessions_tb;
61: Begin
62: bsc_aw_utility.add_option(p_options,null,',');
63: l_name:=get_aw_workspace_name;
64: if nvl(bsc_aw_utility.get_parameter_value(p_options,'create workspace',','),'N')='Y' then
65: if bsc_aw_md_api.check_workspace(l_name)='N' then
66: create_workspace(p_options);
67: end if;
68: end if;
65: if bsc_aw_md_api.check_workspace(l_name)='N' then
66: create_workspace(p_options);
67: end if;
68: end if;
69: l_nowait:=nvl(bsc_aw_utility.get_parameter_value(p_options,'nowait',','),'N');
70: --
71: l_exit:=false;
72: loop
73: begin
76: exception when others then
77: if l_nowait='Y' then
78: raise;
79: elsif sqlcode=-33290 or sqlcode=-37011 then
80: bsc_aw_utility.sleep(60,15);
81: else
82: raise;
83: end if;
84: end;
132: l_wait_object dbms_sql.varchar2_table;
133: Begin
134: l_resync:=null;
135: l_wait:=null;
136: bsc_aw_utility.add_option(p_options,null,',');
137: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' and p_locked_objects.count>0 then --10g
138: /*initially there was if g_attached=false then. we remove this because attach_workspace already takes care of this */
139: attach_workspace(get_aw_workspace_name,'multi');/*since this is for multiple objects, its multi lock */
140: l_count:=0;
133: Begin
134: l_resync:=null;
135: l_wait:=null;
136: bsc_aw_utility.add_option(p_options,null,',');
137: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' and p_locked_objects.count>0 then --10g
138: /*initially there was if g_attached=false then. we remove this because attach_workspace already takes care of this */
139: attach_workspace(get_aw_workspace_name,'multi');/*since this is for multiple objects, its multi lock */
140: l_count:=0;
141: if nvl(bsc_aw_utility.get_parameter_value(p_options,'resync',','),'N')='Y' then
137: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' and p_locked_objects.count>0 then --10g
138: /*initially there was if g_attached=false then. we remove this because attach_workspace already takes care of this */
139: attach_workspace(get_aw_workspace_name,'multi');/*since this is for multiple objects, its multi lock */
140: l_count:=0;
141: if nvl(bsc_aw_utility.get_parameter_value(p_options,'resync',','),'N')='Y' then
142: l_resync:='resync';
143: end if;
144: l_wait:=bsc_aw_utility.get_parameter_value(p_options,'wait type',',');--wait or active wait or null
145: for i in 1..p_locked_objects.count loop --init
140: l_count:=0;
141: if nvl(bsc_aw_utility.get_parameter_value(p_options,'resync',','),'N')='Y' then
142: l_resync:='resync';
143: end if;
144: l_wait:=bsc_aw_utility.get_parameter_value(p_options,'wait type',',');--wait or active wait or null
145: for i in 1..p_locked_objects.count loop --init
146: l_resync_object(i):=l_resync;
147: l_wait_object(i):=l_wait;
148: end loop;
149: for i in 1..p_locked_objects.count loop
150: get_lock_object(p_locked_objects(i),l_resync_object(i),l_wait_object(i));
151: l_count:=i;
152: --we add the object to the list of locked objects if not already there
153: if bsc_aw_utility.in_array(g_locked_objects,p_locked_objects(i))=false then
154: g_locked_objects(g_locked_objects.count+1):=p_locked_objects(i);
155: end if;
156: end loop;
157: end if;
173: l_resync varchar2(40);
174: l_wait varchar2(40);
175: l_start_time number;
176: Begin
177: l_start_time:=bsc_aw_utility.get_dbms_time;
178: l_resync:=p_resync;
179: if p_wait='wait' then --p_wait can be wait, active wait or null
180: l_wait:=p_wait;
181: end if;
189: log('Object '||p_object||' already acquired.');
190: end if;
191: exit;
192: elsif sqlcode=-37011 or sqlcode=-37013 or sqlcode=-37040 or sqlcode=-37042 then
193: if (bsc_aw_utility.get_dbms_time-l_start_time)/100 > bsc_aw_utility.g_max_wait_time then
194: if g_debug then
195: log('Wait time has exceeded '||bsc_aw_utility.g_max_wait_time||'. Aborting...');
196: end if;
197: raise bsc_aw_utility.g_exception;
191: exit;
192: elsif sqlcode=-37011 or sqlcode=-37013 or sqlcode=-37040 or sqlcode=-37042 then
193: if (bsc_aw_utility.get_dbms_time-l_start_time)/100 > bsc_aw_utility.g_max_wait_time then
194: if g_debug then
195: log('Wait time has exceeded '||bsc_aw_utility.g_max_wait_time||'. Aborting...');
196: end if;
197: raise bsc_aw_utility.g_exception;
198: elsif p_wait='active wait' then
199: bsc_aw_utility.sleep(5,7);
193: if (bsc_aw_utility.get_dbms_time-l_start_time)/100 > bsc_aw_utility.g_max_wait_time then
194: if g_debug then
195: log('Wait time has exceeded '||bsc_aw_utility.g_max_wait_time||'. Aborting...');
196: end if;
197: raise bsc_aw_utility.g_exception;
198: elsif p_wait='active wait' then
199: bsc_aw_utility.sleep(5,7);
200: else
201: raise;
195: log('Wait time has exceeded '||bsc_aw_utility.g_max_wait_time||'. Aborting...');
196: end if;
197: raise bsc_aw_utility.g_exception;
198: elsif p_wait='active wait' then
199: bsc_aw_utility.sleep(5,7);
200: else
201: raise;
202: end if;
203: elsif sqlcode=-37044 then --needs resync
229: End;
230:
231: procedure release_lock(p_object varchar2) is
232: Begin
233: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' then
234: bsc_aw_dbms_aw.execute('release '||p_object);
235: end if;
236: Exception when others then
237: if sqlcode=-37021 then
261:
262: procedure detach_aw_workspace(p_workspace varchar2) is
263: Begin
264: if g_attached then
265: bsc_aw_utility.add_sqlerror(-34344,'ignore',null);--ignore if the ws is not already attached
266: bsc_aw_dbms_aw.execute('aw detach '||p_workspace);
267: bsc_aw_utility.remove_sqlerror(-34344,'ignore');
268: end if;
269: g_attached:=false;
263: Begin
264: if g_attached then
265: bsc_aw_utility.add_sqlerror(-34344,'ignore',null);--ignore if the ws is not already attached
266: bsc_aw_dbms_aw.execute('aw detach '||p_workspace);
267: bsc_aw_utility.remove_sqlerror(-34344,'ignore');
268: end if;
269: g_attached:=false;
270: g_attached_mode:=null;
271: Exception when others then
268: end if;
269: g_attached:=false;
270: g_attached_mode:=null;
271: Exception when others then
272: bsc_aw_utility.remove_sqlerror(-34344,'ignore');
273: log_n('Exception in detach_aw_workspace '||sqlerrm);
274: raise;
275: End;
276:
299: Begin
300: if g_locked_objects.count>0 then --10g
301: commit_aw_multi;
302: bsc_aw_dbms_aw.execute('commit');
303: if not(nvl(bsc_aw_utility.get_parameter_value(p_options,'no release lock',','),'N')='Y') then
304: for i in 1..g_locked_objects.count loop
305: release_lock(g_locked_objects(i));
306: end loop;
307: g_locked_objects.delete;
334: */
335: procedure commit_aw(p_locked_objects dbms_sql.varchar2_table,p_options varchar2) is
336: Begin
337: commit_aw_multi(p_locked_objects);
338: if not(nvl(bsc_aw_utility.get_parameter_value(p_options,'no release lock',','),'N')='Y') then
339: for i in 1..p_locked_objects.count loop
340: release_lock(p_locked_objects(i));
341: end loop;
342: for i in 1..p_locked_objects.count loop
339: for i in 1..p_locked_objects.count loop
340: release_lock(p_locked_objects(i));
341: end loop;
342: for i in 1..p_locked_objects.count loop
343: bsc_aw_utility.remove_array_element(g_locked_objects,p_locked_objects(i));
344: end loop;
345: end if;
346: bsc_aw_dbms_aw.execute('commit');
347: Exception when others then
371: l_update_stmt varchar2(5000);
372: l_multi_update boolean;
373: Begin
374: l_multi_update:=false;
375: if bsc_aw_utility.get_db_version>=10 and nvl(bsc_aw_utility.get_parameter_value('exclusive lock'),'N')='N' then
376: l_update_stmt:='update multi ';
377: l_multi_update:=true;
378: for i in 1..p_locked_objects.count loop
379: if length(l_update_stmt||' '||p_locked_objects(i))<4000 then
410: procedure create_workspace(p_name varchar2,p_options varchar2) is
411: l_tablespace varchar2(100);
412: l_segmentsize varchar2(40);
413: Begin
414: bsc_aw_utility.g_options.delete;
415: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
416: l_tablespace:=bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'TABLESPACE');
417: if l_tablespace is null then
418: bsc_aw_dbms_aw.execute('aw create '||p_name);
411: l_tablespace varchar2(100);
412: l_segmentsize varchar2(40);
413: Begin
414: bsc_aw_utility.g_options.delete;
415: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
416: l_tablespace:=bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'TABLESPACE');
417: if l_tablespace is null then
418: bsc_aw_dbms_aw.execute('aw create '||p_name);
419: else
412: l_segmentsize varchar2(40);
413: Begin
414: bsc_aw_utility.g_options.delete;
415: bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
416: l_tablespace:=bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'TABLESPACE');
417: if l_tablespace is null then
418: bsc_aw_dbms_aw.execute('aw create '||p_name);
419: else
420: bsc_aw_dbms_aw.execute('aw create '||p_name||' tablespace '||l_tablespace);
418: bsc_aw_dbms_aw.execute('aw create '||p_name);
419: else
420: bsc_aw_dbms_aw.execute('aw create '||p_name||' tablespace '||l_tablespace);
421: end if;
422: l_segmentsize:=bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'SEGMENTSIZE');
423: if l_segmentsize is not null then
424: bsc_aw_dbms_aw.execute('aw SEGMENTSIZE '||l_segmentsize||' '||p_name);
425: bsc_aw_dbms_aw.execute('aw ALLOCATE '||l_segmentsize||' '||p_name);
426: end if;
425: bsc_aw_dbms_aw.execute('aw ALLOCATE '||l_segmentsize||' '||p_name);
426: end if;
427: create_default_elements;
428: --make the LOB nologging. this is the suggestion of the AW team as per Vladimir
429: bsc_aw_utility.execute_stmt_ne('ALTER TABLE aw$'||p_name||' MODIFY LOB(awlob) (PCTVERSION 0 CACHE READS NOLOGGING)');
430: bsc_aw_md_api.create_workspace(p_name);
431: bsc_aw_md_api.set_upgrade_version(bsc_aw_utility.g_upgrade_version);
432: Exception when others then
433: if sqlcode=-33270 then
427: create_default_elements;
428: --make the LOB nologging. this is the suggestion of the AW team as per Vladimir
429: bsc_aw_utility.execute_stmt_ne('ALTER TABLE aw$'||p_name||' MODIFY LOB(awlob) (PCTVERSION 0 CACHE READS NOLOGGING)');
430: bsc_aw_md_api.create_workspace(p_name);
431: bsc_aw_md_api.set_upgrade_version(bsc_aw_utility.g_upgrade_version);
432: Exception when others then
433: if sqlcode=-33270 then
434: log_n('Workspace '||p_name||' already exists');
435: else
475: bsc_aw_dbms_aw.execute('DIVIDEBYZERO=TRUE');
476: bsc_aw_dbms_aw.execute('OKNULLSTATUS=TRUE');
477: bsc_aw_dbms_aw.execute('LIMIT.SORTREL=FALSE');
478: bsc_aw_dbms_aw.execute('COMMAS=FALSE');
479: bsc_aw_dbms_aw.execute('AWWAITTIME='||bsc_aw_utility.g_max_wait_time);
480: Exception when others then
481: log_n('Exception in exec_workspace_settings '||sqlerrm);
482: raise;
483: End;
504:
505: procedure set_hash_partition_dim is
506: l_cpu_count number;
507: Begin
508: l_cpu_count:=bsc_aw_utility.get_cpu_count;
509: bsc_aw_dbms_aw.execute_ne('dfn HASH_PARTITION_DIM dimension number(4)');
510: for i in 0..l_cpu_count-1 loop
511: bsc_aw_dbms_aw.execute('mnt HASH_PARTITION_DIM merge '||i);
512: end loop;
534: l_lock_objects dbms_sql.varchar2_table;
535: Begin
536: if g_lock_set.exists(p_set_name) then
537: for i in 1..g_lock_set(p_set_name).locked_objects.count loop
538: if bsc_aw_utility.in_array(g_locked_objects,g_lock_set(p_set_name).locked_objects(i))=false then
539: l_lock_objects(l_lock_objects.count+1):=g_lock_set(p_set_name).locked_objects(i);
540: end if;
541: end loop;
542: if l_lock_objects.count>0 then
539: l_lock_objects(l_lock_objects.count+1):=g_lock_set(p_set_name).locked_objects(i);
540: end if;
541: end loop;
542: if l_lock_objects.count>0 then
543: bsc_aw_utility.merge_array(g_locked_objects,l_lock_objects);
544: get_lock(l_lock_objects,p_options);
545: end if;
546: end if;
547: Exception when others then
560: End;
561:
562: procedure update_aw is
563: Begin
564: bsc_aw_utility.add_sqlerror(-34684,'ignore',null);--ignore if the ws is not alreay attached and there are no objects to update
565: bsc_aw_dbms_aw.execute('update');
566: bsc_aw_utility.remove_sqlerror(-34684,'ignore');
567: Exception when others then
568: log_n('Exception in update_aw '||sqlerrm);
562: procedure update_aw is
563: Begin
564: bsc_aw_utility.add_sqlerror(-34684,'ignore',null);--ignore if the ws is not alreay attached and there are no objects to update
565: bsc_aw_dbms_aw.execute('update');
566: bsc_aw_utility.remove_sqlerror(-34684,'ignore');
567: Exception when others then
568: log_n('Exception in update_aw '||sqlerrm);
569: raise;
570: End;
571:
572: --------------------------------------------------------------------------------
573: procedure init_all is
574: Begin
575: g_debug:=bsc_aw_utility.g_debug;
576: Exception when others then
577: null;
578: End;
579:
578: End;
579:
580: procedure log(p_message varchar2) is
581: Begin
582: bsc_aw_utility.log(p_message);
583: Exception when others then
584: null;
585: End;
586: