Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, anthony.

Asked: September 10, 2002 - 11:42 am UTC

Last updated: May 12, 2011 - 3:25 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi tom,

Can you explain to me what happened here??


SQL> delete from temp_Tbl;

97741 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into temp_tbl select * from all_objects;

97741 rows created.

SQL> commit;

Commit complete.

SQL> select object_name
2 from temp_Tbl , x1
3 where temp_Tbl.rowid(+)=x1.c1;

OBJECT_NAME
------------------------------
CUCY_PAIR
DENO_S
DEFTS
DETL
DIY
DI_PAIR
ED
ED2
EENT
FTY_TBE

10 rows selected.

SQL> truncate table temp_Tbl;

Table truncated.

SQL> insert into temp_tbl select * from all_objects;

97741 rows created.

SQL> commit;

Commit complete.

SQL> select object_name
2 from temp_Tbl , x1
3 where temp_Tbl.rowid(+)=x1.c1;
from temp_Tbl , x1
*
ERROR at line 2:
ORA-01410: invalid ROWID

TNX.....
NOTNA

and Tom said...

don't know what X1 is but...

X1 contains some rowid that is not in TEMP_TBL after the second insert. ROWIDS are physical addresses -- the data got put in in different places after the truncate then it did after an insert.

In fact, the DELETE/INSERT could equally easily throw the invalid rowid error as well.

ROWIDS are physical addresses and only exist for the life of a row. They can be reused by some new row later. you got "lucky" the first time (or depending on how you look at it, you got UNLUCKY -- you got 10 rows but not the 10 rows that X1 used to point at). you got "unluck"/"lucky" the second time.

Rating

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 11, 2005 - 1:35 pm UTC

hi tom
i issue a sql like
select cola ,sum(colb)
from table
group by cola
i get
ERROR at line 3:
ORA-01410: invalid ROWID
i checked dba_indexes..the indexes are valid
any suggestions?


Tom Kyte
March 12, 2005 - 8:18 am UTC

you'd have to either

a) work this with support
b) give us a test case to work with.

Invalid rowid

A reader, April 05, 2005 - 4:48 pm UTC

Hi Tom,
One of our monthly batch job was executed succussfully in previous months, but failed this month because:
ORA-20001: ORA-01410: invalid ROWID
Could you please let me know the possible cause of error, and how to avoid this?
Thanks
btw: oerr utility does not help much
oerr ora 01410
01410, 00000, "invalid ROWID"
// *Cause:
// *Action:


Tom Kyte
April 05, 2005 - 6:59 pm UTC

you'd have to give me a tad more information, generally happens when an application uses a rowid that is invalid.


ops$tkyte@ORA9IR2> column rowid new_val r
ops$tkyte@ORA9IR2> select rowid from dual;
 
ROWID
------------------
AAAADeAABAAAAZSAAA 
 
ops$tkyte@ORA9IR2> select * from scott.emp where rowid = '&r';
old   1: select * from scott.emp where rowid = '&r'
new   1: select * from scott.emp where rowid = 'AAAADeAABAAAAZSAAA'
select * from scott.emp where rowid = 'AAAADeAABAAAAZSAAA'
                    *
ERROR at line 1:
ORA-01410: invalid ROWID


but could be thrown for a variety of internal issues (product issues...)

so, one would need more info.  suggest you work this with support. 

expected ROWID got NUMBER

Bhavesh, April 22, 2005 - 2:11 am UTC

Hi Tom,
I wanted to delete the duplicated records,
Eg. TEST table has 2 colums : id,name
and i want to delete record with the same name doesnt matter if id with different,
and i m succedded also
but playing around i found some thing strange,
I got an error like
ORA-00932: inconsistent datatypes: expected ROWID got NUMBER
So Is it means that rowid only works when rows having some number field.
By The Way I passed SQL' paper of OCP,
And I want to thank you for that Because reading your site
I got really good knowledge of SQL.
Thanks in advance.

Tom Kyte
April 22, 2005 - 10:28 am UTC

need example...

Example

Bhavesh Ghodasara, April 23, 2005 - 12:03 am UTC

Hello Tom,
Here is my example:
(I WANT TO DELETE THE DUPLICATE ROW.)
=>table DUMMY:
NO NUMBER
NAME VARCHAR2(20)
=>records :
NO NAME
--------- --------------------
1 bhavesh
3 chirag
1 bhavin
2 bhavesh
5 sachin
6 rahul
8 rahul
Query=>
delete
from dummy a
where rowid <
(select r
from
(select rowid r,name
from dummy)b
where a.name=b.name
and r<=1)
Error=>
ORA-00932: inconsistent datatypes: expected ROWID got NUMBER

I can do this query by find max or min rowid in subquery and delete other records, But suppose i want to delete the record not base on the max or min rowid although i can return one row in upper query. Thats what i trying to do.

Thanks In Advance

Tom Kyte
April 23, 2005 - 9:12 am UTC

r <= 1

r is a rowid, not a number.


</code> http://asktom.oracle.com/pls/ask/search?p_string=delete+duplicates <code>

for answers.

SORRY

Bhavesh Ghodasara, April 23, 2005 - 12:07 am UTC

Sorry Tom
I totally going wrong in above query..
Confusion between rowid and rownum in writing.
SORRY

Miffed about this....

Mark, August 31, 2005 - 4:22 pm UTC

Hi Tom,

Oracle 8.1.7.4 E Windows

THis makes no sense to me. Here is an example of two cursors, both returning no rows, and one failing for invalid rowid.

15:56:43 HT4:DEVDB001155108:15:51 - DEV> DECLARE
15:56:43 2 CURSOR c
15:56:43 3 IS
15:56:43 4 SELECT parent_company_id, group_id, COUNT(ppbid) ppbcount, SUM(adj)
15:56:43 5 adj
15:56:43 6 FROM (SELECT NVL(parent_company_id, 0) parent_company_id
15:56:43 7 ,NVL(group_id, 0) group_id, ppbid, SUM(adj) adj
15:56:43 8 ,SUM(payments) payments, exppay
15:56:43 9 FROM (SELECT pft.parent_company_id, pft.group_id
15:56:43 10 ,DECODE(
15:56:43 11 pft.transaction_type_id, 6
15:56:43 12 ,SUM(pft.debit_amount - pft.credit_amount), 0
15:56:43 13 ) adj
15:56:43 14 ,DECODE(
15:56:43 15 pft.transaction_type_id, 2
15:56:43 16 ,SUM(pft.credit_amount), 0
15:56:43 17 ) payments
15:56:43 18 ,MIN(ppb.pat_procedure_balance_id) ppbid
15:56:43 19 ,pp.pat_procedure_id, mpc.payment_amount exppay
15:56:43 20 FROM pat_procedure_balance ppb
15:56:43 21 ,pat_financial_transaction pft
15:56:43 22 ,pat_procedure pp
15:56:43 23 ,maint_payment_comparison mpc
15:56:43 24 ,pat_insurance pi
15:56:43 25 WHERE ppb.pat_procedure_balance_id =
15:56:43 26 pft.pat_procedure_balance_id
15:56:43 27 AND ppb.balance_paid = 'Y'
15:56:43 28 AND pp.pat_procedure_id = ppb.pat_procedure_id
15:56:43 29 AND ppb.pat_insurance_id = pi.pat_insurance_id
15:56:43 30 AND mpc.maint_payment_comparison_id =
15:56:43 31 ppb.maint_payment_comparison_id (+)
15:56:43 32 AND ppb.approving_operator_id IS NULL
15:56:43 33 AND pp.service_date_from >= SYSDATE - 31
15:56:43 34 AND pp.service_date_from <= SYSDATE
15:56:43 35 AND pft.posting_date IS NOT NULL
15:56:43 36 AND pp.group_id = pft.group_id
15:56:43 37 AND pp.entity_id = ppb.entity_id
15:56:43 38 AND ppb.entity_id = pft.entity_id
15:56:43 39 AND pp.entity_id = pi.entity_id
15:56:44 40 GROUP BY pft.parent_company_id
15:56:44 41 ,pft.group_id
15:56:44 42 ,pp.pat_procedure_id
15:56:44 43 ,pft.transaction_type_id
15:56:44 44 ,mpc.payment_amount)
15:56:44 45 HAVING SUM(adj) <> 0
15:56:44 46 AND SUM(payments) < exppay * .95
15:56:44 47 GROUP BY parent_company_id, group_id, exppay, ppbid)
15:56:44 48 GROUP BY parent_company_id, group_id;
15:56:44 49 BEGIN
15:56:44 50 DBMS_OUTPUT.enable;
15:56:44 51
15:56:44 52 FOR rec IN c
15:56:44 53 LOOP
15:56:44 54 DBMS_OUTPUT.put_line(rec.parent_company_id);
15:56:44 55 END LOOP;
15:56:44 56 END;
15:56:44 57 /
DECLARE
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 4
ORA-06512: at line 52


Elapsed: 00:00:00.47
15:56:44 HT4:DEVDB001155108:15:51 - DEV>
15:56:44 HT4:DEVDB001155108:15:51 - DEV> DECLARE
15:56:44 2 CURSOR c
15:56:44 3 IS
15:56:44 4 SELECT *
15:56:44 5 FROM dual
15:56:44 6 WHERE 1 = 2;
15:56:44 7 BEGIN
15:56:44 8 DBMS_OUTPUT.enable;
15:56:44 9
15:56:44 10 FOR rec IN c
15:56:44 11 LOOP
15:56:44 12 DBMS_OUTPUT.put_line(rec.dummy);
15:56:44 13 END LOOP;
15:56:44 14 END;
15:56:44 15 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16

p.s. Do you ever lecture in the Boston Area?

Tom Kyte
September 01, 2005 - 1:32 am UTC

that first one looks like a product issue.

I've done various seminars in the Boston area, including the local user groups (they meet in the Waltham, MA Oracle office - was just there not too long ago in fact.

I put links on my home page, think the next time I'll be close by will be the CTOUG.

Fix for invalid rowid

Thomas Starr, January 04, 2006 - 7:51 pm UTC

After a user deleted rows from a table, trying to add a unique deferrable constraint resulted in ORA-01410 invalid ROWID.

Despite the index on the constrained column showed as valid in the dictionary, rebuilding it allowed adding the constraint without error.

Oracle 10.1.0.2.0

Thanks in advance for thoughts on what this may mean, and how to avoid it.





Tom Kyte
January 05, 2006 - 9:43 am UTC

you would need to contact support for this one - it should not happen that way at all.

I would like to know all issues of invalid rowid cause.

Choke, March 06, 2006 - 2:59 am UTC

I found this problem on batch job every monday.
And I've tried to recreate it after found it as oracle support recommended.So,I would like to know all issues of invalid rowid cause for protection it.
Thank you
Choke

Invalid rowid

Clear, August 11, 2006 - 5:33 pm UTC

Hi Tom,

I got an error when I do the exp. Here is the error.
How do I fix this?
I ran select rowid from ADL_LOADER in sqlplus,
It's working OK. Any idea?

Thanks a lot.

. . exporting table ADL_GROUPS_TABLE 68 rows exported
. . exporting table ADL_HITS 518024 rows exported
. . exporting table ADL_LOADER
EXP-00008: ORACLE error 1410 encountered
ORA-01410: invalid ROWID
. . exporting table ADL_LOG 1888 rows exported


Tom Kyte
August 11, 2006 - 6:15 pm UTC

please contact support for that one.

A reader, August 12, 2006 - 9:49 am UTC

In the queries that failed

like
select object_name
2 from temp_Tbl , x1
3 where temp_Tbl.rowid(+)=x1.c1;
from temp_Tbl , x1
*
ERROR at line 2:
ORA-01410: invalid ROWID

and

select * from scott.emp where rowid = 'AAAADeAABAAAAZSAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID


Why an Invalid ROWID, why not a NO_DATA_FOUND event in SQL, why is the missing rowid so serious to abort with an error?


Tom Kyte
August 12, 2006 - 8:03 pm UTC

that is an invalid rowid to use against that table - that is a string that cannot be implicity converted into a rowid for that table.

similar to asking why:

select * from t where number_column = 'x';

returns invalid number and not "no data found"

INVALID ROWID when building the Index

Hitesh, August 17, 2006 - 1:08 pm UTC

Hi Tom,

I was building a local index as part of PK definition but get the error INVALID ROWID.

What could be the cause of the problem? 

SQL> alter table p_data add constraint PK_P_DATA primary key (p_timestamp, p_tag) using index (create index PK_P_DATA on p_data (p_timestamp, p_tag ) local tablespace ts_p_index parallel 16)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01410: invalid ROWID
 

Tom Kyte
August 17, 2006 - 1:19 pm UTC

please contact support for that.

IOT

yaro, October 04, 2006 - 4:53 pm UTC

Hello Tom,
Here is my example:

SELECT rowidtochar(ROWID) as ROVID FROM COUNTRIES;

ERROR at line 1:
ORA-01410: invalid ROWID

Countries is Index organization table and rowid is look like
*BAHFh6ICQ0H+

Why rowidtochar fail ?

Tom Kyte
October 04, 2006 - 5:22 pm UTC

because IOT's don't have real rowids, they have "urowids" - universal rowids.

ORA-01410 with "where current of" and BULK COLLECT

Rich, October 06, 2006 - 4:51 pm UTC

Hi Tom,

I'm not sure if this is a bug or if I am just doing something wrong:

Here is the testcase:

create table rer_test_tbl (id number, dname varchar2(50));

insert into rer_test_tbl values (1, 'aaa');
insert into rer_test_tbl values (2, 'bbb');
insert into rer_test_tbl values (3, 'ccc');
insert into rer_test_tbl values (4, 'ddd');
insert into rer_test_tbl values (5, 'eee');

declare
type id_type is table of number;
v_id_tbl id_type;
cursor c1 is
select id from rer_test_tbl for update;
begin
open c1;
loop
fetch c1 bulk collect into v_id_tbl limit 100;
for idx in v_id_tbl.first..v_id_tbl.last
loop
update rer_test_tbl
set dname = dname||'-'||v_id_tbl(idx)
where current of c1;
end loop;
exit when c1%notfound;
end loop;

close c1;
exception
when others then
raise_application_error (-20001, sqlerrm);
end;

ORA-20001: ORA-01410: invalid ROWID


Replacing "where current of" with "where id = v_id_tbl(idx)" will make it work.

Or, not using BULK COLLECT (just plain old FETCH INTO) will also work.

Your feedback will be greatly appreciated.

Tom Kyte
October 06, 2006 - 5:03 pm UTC

with the bulk collect there cannot be any concept such as "the current row"

you just fetched 100 of them - which one is "current"


you would

a) preferable not even using procedural code, your logic above surely does not need any.

b) if you really need procedural code, you would use select id, ROWID rid ... for update. then "update where rowid = rid(i)". Under the covers that is all current of does - it adds rowid to your query and adds the where rowid = to the predicate.


If you run:

drop table t;
create table t ( x int );
insert into t values ( 1 );
commit;


declare
cursor c is select * from t for update;
l_rec c%rowtype;
begin
open c;
loop
fetch c into l_rec;
exit when c%notfound;
dbms_monitor.session_trace_enable;
update t set x = 2 where current of c;
end loop;
close c;
end;
/


(use execute immediate 'alter session set sql-trace=true' if dbms_monitor is not in your release)

you'll find in the tkprof:

UPDATE T SET X = 2
WHERE
ROWID = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 1 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 190 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE T (cr=1 pr=0 pw=0 time=77 us)
1 TABLE ACCESS BY USER ROWID T (cr=1 pr=0 pw=0 time=25 us)


ORA-01410

Kuldeep, October 09, 2006 - 12:10 pm UTC

Dear Tom,

I am getting Invalid Rowid error message when executing the below query:

select count(distinct pm.messageid)
from kc_message m, kc_message pm,KC_KanaUser U
where
m.completedate > sysdate - 29
and trunc(sysdate) - 29 < (select trunc(max(createdate)) from kc_message
where caseid=m.CASEID and type=1 and completeeventid=9)
and m.type =1
and m.completeeventid = 9
and pm.messageid = m.parentid
and pm.type =2
and pm.completekanauserid = U.KANAUSERID;

If I replace m.caseid with pm.caseid it is giving the result. I understand it is executing successfully because this time I am using different set of rows and thats why it is executing successfully.

I am unable to understand why this query is giving invalid rowid error. I am not using rowid directly anywhere in this query.

Is this the possible that some index is having an invalid rowid and rebuilding indexs will solve my problem.

Thanks and regards,

Tom Kyte
October 09, 2006 - 1:34 pm UTC

probably you are using parallel query....

please contact support with your test case ...

ORA-01410: invalid ROWID

A reader, December 11, 2006 - 1:50 pm UTC

Tom,
In the review above (Kuldeep from India), you answered that the he might be using parallel query. Is there any known issue which might cause ORA-01410 with parallel query? We use parallel query and RAC and got this error. Before opening SR with support, I just wanted to find out more.
Thanks


Tom Kyte
December 11, 2006 - 2:27 pm UTC

just tell them what you are getting and the environment you are using, they will diagnose it from there.

Invalid rowid sometimes

martina, May 15, 2007 - 8:31 am UTC

Hi Tom,

very seldom we have an "invalid rowid" Problem. This is why i cannot contact support, i cannot reproduce. The program runs about 3 Times a day, 13 times at another server.

All tables involved are owned by the aborting program and i cannot imagine that sombody hacks at 2:30 in the morning.

Please take a look at the following logfile:

02:24:48 SQL> rem ___________________________________________ Artfdek suchen
02:24:48 SQL> rem ==================================================== schaun was drinnen ist
02:24:48 SQL> def fiup_leit      = fdek_l&seq_num
02:24:48 SQL> def fiup_klein     = fdek_k&seq_num
02:24:48 SQL> def fiup_tab  = &sc_prf_scarf_hlp.
02:24:48 SQL> def packl = 1000
02:24:48 SQL> set time on timi on
02:24:48 SQL> whenever sqlerror continue ;
02:24:48 SQL> -- drop table &fiup_leit ;
02:24:48 SQL> -- drop table &fiup_klein ;
02:24:48 SQL> create table &fiup_leit
02:24:48   2    tablespace &&temp &&mittel &&unrec
02:24:48   3  as select rowid adresse ,trunc((rownum+&packl.)/&packl.) nummer,1 anz
02:24:48   4                 ,rownum recnum
02:24:48   5                 from &fiup_tab
02:24:48   6                 where nvl(menge,0) != 0
02:24:48   7                   and dekw is null
02:24:48   8                 ;
old   1: create table &fiup_leit
new   1: create table fdek_l20002047
old   2:   tablespace &&temp &&mittel &&unrec
new   2:   tablespace TEMP   nologging
old   3: as select rowid adresse ,trunc((rownum+&packl.)/&packl.) nummer,1 anz
new   3: as select rowid adresse ,trunc((rownum+1000)/1000) nummer,1 anz
old   5:               from &fiup_tab
new   5:               from sc_prf_scarf_hlp

Table created.

Elapsed: 00:00:00.62
02:24:48 SQL> create unique index &fiup_leit._u1  on &fiup_leit (nummer ,adresse)
02:24:48   2  tablespace &&ind &&mittel &&unrec  ;
old   1: create unique index &fiup_leit._u1  on &fiup_leit (nummer ,adresse)
new   1: create unique index fdek_l20002047_u1  on fdek_l20002047 (nummer ,adresse)
old   2: tablespace &&ind &&mittel &&unrec
new   2: tablespace IND   nologging

Index created.

Elapsed: 00:00:00.54
02:24:49 SQL> analyze table &fiup_leit estimate statistics ;
old   1: analyze table &fiup_leit estimate statistics
new   1: analyze table fdek_l20002047 estimate statistics

Table analyzed.

Elapsed: 00:00:00.09
02:24:49 SQL> create table &fiup_klein tablespace &&temp &&klein &&unrec as

2:24:49   2  select nummer,count(*) anz
02:24:49   3  from &fiup_leit
02:24:49   4  group by nummer;
old   1: create table &fiup_klein tablespace &&temp &&klein &&unrec as
new   1: create table fdek_k20002047 tablespace TEMP   nologging as
old   3: from &fiup_leit
new   3: from fdek_l20002047

Table created.

Elapsed: 00:00:00.16
02:24:49 SQL> create unique index &fiup_klein._u1  on &fiup_klein(nummer )
02:24:49   2  tablespace &&ind &&klein &&unrec  ;
old   1: create unique index &fiup_klein._u1  on &fiup_klein(nummer )
new   1: create unique index fdek_k20002047_u1  on fdek_k20002047(nummer )
old   2: tablespace &&ind &&klein &&unrec
new   2: tablespace IND   nologging

Index created.

Elapsed: 00:00:00.03
02:24:49 SQL> analyze table &fiup_klein. estimate statistics ;
old   1: analyze table &fiup_klein. estimate statistics
new   1: analyze table fdek_k20002047 estimate statistics

Table analyzed.

Elapsed: 00:00:00.01
02:24:49 SQL> rem ___________________________________________________________ Fuer Neugierige
02:24:49 SQL> start &&direxe./&av_sub_dir./av_progdir av_mkmoni sql
02:24:49 SQL> rem
02:24:49 SQL> rem $Header: av_progdir.sql,v 1.4 99/02/04 13:32:23 mpa ProdVersion $
02:24:49 SQL> rem
02:24:49 SQL>
02:24:49 SQL> rem  Programm : av_progdir.sql
02:24:49 SQL> rem  Funktion : Directory zu Programm
02:24:49 SQL> rem             Macht zu &p1,&p2 die variable  &p1._dir
02:24:49 SQL> rem  Parameter:
02:24:49 SQL> rem  Autor    : mpa
02:24:49 SQL> rem  History  : 9901  ,mpa:  Erfunden
02:24:49 SQL> rem 
___________________________________________________________________ Anfang02:24:49 SQL> rem ________________________________________________________ Tabellenname holen
02:24:49 SQL> def such_p = '&1'
02:24:49 SQL> def such_e  = '&2'
02:24:49 SQL> col &such_p._&such_e._dir new_value &such_p._&such_e._dir ;
02:24:49 SQL> select applikation &such_p._&such_e._dir
02:24:49   2  from bpa_programs
02:24:49   3  where prog_name = '&such_p.'
02:24:49   4  and   extension = '&such_e.'
02:24:49   5  ;
old   1: select applikation &such_p._&such_e._dir
new   1: select applikation av_mkmoni_sql_dir
old   3: where prog_name = '&such_p.'
new   3: where prog_name = 'av_mkmoni'
old   4: and   extension = '&such_e.'
new   4: and   extension = 'sql'

AV_MKMONI_SQL_
--------------
av7

1 row selected.

Elapsed: 00:00:00.00
02:24:49 SQL> start &&direxe./&av_mkmoni_sql_dir./av_mkmoni &fiup_klein &prog_name.fdek
02:24:49 SQL> rem
02:24:49 SQL> rem $Header: av_mkmoni.sql,v 1.14 99/08/18 20:04:50 mpa ProdVersion $
02:24:49 SQL> rem
02:24:49 SQL>
02:24:49 SQL> rem  Programm : av_mkmoni.sql
02:24:49 SQL> rem  Funktion : Einfuellen - Beobachtungsview
02:24:49 SQL> rem  Parameter:
02:24:49 SQL> rem  Autor    : vpe
02:24:49 SQL> rem  History  : 9901  ,mpa:  Erfunden [mpa-j2000]
02:24:49 SQL> rem ___________________________________________________________________ Anfang
02:24:49 SQL> set time on timi on  term on echo on
02:24:49 SQL> rem ________________________________________________________ Tabellenname holen
02:24:49 SQL> def moni_tab = '&1'
02:24:49 SQL> def moni_view  = '&2'
02:24:49 SQL> rem ___________________________________________________________ Fuer Neugierige
02:24:49 SQL> col c_moment new_value moment;
02:24:49 SQL> col c_anzahl new_value anzahl;
02:24:49 SQL> select nvl(sum(anz),0) c_anzahl from
02:24:49   2         &moni_tab. ;
old   2:        &moni_tab.
new   2:        fdek_k20002047

  C_ANZAHL
----------
     91411

1 row selected.

Elapsed: 00:00:00.01
02:24:49 SQL> select to_char(sysdate, 'YYYYMMDDHH24MISS') c_moment from dual;

C_MOMENT
--------------
20070512022449

1 row selected.

Elapsed: 00:00:00.00
02:24:49 SQL> create or replace view &moni_view._moni
02:24:49   2  as
02:24:49   3  select to_date ('&moment','YYYYMMDDHH24MISS') seit
02:24:49   4        ,&anzahl alle
02:24:49   5        ,decode((&anzahl - nvl(sum(anz),0)),0,'laeuft nicht'
02:24:49   6            ,to_char(sysdate  +
02:24:49   7             sum(anz) /  ( (&anzahl - sum(anz))/
02:24:49   8                   (sysdate - to_date('&moment','YYYYMMDDHH24MISS') ))
02:24:49   9        ,'YYMMDD HH24:MI:SS'))fertig
02:24:49  10        ,nvl(sum(anz),0) rest
02:24:49  11  from &moni_tab.;
old   1: create or replace view &moni_view._moni
new   1: create or replace view sc_prf2fdek_moni
old   3: select to_date ('&moment','YYYYMMDDHH24MISS') seit
new   3: select to_date ('20070512022449','YYYYMMDDHH24MISS') seit
old   4:       ,&anzahl alle
new   4:       ,     91411 alle
old   5:       ,decode((&anzahl - nvl(sum(anz),0)),0,'laeuft nicht'
new   5:       ,decode((     91411 - nvl(sum(anz),0)),0,'laeuft nicht'
old   7:            sum(anz) /  ( (&anzahl - sum(anz))/
new   7:            sum(anz) /  ( (     91411 - sum(anz))/
old   8:                  (sysdate - to_date('&moment','YYYYMMDDHH24MISS') ))
new   8:                  (sysdate - to_date('20070512022449','YYYYMMDDHH24MISS') ))
old  11: from &moni_tab.
new  11: from fdek_k20002047

View created.

Elapsed: 00:00:00.02
02:24:49 SQL> whenever sqlerror exit 9 rollback;
02:24:49 SQL> insert into av_monilog(moni,monitab,seq_num,anzahl,start_zeit)
02:24:49   2   values ('&moni_view._moni.','&moni_tab.',&seq_num,&anzahl,sysdate);
old   2:  values ('&moni_view._moni.','&moni_tab.',&seq_num,&anzahl,sysdate)
new   2:  values ('sc_prf2fdek_moni.','fdek_k20002047',20002047,     91411,sysdate)

1 row created.

1 row created.

Elapsed: 00:00:00.00
02:24:49 SQL>  commit;

Commit complete.

Elapsed: 00:00:00.01
02:24:49 SQL> rem ___________________________________________________________ Los gehts
02:24:49 SQL> declare cursor zaehl is
02:24:49   2          select nvl(max(nummer) ,0)
02:24:49   3          from &fiup_leit ;
02:24:49   4          anzahl number;
02:24:49   5  begin  /*111111*/
02:24:49   6    open zaehl;
02:24:49   7    fetch zaehl into anzahl;
02:24:49   8    close zaehl;
02:24:49   9    for i in 1..anzahl loop  /*222222*/
02:24:49  10      begin  /*333333*/
02:24:49  11        declare
02:24:49  12        cursor cleit is select *
02:24:49  13          from &fiup_klein where nummer = i
02:24:49  14          for update of nummer
02:24:49  15          ;
02:24:49  16        begin  /*444444*/
02:24:49  17        for rleit in cleit loop
02:24:49  18           begin /* 55555 */
02:24:49  19           declare cursor cdet is
02:24:49  20              select h.firma,h.filiale,h.artnr,h.datum from &fiup_leit l
02:24:49  21                           ,&fiup_tab  h
02:24:49  22                            where nummer = i
02:24:49  23                            and h.rowid = l.adresse
02:24:49  24                            for update of h.dekw
02:24:49  25                            ;
02:24:49  26           begin /*6666*/
02:24:49  27           for rdet in cdet loop
02:24:49  28               update &fiup_tab s
02:24:49  29               set dekw  = (select avg(s.menge*a.fdekp)
02:24:49  30                 from artfdek a
02:24:49  31                where s.firma = a.firma
02:24:49  32                  and s.filiale = a.filiale
02:24:49  33                  and s.artnr = a.artnr
02:24:49  34                  and s.datum between a.von_datum and a.bis_datum
02:24:49  35              )
02:24:49  36                   where current of cdet
02:24:49  37                   ;
02:24:49  38           end loop;
02:24:49  39           delete from &fiup_klein where current of cleit ;
02:24:49  40           end;  /*6666*/
02:24:49  41           end; /* 55555 */
02:24:49  42        end loop ;
02:24:49  43        end;   /*444444*/
02:24:49  44        commit;
02:24:49  45      end;   /*333333*/
02:24:49  46    end loop;   /*222222*/
02:24:49  47  end;   /*111111*/
02:24:49  48  /
old   3:         from &fiup_leit ;
new   3:         from fdek_l20002047 ;
old  13:         from &fiup_klein where nummer = i
new  13:         from fdek_k20002047 where nummer = i
old  20:            select h.firma,h.filiale,h.artnr,h.datum from &fiup_leit l
new  20:            select h.firma,h.filiale,h.artnr,h.datum from fdek_l20002047 l
old  21:                         ,&fiup_tab  h
new  21:                         ,sc_prf_scarf_hlp  h
old  28:              update &fiup_tab s
new  28:              update sc_prf_scarf_hlp s
old  39:          delete from &fiup_klein where current of cleit ;
new  39:          delete from fdek_k20002047 where current of cleit ;
declare cursor zaehl is
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 27


Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

bpa_dmon V9.61: SeqNr 20002047 Pid 20321 Exitcode 9 ExitSig 0


The only thing i can think of is that the Tables should be created logging. By the way, the Job was finished the next day, without problems!

thank you
martina
Tom Kyte
May 15, 2007 - 8:55 pm UTC

support can assist you

they can set an event that will get diagnostic information dumped whenever this ora error is encountered.

Random ORA-01410

Jonathan Lewis, May 15, 2007 - 11:15 pm UTC

Since this is running in the early hours of the morning, does it run concurrently with any house-keeping routines such as automated index rebuilds ?

correct

martina, May 16, 2007 - 1:51 am UTC

Yes Jonathan,

We did! When it aborted, the index of the table ARTFDEK was rebuilt (online). I did not realize that yesterday. Do you have any idea how to come by this issue? we are rebuilding indices once a week on 200-300 Databases and we do not really have a service window.

Thank You
martina
Tom Kyte
May 16, 2007 - 10:26 am UTC

how many people can see this question coming.....


Martina - why are you doing that?

use dbms_stats.gather_table_stats after create table

Pasko, May 16, 2007 - 1:35 pm UTC

Hi Martina,

I also once got this error in one of our Batch processes.
The reason was because some indexes were not used after the CTAS(create table as ...) and so the Queries took so long to run that another Run of the same Process was scheduled (through unix cron ) before the first Process completed.

It was an Application-Design flaw because the Batch Process was started by unix-cron utility and this Utility does not check if another instance of the same Job is running ( dbms_job does though :) )

So to prevent this we created a lock-file which ensured there were no multiple instances of the same process running at the same time and also we used dbms_stats.gather_table_stats(...) right after the CTAS.

This could possible be happening in your case because you:
--create table as select ...
--index the table
--select from table plus using rowids ( where current of )
--delete from the table

now if you have multiple instances of the batch process running at the same time,you definetely would get invalid rowid error.

Like Tom said, Index Rebuid is bad, but i would advice you to use dbms_stats.gather_table_stats() on all the Tables created in the Batch Process, because that way the Optimizer would get the correct statistics while the Tables have Data.

You or your DBA could also have a 'Database-wide-collect-statistics' Job, but if this job runs
before your CTAS completes, then your Tables will have wrong statistics.

I hope this helps,

Regards,

Pasko


Index Rebuild

Jonathan Lewis, May 16, 2007 - 6:46 pm UTC

Martina, rebuilding indexes on an active system always exposes you to a slight risk of randomly occurring ORA-01410 errors. Rebuilding lots of indexes on auto-pilot, one after the after, increases the risk of a random crash. There's a brief paragraph about this specific problem in the article pointed to by this URL: http://www.jlcomp.demon.co.uk/indexes_i.html

ORA-01410: invalid ROWID

K Nagaraju, April 06, 2010 - 5:40 am UTC

Tom, first of all, thanks for valueable inputs.

we too get the similar error, the below is the query caused the problem :

when we execute the following query for the pick list No's upto 853936, i am able to get the result

SQL> select PKSL_NO
from pick_lists
where rowid in(select rowid from pick_lists where PKSL_NO=853935);

   PKSL_NO
----------
    853935

SQL> select PKSL_NO
from pick_lists
where rowid in(select rowid from pick_lists where PKSL_NO=853936);

   PKSL_NO
----------
    853936

whereas when we execute the same query for the pick list No's after 853936, i am getting error "ORA-01410: invalid ROWID"


SQL> select PKSL_NO
from pick_lists
where rowid in(select rowid from pick_lists where PKSL_NO=853937);

ERROR at line 2:
ORA-01410: invalid ROWID

SQL> select PKSL_NO
from pick_lists
where rowid in(select rowid from pick_lists where PKSL_NO=853938);

ERROR at line 2:
ORA-01410: invalid ROWID

But the below query is giving the results when it run independently:

select rowid from pick_lists where PKSL_NO=853937;


One of our DBA said that this is a known bug:

    Bug : 2598043  
    Occurs when ...

        * Database has been upgraded to 9i
        * Table has more than 505 extents
        * Query uses rowid to access the extents > 505
        * WHERE rowid = '<rowid>' fails, but WHERE rowid LIKE '<rowid>%'

and he offered the below solution :

ALTER TABLE SOLEIL.PICK_LISTS MOVE;

alter index soleil.PKSL_FRGN1 rebuild parallel 4 nologging;
alter index soleil.PKSL_FRGN  rebuild parallel 4 nologging;
alter index soleil.PKSL_IND2  rebuild parallel 4 nologging;
alter index soleil.PKSL_PRIM  rebuild parallel 4 nologging;

alter index soleil.PKSL_FRGN1 rebuild noparallel logging;
alter index soleil.PKSL_FRGN  rebuild noparallel logging;
alter index soleil.PKSL_IND2  rebuild noparallel logging;
alter index soleil.PKSL_PRIM  rebuild noparallel logging;

please note that our database was migrated from 7 to 9.0.1

Now Is the DBA correct? if yes, why is this happening?
and how the solution would fix the issue.

Tom Kyte
April 12, 2010 - 7:02 pm UTC

I'm always confused by these questions.

It looks like your DBA did due diligence - apparently you must have

upgraded to 9i
with more than 505 extents in some table
and you are accessing data above the 505th extent
and you are using = (in is equals)

and they have the solution.


One wonders why you would migrate to an obsolete release - you've finally made it into this century, but the turn of the century.


Your DBA seems to have researched this well. Listen to them on this one.

The solution 'fixes' it by reorganizing everything.

Diff rebuild and re-craete of index.

Young, April 12, 2010 - 2:37 am UTC

Hello Tom,
I have few questions about the Rebuild and re-create(Drop and Create) option of index.

Which do you use or recommend at the below of accident on 10gR2.
Rebuild or Re-create(Drop and Create) option of index?

1.when Only table was corrupted?
2.when Only Index was corrupted?
3.when Table and Index was corrupted?
4.what is the main difference between rebuild and re-craete of index?

Thanks for your assistance,
Tom Kyte
April 13, 2010 - 9:13 am UTC

1) neither - just restore the corrupt blocks and be done with it.

2) neither, see #1

3) neither see #1

4) drop+create - you stand a chance of losing the index (what if the drop works but the create fails and you don't notice). drop+create - you have to full scan the table, sort it to build it, with a rebuild we can probably - in many cases - just read the existing index in order and build a new one - less io, no sort

Invalid Rowid

Victor, May 12, 2011 - 2:41 pm UTC

I have problems with a query.

I Tried to run this query but after 10 minutes running canceled with ORA-01410: Invalid Rowid

SELECT *
FROM FAV_CUENTA CTA
WHERE RTRIM(CTA.CODI_EMPRFACT) = RTRIM('VTF')
AND RTRIM(CTA.CODI_SUCUFACT) =
decode(RTRIM(''), '', RTRIM(CTA.CODI_SUCUFACT), RTRIM(''))
AND CTA.NMRO_CICLOFAC = 15
AND EXISTS
(SELECT *
FROM SUT_SERVICIO SER, SUT_CONSERVI CSERV, SUT_CONCEPTO_NEW CPTO
WHERE SER.FECH_ALTA IS NOT NULL
AND SER.IDEN_SERVICIO = CSERV.IDEN_SERVICIO
AND CSERV.CODI_CONCEPTO = CPTO.CODI_CONCEPTO
AND CSERV.FECH_INIFACT IS NOT NULL
AND ((CPTO.TIPO_CARGRENT = 'C' AND CSERV.FECH_LIMFACT IS NULL) OR
(CPTO.TIPO_CARGRENT = 'R' AND
NVL(CSERV.FECH_LIMFACT, SYSDATE) > ADD_MONTHS(SYSDATE, -8))))

but I Try now with 1 change

SELECT *
FROM FAV_CUENTA CTA
WHERE RTRIM(CTA.CODI_EMPRFACT) = RTRIM('VTF')
AND RTRIM(CTA.CODI_SUCUFACT) =
decode(RTRIM(''), '', RTRIM(CTA.CODI_SUCUFACT), RTRIM(''))
AND CTA.NMRO_CICLOFAC = 15
AND EXISTS
(SELECT 1
FROM SUT_SERVICIO SER, SUT_CONSERVI CSERV, SUT_CONCEPTO_NEW CPTO
WHERE SER.FECH_ALTA IS NOT NULL
AND SER.IDEN_SERVICIO = CSERV.IDEN_SERVICIO
AND CSERV.CODI_CONCEPTO = CPTO.CODI_CONCEPTO
AND CSERV.FECH_INIFACT IS NOT NULL
AND ((CPTO.TIPO_CARGRENT = 'C' AND CSERV.FECH_LIMFACT IS NULL) OR
(CPTO.TIPO_CARGRENT = 'R' AND
NVL(CSERV.FECH_LIMFACT, SYSDATE) > ADD_MONTHS(SYSDATE, -8))))

and this query ended very well...

Why???
Tom Kyte
May 12, 2011 - 3:25 pm UTC

those two queries are identical, the select list for an exists is not relevant.

I always just

select NULL from ...



Looks like you might have hit a bug - were you using parallel query by any chance?

The reason your change might have "worked" is because you had to hard parse for the 'new' query and you had a chance of getting a different plan from the first - I'd check that out and see if that happened.