This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 22, 2011 4:46 AM by 906492 RSS

ORA-01410 when selecting data via ROWID

906492 Newbie
Currently Being Moderated
hi there,

after update on 11.2.0.2 we are getting the following error in our application when selecting a record using the comparison via the ROWID:

select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s where s.rowid='AAASN0AAFAAACBUAAB';
-- => ORA-01410 (it doesn't matter which other column additionally to the rowid will be chosen)

performing the same select but using the * for all data
select s.rowid , s.* from vws_vrzng_enht_haupt_sys s where s.rowid='AAASN0AAFAAACBUAAB';
-- => one row will be received.

that is very strange to me.

Using the former release 10.2.0.4 everything was fine without receiving such an error.
Also the dirty workaround with setting the optimizer_features_enable to 10.2.0.4 will work....

Has anyone faced this error, too? Any help will be higly appreciated.
thanks Stefan
  • 1. Re: ORA-01410 when selecting data via ROWID
    fjfranken Expert
    Currently Being Moderated
    I see you have an Invalid char in your ROWID.
    ROWID's can only exist of hexadecimal values, i.e. 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E and F

    Please have a look at the ROWID entered here


    Cheers
    FJFranken


    Edit: Mistaken answer. Ignore please

    Edited by: fjfranken on 20-dec-2011 6:49
  • 2. Re: ORA-01410 when selecting data via ROWID
    Girish Sharma Guru
    Currently Being Moderated
    See my example :
    SQL> select sal,rowid from emp;
    
           SAL ROWID
    ---------- ------------------
           800 AAAR3sAAEAAAACXAAA
          1600 AAAR3sAAEAAAACXAAB
          1250 AAAR3sAAEAAAACXAAC
          2975 AAAR3sAAEAAAACXAAD
          1250 AAAR3sAAEAAAACXAAE
          2850 AAAR3sAAEAAAACXAAF
          2450 AAAR3sAAEAAAACXAAG
          3000 AAAR3sAAEAAAACXAAH
          5000 AAAR3sAAEAAAACXAAI
          1500 AAAR3sAAEAAAACXAAJ
          1100 AAAR3sAAEAAAACXAAK
    
           SAL ROWID
    ---------- ------------------
           950 AAAR3sAAEAAAACXAAL
          3000 AAAR3sAAEAAAACXAAM
          1300 AAAR3sAAEAAAACXAAN
    
    14 rows selected.
    
    SQL> select sal,empno from emp where rowid = chartorowid('AAAR3sAAEAAAACXAAE');
    
           SAL      EMPNO
    ---------- ----------
          1250       7654
    But in real life, we never uses rowid in where clause.

    Regards
    Girish Sharma
  • 3. Re: ORA-01410 when selecting data via ROWID
    fjfranken Expert
    Currently Being Moderated
    Another attempt:


    I get the same error in 11.2 if I use uppercase ROWID's
    ROWID's are case sensitive, so maybe start investigating there



    Success!!
    FJFranken
  • 4. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi there,

    thanks for your response on that:
    the strange behaviour is that
    performing the same select but using the s.* for all data
    select s.rowid , s.* from vws_vrzng_enht_haupt_sys s where s.rowid='AAASN0AAFAAACBUAAB';
    -- => one row will be received.

    BUT:
    select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s where s.rowid='AAASN0AAFAAACBUAAB';
    -- => ORA-01410 (it doesn't matter which other column additionally to the rowid will be chosen)

    therefore the rowid does exist, one entry will be found.
    BUT when using only one column instead of s.* then the ORA-01410 will be hit.

    thanks for any hints on that
    kind regards stefan
  • 5. Re: ORA-01410 when selecting data via ROWID
    Girish Sharma Guru
    Currently Being Moderated
    SQL> set line 200;
    SQL> select s.rowid,s.empno,s.sal from emp s where s.rowid='AAAR3sAAEAAAACXAAE';
    
    ROWID                   EMPNO        SAL
    ------------------ ---------- ----------
    AAAR3sAAEAAAACXAAE       7654       1250
    
    SQL> select s.rowid,s.* from emp s where s.rowid='AAAR3sAAEAAAACXAAE';
    
    ROWID                   EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ------------------ ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    AAAR3sAAEAAAACXAAE       7654 1          SALESMAN        7698 28-SEP-81       1250       1400         30
    What is output of your query :

    select s.rowid , s.* from vws_vrzng_enht_haupt_sys s where rownum<=5;
  • 6. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi the shortened output (because it is a huge view) of
    select rowidtochar(s.rowid) , s.* from vws_vrzng_enht_haupt_sys s where rownum<=5;
    is

    AAASN0AAFAAACBUAAB     12     xxxxx     1     10000     10000
    AAASN0AAFAAACBUAAA     13     xxxxx     1     10001     10001
    AAASN0AAFAAACBUAAN     14     xxxxx     1     10003     10003
    AAASN0AAFAAACBUAAF     16     xxxxx     1     10003     10003
    AAASN0AAFAAACBUAAO     15     xxxxx     1     10004     10004

    thanks and best regards
    Stefan
  • 7. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi all,

    another strange thing is this one:

    SQL> select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    2 where s.rowid='AAASN0AAFAAACBUAAB';
    select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    *
    FEHLER in Zeile 1:
    ORA-01410: Ung³ltige ROWID


    SQL> select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    2 where s.rowid=(select 'AAASN0AAFAAACBUAAB' from dual);

    ROWID VRZNG_ENHT_TITEL
    ------------------ -------------------------------------------------------------
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    -------------------------------------------------------------------------------
    AAASN0AAFAAACBUAAB Kanton Z³rich


    => using the rowid in an subselect works as well!

    but we don't want to adapt our complete source code because of that.
    Therefore help will be higly appreciated how to solve that issue without changing the code..

    thanks stefan
  • 8. Re: ORA-01410 when selecting data via ROWID
    Girish Sharma Guru
    Currently Being Moderated
    1.This error can also happen if you have an index rebuild online concurrently running.
    2.Table may being updated at the time you run your query.
    3.Are you using parallel anywhere in the index creation(if index exists) ?

    Some more notes by Sir Jonathan Lewis at : http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/

    Regards
    Girish Sharma
  • 9. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi Girish,

    the VWS_VRZNG_ENHT_HAUPT_SYS is a compley view that selects a couple of tables.
    I don't think that other processes are running in parallel and that someone updates the tables because I put the schema on an additional database for error testing purposes.

    thanks for any other hints on that
    regards Stefan
  • 10. Re: ORA-01410 when selecting data via ROWID
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    903489 wrote:

    SQL> select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    2 where s.rowid='AAASN0AAFAAACBUAAB';
    select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    *
    FEHLER in Zeile 1:
    ORA-01410: Ung³ltige ROWID

    SQL> select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    2 where s.rowid=(select 'AAASN0AAFAAACBUAAB' from dual);

    ROWID VRZNG_ENHT_TITEL
    ------------------ -------------------------------------------------------------
    AAASN0AAFAAACBUAAB Kanton Z³rich


    => using the rowid in an subselect works as well!
    There are some differences in the way that read-consistency operates when selecting by rowid, and perhaps this has changed in 11.2. As a first step of investigation I would enable the 10046 trace to see what's actually happening when the queries run.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Author: <b><em>Oracle Core</em></b>
  • 11. Re: ORA-01410 when selecting data via ROWID
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    903489 wrote:
    hi Girish,

    the VWS_VRZNG_ENHT_HAUPT_SYS is a compley view that selects a couple of tables.
    I don't think that other processes are running in parallel and that someone updates the tables because I put the schema on an additional database for error testing purposes.

    thanks for any other hints on that
    regards Stefan
    Hi Stefan,

    it looks like the execution plan could matter in your case. So can you give us the formatted EXPLAIN PLAN output for your most recent simple example where you get the ORA-01410 but do not get it when using the scalar subquery on DUAL?

    Please use DBMS_XPLAN.DISPLAY to format the execution plan and don't forget to include the "Predicate Information" section below the execution plan - it will be very likely the most important part in your particular case.

    Use the {noformat}{{noformat}code{noformat}}{noformat} tag before and after the plan output to have it formatted in fixed font for better readability - like this:

    {noformat}{{noformat}code{noformat}}{noformat}

    SQL> set linesize 200 pagesize 0 tab off

    SQL> select * from table(dbms_xplan.display);
    Plan hash value: 77569906

    ----------------------------------------------
    | Id | Operation | Name | Rows |
    ----------------------------------------------
    | 0 | SELECT STATEMENT | | 1 |
    | 1 | SORT AGGREGATE | | 1 |
    |* 2 | HASH JOIN | | 940K|
    |* 3 | TABLE ACCESS FULL | D | 100 |
    |* 4 | HASH JOIN | | 945K|
    |* 5 | TABLE ACCESS FULL | D | 100 |
    |* 6 | HASH JOIN | | 950K|
    |* 7 | TABLE ACCESS FULL| D | 10 |
    | 8 | TABLE ACCESS FULL| T | 1000K|
    ----------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - access("F"."FK3"="D"."ID")
    3 - filter("IS_FLAG_D3"='Y')
    4 - access("F"."FK1"="D"."ID")
    5 - filter("IS_FLAG_D1"='Y')
    6 - access("F"."FK2"="D"."ID")
    7 - filter("IS_FLAG_D2"='Y')

    {noformat}{{noformat}code{noformat}}{noformat}

    This will then turn into this:
    SQL> set linesize 200 pagesize 0 tab off
    
    SQL> select * from table(dbms_xplan.display);
    Plan hash value: 77569906
    
    ----------------------------------------------
    | Id  | Operation             | Name | Rows  |
    ----------------------------------------------
    |   0 | SELECT STATEMENT      |      |     1 |
    |   1 |  SORT AGGREGATE       |      |     1 |
    |*  2 |   HASH JOIN           |      |   940K|
    |*  3 |    TABLE ACCESS FULL  | D    |   100 |
    |*  4 |    HASH JOIN          |      |   945K|
    |*  5 |     TABLE ACCESS FULL | D    |   100 |
    |*  6 |     HASH JOIN         |      |   950K|
    |*  7 |      TABLE ACCESS FULL| D    |    10 |
    |   8 |      TABLE ACCESS FULL| T    |  1000K|
    ----------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("F"."FK3"="D"."ID")
       3 - filter("IS_FLAG_D3"='Y')
       4 - access("F"."FK1"="D"."ID")
       5 - filter("IS_FLAG_D1"='Y')
       6 - access("F"."FK2"="D"."ID")
       7 - filter("IS_FLAG_D2"='Y')
    Hope this helps,
    Randolf

    PS: Hint - you can use the "Reply" and then "Quote" button if you want to see the details how I formatted the code
  • 12. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi Randolf,

    thans a lot for your information on that.
    Folllowing the explain plan for the statement that ends with an ORA-01410.

    SQL> SELECT * FROM TABLE(dbms_xplan.display);
    Plan hash value: 1981411608

    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 72 | 1 (0)| 00:00:01 |
    | 1 | NESTED LOOPS | | 1 | 72 | 1 (0)| 00:00:01 |
    | 2 | NESTED LOOPS | | 1 | 57 | 1 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY USER ROWID| TBS_VRZNG_ENHT | 1 | 54 | 1 (0)| 00:00:01 |
    |* 4 | INDEX UNIQUE SCAN | CPK_GSFT_OBJ | 22 | 66 | 0 (0)| 00:00:01 |
    |* 5 | INDEX UNIQUE SCAN | CUK_GOBH_GO2_ID | 17 | 255 | 0 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    4 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")
    5 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")

    18 Zeilen ausgewõhlt.

    sorry but I didn't really get what you meant with the code and with the formatting of the code...

    thanks for any further information
    Stefan
  • 13. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi there,

    folllowing the explain plan when using a subselect with dual:
    select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    where s.rowid=(select 'AAASN0AAFAAACBUAAB' from dual);


    SQL> SELECT * FROM TABLE(dbms_xplan.display);
    Plan hash value: 3738078075

    --------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 39 | 4 (0)| 00:00:01 |
    | 1 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
    | 2 | NESTED LOOPS | | 1 | 18 | 1 (0)| 00:00:01 |
    |* 3 | TABLE ACCESS BY USER ROWID| TBS_GSFT_OBJ_BZHNG_HRCH | 1 | 15 | 1 (0)| 00:00:01 |
    | 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
    |* 5 | INDEX UNIQUE SCAN | CPK_GSFT_OBJ | 1 | 3 | 0 (0)| 00:00:01 |
    | 6 | TABLE ACCESS BY INDEX ROWID| TBS_VRZNG_ENHT | 1 | 21 | 1 (0)| 00:00:01 |
    |* 7 | INDEX UNIQUE SCAN | CPK_VRZNG_ENHT | 1 | | 0 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    3 - access(CHARTOROWID( (SELECT 'AAASN0AAFAAACBUAAB' FROM "SYS"."DUAL" "DUAL")))
    5 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
    7 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")

    thanks and best regards
    stefan
  • 14. Re: ORA-01410 when selecting data via ROWID
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Plan causing the ORA-01410:
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                 |     1 |    72 |     1   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |                 |     1 |    72 |     1   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS               |                 |     1 |    57 |     1   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS BY USER ROWID| TBS_VRZNG_ENHT  |     1 |    54 |     1   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN         | CPK_GSFT_OBJ    |    22 |    66 |     0   (0)| 00:00:01 |
    |*  5 |   INDEX UNIQUE SCAN          | CUK_GOBH_GO2_ID |    17 |   255 |     0   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")
       5 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
    Plan using the DUAL subquery:
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                         |     1 |    39 |     4   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |                         |     1 |    39 |     2   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS               |                         |     1 |    18 |     1   (0)| 00:00:01 |
    |*  3 |    TABLE ACCESS BY USER ROWID| TBS_GSFT_OBJ_BZHNG_HRCH |     1 |    15 |     1   (0)| 00:00:01 |
    |   4 |     FAST DUAL                |                         |     1 |       |     2   (0)| 00:00:01 |
    |*  5 |    INDEX UNIQUE SCAN         | CPK_GSFT_OBJ            |     1 |     3 |     0   (0)| 00:00:01 |
    |   6 |   TABLE ACCESS BY INDEX ROWID| TBS_VRZNG_ENHT          |     1 |    21 |     1   (0)| 00:00:01 |
    |*  7 |    INDEX UNIQUE SCAN         | CPK_VRZNG_ENHT          |     1 |       |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access(CHARTOROWID( (SELECT 'AAASN0AAFAAACBUAAB' FROM "SYS"."DUAL" "DUAL")))
       5 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
       7 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")
    Have you spotted the difference yourself? One execution plan applies the ROWID to a different table than the other one. You might have hit a bug here, potentially caused by a transformation applied by the optimizer.

    Can you show us the execution plan that worked in 10.2.0.4?

    Would it be possible to share the definition of the view "vws_vrzng_enht_haupt_sys"?

    Hope this helps,
    Randolf
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points