In last article, we have seen how edition based redefinition is very helpful for online changes in application. But there a question arise what happens when we have a PL/SQL in both the editions are used by the application/user.
1. We will create a table ‘TESTEDITION1’ as below under ‘TEST1’ user.
Create table testedition1 (id number, name varchar2(30));
2. Now login to TEST1 user and create a pl/sql as shown below to insert records in to the table
create or replace procedure tst_edition_prc as
begin
for i in 1..1000000 loop
insert into testedition1 values(i+1,'EDITION_UPDATE');
end loop;
commit;
end;
/
We have 10 million records in the table
3. Next, we will login to TEST1 user with default edition and create a plsql procedure to update NAME column values.
create or replace procedure test_edition_1
is
edit_name varchar2(30);
num_of_rows number;
BEGIN
select sys_context('USERENV','CURRENT_EDITION_NAME') into edit_name from dual;
dbms_output.put_line('The edition name is '||edit_name);
dbms_output.put_line ('Welcome to my new edition' ||edit_name);
update testedition1 set name='EDITION_UPDATENEW' where name='EDITION_UPDATE';
commit;
select count(1) into num_of_rows from testedition1 where name='EDITION_UPDATE';
dbms_output.put_line ('Number of rows' ||num_of_rows);
END;
/
4. Cross check the edition of the procedure.
SQL> col object_name format a35
SQL> select object_name,status,object_type,edition_name from user_objects_ae where object_name-‘TEST_EDITION_1’;
OBJECT_NAME STATUS OBJECT_TYPE EDITION_NAME
----------------------------------- ------- ------------------- ------------------------------
TEST_EDITION_1 VALID PROCEDURE ORA$BASE
5. Next, open another SQLPLUS session as TEST1 user and change the edition to EDITION1.
SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USA
------------------------------ ------------------------------ ---
ORA$BASE YES
EDITION1 ORA$BASE YES
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
---------------------------------------------------------------------------------------------------------------------------
ORA$BASE
SQL> alter session set edition=edition1;
Session altered.
SQL> select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;
SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------------------------------------------------------EDITION1
6. Here, we will modify the TEST_EDITION_1 procedure to update few rows .
create or replace procedure test_edition_1
is
edit_name varchar2(30);
num_of_rows number;
name_new varchar2(30);
BEGIN
select sys_context('USERENV','CURRENT_EDITION_NAME') into edit_name from dual;
dbms_output.put_line('The edition name is '||edit_name);
dbms_output.put_line ('Welcome to my new edition' ||edit_name);
update testedition1 set name='EDITION_UPDATENEW' where name='EDITION_UPDATE' and id between 501 and 1000;
commit;
select name into name_new from testedition1 where name='EDITION_UDDATENEW' and id between 501 and 1000;
select count(*) into num_of_rows from testedition1 where name='EDITION_UPDATE';
dbms_output.put_line ('Number of rows' || num_of_rows);
dbms_output.put_line ('Name' || name_new);
END;
/
7. Cross check whether we have procedures with same name belonging to different editions.
SQL> select object_name,status,object_type,edition_name from user_objects_ae where object_name='TEST_EDITION_1';
OBJECT_NAME STATUS OBJECT_TYPE EDITION_NAME
----------------------------------- ------- ------------------- ------------------------------
TEST_EDITION_1 VALID PROCEDURE ORA$BASE
TEST_EDITION_1 VALID PROCEDURE EDITION1
8. Let us try to simulate the locking by executing the both the versions of procedure from different sql plus sessions. Please note these 2 procedures will do update to existing data in the table.
SQL> exec test_edition_1; -- from default edition
SQL> exec test_edition_1; - - from new edition (EDITION1)
9. When executing the above procedures, in another sql plus session as SYS user, check for any blocking locks.
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------------------------------------------------------- ---------------
125 16 17 Application 53
130 125 7 Configuration 1
At the same time, check for the session details.
SQL> @sid.sql
Enter Oracle SID: 125
=====================================================================
SID/Serial : 125,7
Foreground : PID: 6264 - sqlplus@vm1 (TNS V1-V3)
Shadow : PID: 6424 - oracle@vm1 (TNS V1-V3)
Terminal : pts/1/ UNKNOWN
OS User : oracle on vm1
Ora User : TEST1
Status Flags: ACTIVE DEDICATED USER
Tran Active : 000000006D49CFD0
Login Time : Mon 09:34:44
Last Call : Mon 11:13:34 - .8 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
UPDATE TESTEDITION1 SET NAME='EDITION_UPDATENEW' WHERE NAME='EDI
TION_UPDATE'
Previous SQL statement:
SELECT SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') FROM DUAL
Session Waits:
WAITED SHORT TIME: db file sequential read
Connect Info:
: Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
: Oracle Advanced Security: authentication service for Linux: Version 11.2.0.3.0 - Production
: Oracle Advanced Security: encryption service for Linux: Version 11.2.0.3.0 - Production
: Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production
Locks:
TRANSAC ENQ H: X R: NONE - RS+SLOT#786451 WRP#322
DML/DATA ENQ H: RX R: NONE - TESTEDITION1
TYPE=AE H: S R: NONE - ID1=100 ID2=0
=====================================================================
SQL> @sid.sql
Enter Oracle SID: 130
=====================================================================
SID/Serial : 130,1
Foreground : PID: 6289 - oracle@vm1 (LGWR)
Shadow : PID: 6289 - oracle@vm1 (LGWR)
Terminal : UNKNOWN/ UNKNOWN
OS User : oracle on vm1
Ora User :
Status Flags: ACTIVE DEDICATED BACKGROUND
Tran Active : NONE
Login Time : Mon 09:32:42
Last Call : Mon 09:32:42 - 103.6 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
Previous SQL statement:
Session Waits:
WAITING: log file parallel write
Connect Info:
Locks:
REDO THREAD H: X R: NONE - THREAD=1
=====================================================================
SQL> @sid.sql
1Enter Oracle SID:6
=====================================================================
SID/Serial : 6,1
Foreground : PID: 6291 - oracle@vm1 (CKPT)
Shadow : PID: 6291 - oracle@vm1 (CKPT)
Terminal : UNKNOWN/ UNKNOWN
OS User : oracle on vm1
Ora User :
Status Flags: ACTIVE DEDICATED BACKGROUND
Tran Active : NONE
Login Time : Mon 09:32:42
Last Call : Mon 09:32:43 - 105.0 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
Previous SQL statement:
Session Waits:
WAITING: rdbms ipc message
Connect Info:
Locks:
CONTROL FILE H: RS R: NONE - ID1=0 ID2=0
TYPE=RD H: NULL R: NONE - ID1=1 ID2=0
TYPE=XR H: NULL R: NONE - ID1=4 ID2=0
TYPE=RS H: RS R: NONE - ID1=25 ID2=1
From above results, it is evident that the tables are getting locked with multiple versions/editions of PLSQL are used.
10. Next, it is also noticed that the PLSQL which uses old data for update too will fail with “No data found” error while the plsql with old version will be successful. This is possible vice-versa.
SQL> exec test_edition_1;
The edition name is EDITION1
Welcome to my new editionEDITION1
BEGIN test_edition_1; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TEST1.TEST_EDITION_1", line 12
ORA-06512: at line 1
SQL> exec test_edition_1;
The edition name is ORA$BASE
Welcome to my new editionORA$BASE
Number of rows0
PL/SQL procedure successfully completed.
Conclusion
a. Edition based Redefinition is used only for online patching with minimal downtime.
b. Old version of Pl/Sql should not be used once the new version is activated in the application.
c. If both the versions needs to be used, careful design of the application required.
No comments:
Post a Comment