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?
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:
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.
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
April 23, 2005 - 9:12 am UTC
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?
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.
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
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?
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
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 ?
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.
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,
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
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
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
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.
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,
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???
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.