[Home] [Help]
PACKAGE BODY: APPS.BIS_TRUNCATE_EMPTY_MV_LOG_PKG
Source
1 package body BIS_TRUNCATE_EMPTY_MV_LOG_PKG AS
2 /*$Header: BISTEMLB.pls 120.1 2006/02/09 05:43 aguwalan noship $*/
3
4 /****************************************************************************
5 -- Is_Refresh_Pgm_running
6 -- DESCRIPTION:
7 -- Checks if any Refrest program is running in the system.
8 -- Returns 1 if there is any refresh request set running
9 -- 2 if there is any MV refresh program running
10 -- 3 if there is any MV being refreshed
11 -- 0 if none of the above condition is true
12 --****************************************************************************/
13 FUNCTION Check_Refresh_Prog_running
14 return number
15 IS
16 CURSOR rs_running IS
17 select distinct bis.request_set_name Name, cr.phase_code Phase, cr.request_id request, cr.requested_start_date s_date
18 from bis_request_set_objects bis, fnd_request_sets fnd , fnd_concurrent_requests cr
19 where bis.request_set_name = fnd.request_set_name
20 and bis.set_app_id = fnd.application_id
21 and cr.phase_code = 'R'
22 and to_char(fnd.application_id) = cr.argument1
23 and to_char(fnd.request_set_id) = cr.argument2
24 and cr.argument4 is null;
25 rs_running_rec rs_running%rowtype;
26
27 CURSOR mv_refresh_prog IS
28 SELECT obj.object_name Name, req.request_id request
29 FROM fnd_concurrent_programs prog, fnd_concurrent_requests req, bis_obj_properties obj
30 WHERE (prog.concurrent_program_name = 'BIS_MV_REFRESH_STANDALONE' OR prog.concurrent_program_name = 'BIS_MV_REFRESH')
31 AND prog.application_id = 191
32 AND prog.concurrent_program_id = req.concurrent_program_id
33 AND req.phase_code = 'R'
34 AND obj.object_type = 'MV'
35 AND req.argument2 = obj.object_name;
36 mv_refresh_prog_rec mv_refresh_prog%rowtype;
37
38 CURSOR mv_refresh IS
39 SELECT currmvname name
40 FROM v$mvrefresh mv, bis_obj_properties obj
41 WHERE obj.object_name = currmvname;
42 mv_refresh_rec mv_refresh%rowtype;
43
44 prog_running number;
45 l_program_status boolean :=true;
46
47 BEGIN
48 prog_running := 0;
49 OPEN rs_running;
50 BIS_COLLECTION_UTILITIES.put_line('Checking if DBI Refresh Request Sets are running ...');
51 BIS_COLLECTION_UTILITIES.put_line(' ');
52 LOOP
53 FETCH rs_running INTO rs_running_rec;
54 EXIT WHEN rs_running%NOTFOUND;
55 prog_running := 1;
56 BIS_COLLECTION_UTILITIES.put_line(' - ' || rs_running_rec.Name||'(Req. Id:'||rs_running_rec.request||') Phase:'|| rs_running_rec.Phase || ' Started:' || to_char(rs_running_rec.s_Date,'DD-MON-YY HH24:MI:SS'));
57 END LOOP;
58
59 IF (prog_running <> 0) THEN
60 RETURN prog_running;
61 END IF;
62
63 BIS_COLLECTION_UTILITIES.put_line('Checking if MV Refresh Program are running ...');
64 BIS_COLLECTION_UTILITIES.put_line(' ');
65 OPEN mv_refresh_prog;
66 LOOP
67 FETCH mv_refresh_prog INTO mv_refresh_prog_rec;
68 EXIT WHEN mv_refresh_prog%NOTFOUND;
69 prog_running := 2;
70 BIS_COLLECTION_UTILITIES.put_line(' - ' || mv_refresh_prog_rec.Name||'(Req. Id:'||mv_refresh_prog_rec.request||') - Getting Refreshed');
71 END LOOP;
72 CLOSE mv_refresh_prog;
73
74 IF (prog_running <> 0) THEN
75 RETURN prog_running;
76 END IF;
77
78 BIS_COLLECTION_UTILITIES.put_line('Checking if any MV being refreshed ...');
79 BIS_COLLECTION_UTILITIES.put_line(' ');
80 OPEN mv_refresh;
81 LOOP
82 FETCH mv_refresh INTO mv_refresh_rec;
83 EXIT WHEN mv_refresh%NOTFOUND;
84 prog_running := 3;
85 BIS_COLLECTION_UTILITIES.put_line(' - ' || mv_refresh_rec.Name ||' - Getting Refreshed');
86 END LOOP;
87 CLOSE mv_refresh;
88
89 RETURN prog_running;
90 EXCEPTION
91 WHEN OTHERS THEN
92 BIS_COLLECTION_UTILITIES.put_line('Error in Check_Refresh_Prog_running '|| sqlerrm);
93 l_program_status := fnd_concurrent.set_completion_status('Error' ,NULL);
94 END;
95
96
97 /****************************************************************************
98 -- getMVLogSize
99 -- DESCRIPTION:
100 -- Gets the space occupied by the MV Log in Bytes
101 --****************************************************************************/
102 FUNCTION get_Table_size(p_obj_owner IN VARCHAR2
103 ,p_log_table IN VARCHAR2)
104 RETURN NUMBER
105 IS
106 op1 NUMBER;
107 total_bytes NUMBER;
108 op3 NUMBER;
109 op4 NUMBER;
110 op5 NUMBER;
111 op6 NUMBER;
112 op7 NUMBER;
113 BEGIN
114 total_bytes := 0;
115 BEGIN
116 Dbms_Space.Unused_Space(p_obj_owner, p_log_table, 'TABLE',op1,total_bytes,op3,op4,op5,op6,op7);
117 EXCEPTION
118 WHEN OTHERS THEN
119 BIS_COLLECTION_UTILITIES.put_line('Error in get_Table_size('|| p_obj_owner || ',' || p_log_table || sqlerrm);
120 END;
121 return total_bytes;
122 END;
123
124 /****************************************************************************
125 -- Truncate_Empty_MV_Log
126 -- DESCRIPTION:
127 -- Truncates Empty MV Logs to improve performance issues of Refresh
128 -- programs
129 --****************************************************************************/
130 PROCEDURE Truncate_Empty_MV_Log( errbuf OUT NOCOPY VARCHAR2
131 ,retcode OUT NOCOPY VARCHAR
132 ,threshold IN NUMBER)
133 IS
134 TYPE curType IS REF CURSOR ;
135 cursor impl_tables is
136 select object_name, BIS_CREATE_REQUESTSET.get_object_owner(object_name, object_type) obj_owner
137 from bis_obj_properties
138 where (object_type = 'TABLE' OR object_type = 'MV');
139 impl_tables_rec impl_tables%rowtype;
140
141 cursor log_table(obj_name varchar2, obj_owner varchar2) is
142 select log_table, log_owner
143 from All_SNAPSHOT_LOGS LOG
144 where log.master = obj_name
145 and log.log_owner = obj_owner;
146 log_table_rec log_table%rowtype;
147
148 l_stmt varchar2(1000);
149 l_flag number;
150 c1 curType;
151 e_refresh_prog_running EXCEPTION;
152 l_program_status boolean := true;
153 l_refresh_progs number;
154 l_threshold_bytes number;
155 default_threshold number := 80;
156 BEGIN
157 BIS_COLLECTION_UTILITIES.put_line('Starting Truncate Empty MV Logs');
158 BIS_COLLECTION_UTILITIES.put_line(' ');
159 l_refresh_progs := Check_Refresh_Prog_running;
160 if(l_refresh_progs <> 0) then
161 raise e_refresh_prog_running;
162 end if;
163
164 if (threshold is null) then
165 l_threshold_bytes := default_threshold * 1024 * 1024;
166 BIS_COLLECTION_UTILITIES.put_line('Threshold for Empty MV Logs (by default) = ' || to_char(default_threshold) || 'MB');
167 else
168 l_threshold_bytes := threshold * 1024 * 1024;
169 BIS_COLLECTION_UTILITIES.put_line('Threshold for Empty MV Logs = ' || to_char(threshold) || 'MB');
170 end if;
171
172 open impl_tables;
173 BIS_COLLECTION_UTILITIES.put_line(RPAD('Object Name',35,' ') || RPAD('Owner',10,' ') || RPAD('Log table name',35,' ') ||' Action');
174 loop
175 fetch impl_tables into impl_tables_rec;
176 exit when impl_tables%notfound;
177 if(impl_tables_rec.obj_owner = 'NOTFOUND' ) then
178 impl_tables_rec.obj_owner := 'APPS';
179 end if;
180 open log_table(impl_tables_rec.object_name,impl_tables_rec.obj_owner);
181 loop
182 fetch log_table into log_table_rec;
183 exit when log_table%notfound;
184 begin
185 l_stmt := 'select /*+ FIRST_ROWS */ 1 from '|| log_table_rec.log_owner ||'.'|| log_table_rec.log_table ||' where rownum=1';
186 open c1 for l_stmt;
187 fetch c1 into l_flag;
188 if (c1%NotFound) then
189 if (get_Table_size(impl_tables_rec.obj_owner,log_table_rec.log_table) >= l_threshold_bytes) then
190 l_stmt := 'TRUNCATE TABLE '|| impl_tables_rec.obj_owner || '.' ||log_table_rec.log_table;
191 execute immediate l_stmt;
192 BIS_COLLECTION_UTILITIES.put_line(RPAD(impl_tables_rec.object_name,35,' ') || RPAD(impl_tables_rec.obj_owner,10,' ') || RPAD(log_table_rec.log_table,35,' ') ||' Truncated');
193 else
194 BIS_COLLECTION_UTILITIES.put_line(RPAD(impl_tables_rec.object_name,35,' ') || RPAD(impl_tables_rec.obj_owner,10,' ') || RPAD(log_table_rec.log_table,35,' ') ||' Empty under threshold');
195 end if;
196 else
197 BIS_COLLECTION_UTILITIES.put_line(RPAD(impl_tables_rec.object_name,35,' ') || RPAD(impl_tables_rec.obj_owner,10,' ') || RPAD(log_table_rec.log_table,35,' ') ||' Not Empty');
198 end if;
199 close c1;
200 exception
201 when others then
202 BIS_COLLECTION_UTILITIES.put_line(RPAD(impl_tables_rec.object_name,35,' ') || RPAD(impl_tables_rec.obj_owner,10,' ') || 'Exception while accessing ' ||log_table_rec.log_table || ': ' || sqlerrm);
203 if (c1%isopen) then
204 close c1;
205 end if;
206 end;
207 end loop;
208 close log_table;
209 end loop;
210 close impl_tables;
211 BIS_COLLECTION_UTILITIES.put_line(' ');
212 BIS_COLLECTION_UTILITIES.put_line('Completing Truncate Empty MV Logs');
213
214 EXCEPTION
215 WHEN e_refresh_prog_running THEN
216 BIS_COLLECTION_UTILITIES.put_line(' ');
217 IF (l_refresh_progs = 1) THEN
218 BIS_COLLECTION_UTILITIES.put_line('Error in Truncate Empty MV Logs - DBI Refresh program is running' );
219 ELSE
220 BIS_COLLECTION_UTILITIES.put_line('Error in Truncate Empty MV Logs - MV is being refreshed' );
221 END IF;
222 BIS_COLLECTION_UTILITIES.put_line('Please run Truncate Empty MV Logs when there are no Refresh request-set/programs running');
223 l_program_status := fnd_concurrent.set_completion_status('Error' ,NULL);
224 errbuf := 'DBI Refresh Program Running';
225 WHEN OTHERS THEN
226 BIS_COLLECTION_UTILITIES.put_line('Error in Truncate Empty MV Logs '|| sqlerrm);
227 l_program_status := fnd_concurrent.set_completion_status('Error' ,NULL);
228 errbuf := sqlerrm;
229 retcode := sqlcode;
230 END;
231
232 END BIS_TRUNCATE_EMPTY_MV_LOG_PKG;