ORA-01466 in a procedure with SET TRANSACTION READ ONLY

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ORA-01466 in a procedure with SET TRANSACTION READ ONLY

Postby dariyoosh » Thu Jun 13, 2013 11:30 am

Dear Tim,


I would like to ask you about a problem with SET TRANSACTION READ ONLY causing ORA-01466 being raised in my program.

Code: Select all
Oracle version:       Enterprise Edition Release 11.2.0.1.0 - 64bit
OS:                   Linux Fedora Core 17 (X86_64)


And here is the test case to use for this question
Code: Select all
HOST clear;
SET SQLBLANKLINES ON;
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
SET SERVEROUTPUT ON;


DROP TABLE tmptab;
CREATE TABLE tmptab AS
    SELECT  level lvl
    FROM    dual
    CONNECT BY level <= 10;
   

-- The program just reads the numbers in the tmptab created above
-- and puts them in a cursor and will print those
-- numbers

CREATE OR REPLACE PROCEDURE mytestProc
AUTHID CURRENT_USER
IS 
    nmbrs       SYS_REFCURSOR;
    nbr         tmptab%ROWTYPE;
BEGIN
    COMMIT;
    SET TRANSACTION READ ONLY NAME 'my read-only transaction';
   
    sys.dbms_lock.sleep(seconds=>10);
   
    OPEN nmbrs FOR
        SELECT lvl
        FROM tmptab;
    LOOP
        FETCH nmbrs INTO nbr;
        EXIT WHEN nmbrs%NOTFOUND;
        sys.dbms_output.put_line('lvl = ' || nbr.lvl);
    END LOOP;
    CLOSE nmbrs;
    COMMIT;
END mytestProc;
/
SHOW ERRORS;



BEGIN
    mytestProc();
END;
/


DROP PROCEDURE mytestProc;
DROP TABLE tmptab;

SET SERVEROUTPUT OFF;


Currently I'm reading about SET TRANSACTION READ ONLY in the Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) online book, chapter 6 PL/SQL Static in order to learn and understand the staff. According to the documentation:
http://docs.oracle.com/cd/E11882_01/app ... .htm#i3316
. . .
During a read-only transaction, all queries refer to the same snapshot of the database, providing a multi-table, multi-query, read-consistent view. Other users can continue to query or update data as usual. A commit or rollback ends the transaction.
. . .

What I understand from this (please correct me if I'm wrong) is that I take an image (in terms of data) from the table, meanwhile other users will be able to continue their job on my table (even do updates or delete), except that the output of their job will not be visible for my PL/SQL program until a ROLLBACK or COMMIT terminates my SET TRANSACTION READ ONLY.

I just wanted to see this in practice, so I created the test case provided above. Here is what I decided to do as a test:

1. I create a table named tmptab populated with a few numbers (just an example)

2. I open two linux terminals trm1 and trm2 each of them with a SQL*Plus session opened.

3. I launch the test case script at trm1, once the program arrives at sys.dbms_lock.sleep(seconds=>10); it halts (sleeps) for 10 seconds, and meanwhile I go to trm2 and I'll do some UPDATE on the table.

4. Because of SET TRANSACTION READ ONLY, the update done at step 3 will not be visible in my procedure, and the cursor will include only values that were in the table before the update in trm2

5. After the COMMIT has been done at the end of my procedure, the (READ ONLY) transaction will be terminated and only then the result of the update done in trm2 will also be visible in trm1

Now, the problem is that apparently things don't work that way. When I tried to run the above scenario I was stopped at the step 3 by the following error message.
Code: Select all
BEGIN
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
ORA-06512: at "TRAINING.MYTESTPROC", line 10
ORA-06512: at line 2

Besides, it seems that this behaviour is not deterministic, that is, I may launch 10 times the test script, for example 8 times I get the above error message and 2 times the script works properly by printing on the screen the values in the table tmptab.
Code: Select all
lvl = 1
lvl = 2
lvl = 3
lvl = 4
lvl = 5
lvl = 6
lvl = 7
lvl = 8
lvl = 9
lvl = 10

PL/SQL procedure successfully completed.

I checked the online documentation for the error message
http://docs.oracle.com/cd/E11882_01/ser ... #sthref819
ORA-01466: unable to read data - table definition has changed

Cause: Query parsed after tbl (or index) change, and executed w/old snapshot
Action: commit (or rollback) transaction, and re-execute

Well, if you look at the test case, I do a COMMIT before SET TRANSACTION READ ONLY and one COMMIT at
the end of the procedure. So I don't see why I get this error.

So I started Googling to see what's the problem and I found an article which seems to be related to my problem.
If you look at the test case I provided above here is what I do
Code: Select all
CREATE TABLE tmp AS . . .

CREATE OR REPLACE PROCEDURE . . .
BEGIN
    COMMIT;
    SET TRANSACTION READ ONLY NAME 'my Read-only Transaction';
    . . .
    COMMIT;
END proc;


I found the following article which explain a similar problem and based on this article I arrived at the conclusion that the problem is the CREATE TABLE statement just before my procedure definition. In order to prove this I put the CREATE TABLE statement in comment and I run the very same script multiple times successfully without that error.

Here is the link

http://arjudba.blogspot.fr/2008/05/ora- ... table.html

According to this article, the problems seems to be due to SCN (System Change Number). As I understand the value of an SCN is the logical point in time at which changes are made to a database. Therefore, whenever I run a CREATE TABLE statement which does an implicit COMMIT (if the DDL has been successfull), this increments SCN, but not immediately rather after a few seconds. So it may happen (as I indicated in my test case) that my program runs SET TRANSACTION READ ONLY at a given instant when the SCN still refers to its older value (that is, the last value before creating the table).

I don't know whether my understanding of this article is correct, but here is how I changed the code

Code: Select all
HOST clear;
SET SQLBLANKLINES ON;
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
-- SET TIMING ON;
SET SERVEROUTPUT ON;


DROP TABLE tmptab;
CREATE TABLE tmptab AS
    SELECT  level lvl
    FROM    dual
    CONNECT BY level <= 10;
   
   
CREATE OR REPLACE PROCEDURE mytestProc
AUTHID CURRENT_USER
IS 
    nmbrs       SYS_REFCURSOR;
    nbr         tmptab%ROWTYPE;
    scn_old     NUMBER;
BEGIN
    COMMIT;

    -- So here write after doing the COMMIT I store the last
    -- available value of the SCN
    scn_old := sys.dbms_flashback.get_system_change_number;
   
    -- As long as the new value is not available, that is, as long as
    -- oracle has not incremented the value, we do a busy wait
    -- inside a loop
    WHILE (dbms_flashback.get_system_change_number = scn_old) LOOP
        NULL;
    END LOOP;
   
    -- We have left the loop, so now can be sure that the SCN has the
    -- correct value
    SET TRANSACTION READ ONLY NAME 'my read-only transaction';
   
    sys.dbms_output.put_line('Sleeping for 10 seconds');
    sys.dbms_lock.sleep(seconds=>10);
   
    OPEN nmbrs FOR
        SELECT lvl
        FROM tmptab;
    LOOP
        FETCH nmbrs INTO nbr;
        EXIT WHEN nmbrs%NOTFOUND;
        sys.dbms_output.put_line('lvl = ' || nbr.lvl);
    END LOOP;
    CLOSE nmbrs;
    COMMIT;
END mytestProc;
/
SHOW ERRORS;



BEGIN
    mytestProc();
END;
/


DROP PROCEDURE mytestProc;
DROP TABLE tmptab;

SET SERVEROUTPUT OFF;


It seems that this modification in the code
Code: Select all
. . .
scn_old := sys.dbms_flashback.get_system_change_number;
   
WHILE (dbms_flashback.get_system_change_number = scn_old) LOOP
    NULL;
END LOOP;
. . .

improved considerably the problem. I created a KornShell script which launched 200 times my PL/SQL script and only 14 ended with ORA-01466.

Therefore, I would like to ask your advice to see is my approach correct and what is the problem because as I said I still get ORA-01466 error (much less than before) but the fact that there are still error, shows that clearly my solution is not correct.

Thanks in advance,
Dariyoosh
User avatar
dariyoosh
Member
 
Posts: 24
Joined: Tue Aug 11, 2009 11:18 am

Re: ORA-01466 in a procedure with SET TRANSACTION READ ONLY

Postby Tim... » Thu Jun 13, 2013 3:43 pm

Hi.

The transaction stuff only relates to DML (INSERT, UPDATE, DELETE), not DDL. Instead of dropping and creating the table, just do the test with inserts and deletes.

Before you waste time looking at this I would ask if you are ever likely to use this in a real situation? I've been using Oracle for nearly 20 years and do you know how often I've used SET TRANSACTION? Exactly 0. Yes. Never! Why? Because Oracle's read-consistency model is pretty cool. Some would say the envy of all the other database engines.

Oracle does not block reads. By default, uncommited changes in one session are not visible to other sessions. Oracle uses undo to keep a consistent view of the data at the point in time when the query was issued. The test you are trying to describe is exactly what Oracle does by default. You don't need SET TRANSACTION to get that behaviour.

The SET TRANSACTION stuff is only there because it is an ANSI requirement and allows you to cripple Oracle's read-consistency model to make it work as badly as some other engines. Just my opinion. :)

With this in mind, I'm finding it really hard to work up the energy to even run your example, because I think both of us would be better spent using out time to do something more productive.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: ORA-01466 in a procedure with SET TRANSACTION READ ONLY

Postby dariyoosh » Thu Jun 13, 2013 5:07 pm

Hello again


Before you waste time looking at this I would ask if you are ever likely to use this in a real situation? I've been using Oracle for nearly 20 years and do you know how often I've used SET TRANSACTION? Exactly 0.

Ok, Thanks for this information. Well, I think so far, you should have probably discovered that there is a huge difference between your knowledge in oracle and a newbie like me :D

I was just reading the documentation, in order to learn what I didn't know about static SQL in that chapter and at the end of the chapter, I discovered about these topics and just because it was in the documentation, I said to myself maybe it was important to learn as the documentation doesn't specify what is important to learn and what is not :) .

But according to what you said, apparently it is almost never used in a classic oracle environment.

Anyway, thank you very much for your time and for your attention to my problem.

Regards,
Dariyoosh
User avatar
dariyoosh
Member
 
Posts: 24
Joined: Tue Aug 11, 2009 11:18 am

Re: ORA-01466 in a procedure with SET TRANSACTION READ ONLY

Postby Tim... » Thu Jun 13, 2013 5:24 pm

Hi.

I'm not saying don't look at it. Don't let me put you off. :) Just put it further down the order of things you need to look at. Understanding Oracle's default read-consistency model is super important, but using this stuff to effectively cripple it is a low priority in my book. Other may say different.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests