1 PACKAGE BODY MSD_AW_RENAME AS
2 /* $Header: msdawrnb.pls 120.0.12020000.5 2012/12/21 12:09:54 rissingh noship $ */
3
4 /* Public Procedures */
5
6 /*
7 Procedure to Rename the Patch edition AWs to RUN edition
8 and to take a backup of the Old AW.
9 */
10
11 procedure rename_aw(p_aw_name varchar2) is
12
13 patch_edition_name varchar2(50);
14 backup_name varchar2(50);
15 PATCH_EDITION_EXISTS number :=0;
16 aw_name varchar2(30);
17 err_msg varchar2(500);
18 current_user_name varchar2(20);
19
20 session_id integer;
21 serial_no integer;
22 session_owner varchar2(20);
23 alter_sql varchar2(200);
24 TYPE type_cursor IS REF CURSOR;
25
26 Attached_aws type_cursor;
27 v_stmt_str VARCHAR2(500);
28 v_stmt_str1 VARCHAR2(500);
29 -- Cursor to get the current attached sessions
30
31
32 begin
33
34 ad_zd_log.message('ODP','EVENT','Entered into RENAME_AW ');
35
36 v_stmt_str := 'select sid as session_id, serial# as serial_no, owner as session_owner from ' ||
37 'all_aws daws,v$aw_olap vawo,v$session '||
38 ' where daws.aw_number=vawo.aw_number and sid=vawo.session_id '||
39 ' and (daws.aw_name like ''%XWDEVKIT'' or daws.aw_name like ''%ODPCODE'')';
40
41
42
43 aw_name:=upper(p_aw_name);
44 patch_edition_name:=concat(aw_name,'_PATCH_EDITION');
45 backup_name:=concat(aw_name,'_BACKUP');
46
47 v_stmt_str1:= 'select 1 from all_aws where AW_NAME=''' || patch_edition_name||'''';
48 execute immediate v_stmt_str1 into PATCH_EDITION_EXISTS;
49 if PATCH_EDITION_EXISTS = 1 then
50
51 ad_zd_log.message('ODP','EVENT','Entered into RENAME_AW - PATCH_EDITION_EXISTS');
52 select user into current_user_name from dual;
53
54 ad_zd_log.message('ODP','INFORMATION','Executing RENAME_AW as "'||current_user_name||'" user.');
55
56 -- Detach PATCH EDITION AW
57 begin
58 dbms_aw.aw_detach(patch_edition_name);
59 exception
60 when others then
61 --null;
62 if SQLCODE <> -34344 then
63 err_msg := 'Exception - '||aw_name||' - Detach Patch Edition - '||SQLCODE||' -ERROR- '||SQLERRM;
64 ad_zd_log.message('ODP','EXCEPTION',err_msg);
65 end if;
66 end;
67
68 -- Detach RUN EDITION AW
69 begin
70 dbms_aw.aw_detach(aw_name);
71 exception
72 when others then
73 --null;
74 if SQLCODE <> -34344 then
75 err_msg := 'Exception -'||aw_name||' - Detach Run Edition - '||SQLCODE||' -ERROR- '||SQLERRM;
76 ad_zd_log.message('ODP','EXCEPTION',err_msg);
77 end if;
78 end;
79
80 -- Delete the old backup of the AW
81 begin
82 dbms_aw.aw_delete(backup_name);
83 exception
84 when others then
85 --null;
86 if SQLCODE = -33262
87 then
88 err_msg := 'Delete BackUp- Backup for the AW '||aw_name||' does not exist ';
89 ad_zd_log.message('ODP','INFORMATION',err_msg);
90 else
91 err_msg := 'Delete BackUp- '||aw_name||' - '||SQLCODE||' -ERROR- '||SQLERRM;
92 ad_zd_log.message('ODP','EXCEPTION',err_msg);
93 end if;
94
95 end;
96
97 -- Kill the sessions attached to the AWs
98 begin
99 OPEN Attached_aws FOR v_stmt_str;
100 loop
101
102 ad_zd_log.message('ODP','EVENT','Entered into KILL SESSIONS LOOP ');
103 FETCH Attached_aws into session_id, serial_no, session_owner;
104 exit when Attached_aws%NOTFOUND;
105 alter_sql:='ALTER SYSTEM KILL SESSION '''||session_id||','||serial_no||'''';
106 ad_zd_log.message('ODP','EVENT','SESSION OWNER - '||session_owner);
107 Execute immediate alter_sql;
108 ad_zd_log.message('ODP','EVENT','Killed session '||session_id);
109
110 end loop;
111
112 close Attached_aws;
113 dbms_lock.sleep(5); -- Sleep for 5 seconds to allow locks to get released
114 exception
115 when others then
116 --null;
117 err_msg := 'Exception - '||aw_name||' - KILL SESSIONS - '||session_id ||'- '||SQLCODE||' -ERROR- '||SQLERRM;
118 ad_zd_log.message('ODP','EXCEPTION',err_msg);
119 end;
120
121 -- Rename the AWs
122 begin
123 dbms_aw.aw_rename(aw_name,backup_name); -- Keep a backup of the RUN EDITION
124 ad_zd_log.message('ODP','EVENT','Backup Created - '||backup_name);
125 dbms_aw.aw_rename(patch_edition_name,aw_name); -- Rename the patch edition to RUN edition
126 ad_zd_log.message('ODP','EVENT','Executed rename for AW - '||aw_name);
127 exception
128 when others then
129 --null;
130 err_msg := 'Exception - '||aw_name||' -Rename Patch Edition - '||SQLCODE||' -ERROR- '||SQLERRM;
131 ad_zd_log.message('ODP','EXCEPTION', err_msg);
132 end;
133
134 end if;
135
136 ad_zd_log.message('ODP','EVENT','Exit from RENAME_AW ');
137
138 end rename_aw;
139
140 END MSD_AW_RENAME;
[Home] [Help]