Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01410: invalid ROWID (2 Merged) (Oracle 10g release 2 10.2.0.3.0 on lkinux 64 bit)
ORA-01410: invalid ROWID (2 Merged) [message #495383] Fri, 18 February 2011 11:00 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,
I am getting following error

ORA-12012: error on auto execute of job 17069
ORA-01410: invalid ROWID
ORA-06512: at "t1.DR1", line 12
ORA-06512: at line 1

t1 is the schema and DR1 is the procedure run every hour

Error in Alert log showing thirce time.

Following is the content of procedure

create or replace procedure t1.dr1
is


CURSOR CS1
IS
select * FROM t1.V_TL_DR1;

begin


FOR CURRENT_RECORD IN CS1
LOOP
begin

insert into t1.tr_dr1
values
(t1.tr_dr1_seq.nextval,CURRENT_RECORD.uuid,default,'ANY',DEFAULT,NULL,NULL,NULL,null);

if CURRENT_RECORD.dv_code in('SD1','SD2') then

INSERT INTO t1.dr1_writer
values
(t1.dr1_writer_feed_seq.nextval,CURRENT_RECORD.uuid,'OP1',DEFAULT,NULL,DEFAULT,NULL,null,DEFAULT,NULL);

else

INSERT INTO t1.dr1_writer
values
(t1.dr1_writer_feed_seq.nextval,CURRENT_RECORD.uuid,'OP2',DEFAULT,NULL,DEFAULT,NULL,null,DEFAULT,NULL);

end if;

exception
WHEN DUP_VAL_ON_INDEX then
dbms_output.put_line('IT IS NOT ALLOWED TO DUPLICATE');

end;
END LOOP;


end;

t1.V_TL_DR1 IS a view which has two columns uuid and dv_code

Could any one tell me that, is it something to do with above procedure
Re: ORA-01410: invalid ROWID [message #495384 is a reply to message #495383] Fri, 18 February 2011 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: ORA-01410: invalid ROWID on execution of oracle procedure [message #495386 is a reply to message #495383] Fri, 18 February 2011 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: ORA-01410: invalid ROWID [message #495388 is a reply to message #495384] Fri, 18 February 2011 11:12 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
OK no problem

Could you please check that whether there is any thing wrong with the cursor or cursor loop i am using.

Re: ORA-01410: invalid ROWID [message #495390 is a reply to message #495388] Fri, 18 February 2011 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Could you please check that whether there is any thing wrong with the cursor or cursor loop i am using.

Without tables & data, there is nothing we can do.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Re: ORA-01410: invalid ROWID [message #495392 is a reply to message #495390] Fri, 18 February 2011 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Could you please check that whether there is any thing wrong with the cursor or cursor loop i am using.

I can't read something that is not correctly formatted.

Regards
Michel
Re: ORA-01410: invalid ROWID [message #495393 is a reply to message #495390] Fri, 18 February 2011 11:29 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Following is the tr_dr1 table

create table t1.tr_dr1
(
UUID NUMBER(18) not null,
DR_UID NUMBER(18) not null,
CREATED TIMESTAMP(6) default systimestamp not null,
DR_TYPE NVARCHAR2(20),
RESUMED_BY_A2 CHAR(1 CHAR) default 'N' not null,
RESUMED_BY_A2_AT TIMESTAMP(6),
RESUMED_BY_A4 NUMBER(18),
RESUMED_BY_A4_AT TIMESTAMP(6),
ALL_B CHAR(1 CHAR)
)
tablespace t1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table t1.tr_dr1
add constraint tr_dr1_PK primary key (UUID)
using index
tablespace T1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table t1.tr_dr1
add constraint tr_dr1_UK unique (DR_UID)
using index
tablespace T1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);


Second Table dr1_writer

create table t1.dr1_writer
(
UUID NUMBER(18) not null,
DR_UID NUMBER(18) not null,
DR_NAME NVARCHAR2(50),
CREATED TIMESTAMP(6) default systimestamp not null,
BACK_DATETIME NVARCHAR2(64),
PROCESSED CHAR(1 CHAR),
PROCESSED_AT TIMESTAMP(6),
MODIFIED TIMESTAMP(6),
OP_TYPE NVARCHAR2(20) default 'ADD' not null,
USER_UID NUMBER(18)
)
tablespace T1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T1.dr1_writer
add constraint dr1_writer_PK primary key (UUID)
using index
tablespace T1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

Re: ORA-01410: invalid ROWID [message #495396 is a reply to message #495393] Fri, 18 February 2011 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted.

Regards
Michel
Re: ORA-01410: invalid ROWID [message #495561 is a reply to message #495396] Mon, 21 February 2011 03:30 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
create or replace procedure t1.dr1
is
CURSOR CS1
IS
select * FROM t1.V_TL_DR1;

begin
FOR CURRENT_RECORD IN CS1
LOOP
begin

insert into t1.tr_dr1
values (t1.tr_dr1_seq.nextval,CURRENT_RECORD.uuid,default,'ANY',DEFAULT,NULL,NULL,NULL,null);

if CURRENT_RECORD.dv_code in('SD1','SD2') then

INSERT INTO t1.dr1_writer
values
(t1.dr1_writer_feed_seq.nextval,CURRENT_RECORD.uuid,'OP1',DEFAULT,NULL,DEFAULT,NULL,null,DEFAULT,NULL);

else

INSERT INTO t1.dr1_writer
values
(t1.dr1_writer_feed_seq.nextval,CURRENT_RECORD.uuid,'OP2',DEFAULT,NULL,DEFAULT,NULL,null,DEFAULT,NULL);

end if;

exception
WHEN DUP_VAL_ON_INDEX then
dbms_output.put_line('IT IS NOT ALLOWED TO DUPLICATE');

end;
END LOOP;


end;
Re: ORA-01410: invalid ROWID [message #495565 is a reply to message #495561] Mon, 21 February 2011 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 18 February 2011 18:44
Still not formatted.

Regards
Michel

Re: ORA-01410: invalid ROWID [message #495571 is a reply to message #495565] Mon, 21 February 2011 04:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
@MIFI - Read the orafaq forum guide which you've already been pointed to. It'll tell you how to format your code. You need to use [code] tags.
It'd help if you told us which line is line 12.

[Updated on: Mon, 21 February 2011 04:04]

Report message to a moderator

Re: ORA-01410: invalid ROWID [message #495574 is a reply to message #495571] Mon, 21 February 2011 04:16 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
create or replace procedure t1.dr1
is
 CURSOR CS1
 IS
 select * FROM t1.V_TL_DR1;

begin
 FOR CURRENT_RECORD IN CS1
  LOOP
   begin

    insert into t1.tr_dr1
    values (t1.tr_dr1_seq.nextval,CURRENT_RECORD.uuid,default,'ANY',DEFAULT,NULL,NULL,NULL,null);

     if CURRENT_RECORD.dv_code in('SD1','SD2') then

       INSERT INTO t1.dr1_writer
       values
       (t1.dr1_writer_feed_seq.nextval,CURRENT_RECORD.uuid,'OP1',DEFAULT,NULL,DEFAULT,NULL,null,DEFAULT,NULL);

    else

      INSERT INTO t1.dr1_writer
      values
      (t1.dr1_writer_feed_seq.nextval,CURRENT_RECORD.uuid,'OP2',DEFAULT,NULL,DEFAULT,NULL,null,DEFAULT,NULL);

    end if;

   exception
    WHEN DUP_VAL_ON_INDEX then
     dbms_output.put_line('IT IS NOT ALLOWED TO DUPLICATE');

   end;
  END LOOP;


end;


Line no 12 is "FOR CURRENT_RECORD IN CS1"

[Updated on: Mon, 21 February 2011 04:19]

Report message to a moderator

Re: ORA-01410: invalid ROWID [message #495575 is a reply to message #495574] Mon, 21 February 2011 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thank you - now which line is line 12? The first insert statement?
Re: ORA-01410: invalid ROWID [message #495576 is a reply to message #495575] Mon, 21 February 2011 04:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The For loop is line 12? Have you left some lines out?
Re: ORA-01410: invalid ROWID [message #495577 is a reply to message #495576] Mon, 21 February 2011 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Assuming it is the for loop - you need to show us the view definition.
Re: ORA-01410: invalid ROWID [message #495578 is a reply to message #495577] Mon, 21 February 2011 04:29 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
CREATE OR REPLACE VIEW t1.V_TL_DR1 AS
select distinct ap.uuid uuid,ap.adv_code adv_code

from 

	(select alj.app_uid app_uid,alj.stages_uid stages_uid
	,max(rate_AB) RATE_AB
	,max(rate_Ac) RATE_AC
	,max(rate_AD) RATE_AD
	,MAX(CREATED) CREATED
	from t1.aof alj, (select max(uuid) uuid,max(in_name) in_name from t1.stages where stage=50 group by app_uid) st2
	where alj.stages_uid=st2.uuid
	group by alj.app_uid,alj.stages_uid) alj

	,t1.app ap
	,t1.app_user appu
	,(select * from t1.app_applicant where joint='P') applicants

		WHERE

		alj.app_uid=ap.uuid

		and ap.user_uid=appu.uuid

		and ap.uuid=applicants.app_uid(+)

		and alj.created between systimestamp-(30/1440) and systimestamp-(10/1440)

		and ap.app_status='N'

		and
		(
		(RATE_AB is not null and RATE_AB<>0)
		OR (RATE_AC not null and RATE_AC<>0)
		OR (RATE_AD is not null and RATE_AD<>0 and applicants.residential_status not in('T','U'))
		)

		and appu.role='WEB'
		and ap.created between systimestamp-90 and systimestamp
		and ap.adv_code not in('UP1','UP2','UP3','UP4')



[Updated on: Mon, 21 February 2011 04:34]

Report message to a moderator

Re: ORA-01410: invalid ROWID [message #495580 is a reply to message #495578] Mon, 21 February 2011 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) If you just do
select * from t1.V_TL_DR1
in sqlplus do you get an error?
2) Does this procedure error out every time it's run?

I can't currently see anything that'd cause an invalid rowid error.
Re: ORA-01410: invalid ROWID [message #495592 is a reply to message #495580] Mon, 21 February 2011 05:19 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
No, sometimes , when the procedure runs it gives error.
procedure runs every 5 minutes
Today, it is not giving any error
1 month back, it gave same error and after a week we were unable to access "t1.app_applicant" and giving error of invalid rowid .
I tried to drop primary key on "t1.app_applicant" but i could not do that then i get all the records of "t1.app_applicant" from my replication server and create temp table and drop the table "t1.app_applicant" and rename the temp table to "t1.app_applicant".

Re: ORA-01410: invalid ROWID [message #495597 is a reply to message #495592] Mon, 21 February 2011 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
MIFI wrote on Mon, 21 February 2011 11:19
1 month back, it gave same error and after a week we were unable to access "t1.app_applicant" and giving error of invalid rowid .

So you've been getting an invalid rowid error querying one of the objects referenced by the view that the cursor selects from? And you didn't think this information was relevant?

MIFI wrote on Mon, 21 February 2011 11:19

I tried to drop primary key on "t1.app_applicant" but i could not do that

Because? did you get an error? which one?

Post the definition of t1.app_applicant.

MIFI wrote on Mon, 21 February 2011 11:19

then i get all the records of "t1.app_applicant" from my replication server


How's the replication server involved exactly?
Re: ORA-01410: invalid ROWID [message #495609 is a reply to message #495597] Mon, 21 February 2011 05:57 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
1) No, we were able to query that table even after the error ORA-01410: invalid ROWID occured on execution of procedure.
But after some days that table was not accessible.

We were able to get result from query
Select count(*) from t1.app_applicant but whenever we try to execute the query "select * from t1.app_applicant", we were getting ORA-01410

2)We had same error ORA-01410

3)Actually i thought that there is a problem with rowid in our primary database, we have our schema replciated (Stream schema replication) to other database, so i copy all records related to that table to temporary table and via database link create tamp table in primary database and then drop the corrupt table and rename temp table in production to original name.
Re: ORA-01410: invalid ROWID [message #495615 is a reply to message #495597] Mon, 21 February 2011 06:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
MIFI wrote on Mon, 21 February 2011 11:57

But after some days that table was not accessible.

Not accessible can mean all sorts of things, be specific.

cookiemonster wrote on Mon, 21 February 2011 11:24
Post the definition of t1.app_applicant.

Re: ORA-01410: invalid ROWID [message #495617 is a reply to message #495615] Mon, 21 February 2011 07:00 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
i could not do select from the table and i was not able to remove priimary key from table as it was giving same error ORA-01410
Re: ORA-01410: invalid ROWID [message #495621 is a reply to message #495617] Mon, 21 February 2011 07:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I asked for two things in my last post - one of which was a repeat request, which you have ignored again. If you want our help answer our questions.
Re: ORA-01410: invalid ROWID [message #495622 is a reply to message #495617] Mon, 21 February 2011 07:10 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
create table t1.app_applicant
(
  UUID                          NUMBER(18) not null,
  APP_UID                       NUMBER(18) not null,
  CORRESP_ADDRESS_UID           NUMBER(18),
  JOINT                         CHAR(1 CHAR) default 'P' not null,
  TITLE                         NVARCHAR2(16),
  FIRSTNAME                     NVARCHAR2(64),
  INITIALS                      NVARCHAR2(64),
  SURNAME                       NVARCHAR2(64),
  MAIDENNAME                    NVARCHAR2(64),
  DOB                           CHAR(10 CHAR),
  GENDER                        CHAR(1 CHAR),
  MARITAL_STATUS                CHAR(1 CHAR) default 'S',
  DEPENDENTS                    NUMBER(5),
  EMAIL                         NVARCHAR2(256),
  EMAIL2                        NVARCHAR2(256),
  HOME_PHONE                    NVARCHAR2(24),
  WORK_PHONE                    NVARCHAR2(24),
  MOBILE_PHONE                  NVARCHAR2(24),
  NUMBER_OF_EMPLOYMENTS         NUMBER(5),
  HAS_MULTIPLE_PROPERTIES       CHAR(1 CHAR),
  CREATED                       TIMESTAMP(6) default systimestamp not null,
  MODIFIED                      TIMESTAMP(6),
  NATIONALITY                   NVARCHAR2(100),
  HOUSEHOLD_TOTAL               NUMBER,
  HOUSEHOLD_EMPLOYED            NUMBER,
  RESIDENTIAL_STATUS            CHAR(1 CHAR) default 'T',
  UK_RESIDENT                   CHAR(1 CHAR) default 'Y',
  UK_CITIZEN                    CHAR(1 CHAR) default 'Y',
  )
tablespace t1
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table t1.app_applicant
  add constraint app_applicant_PK primary key (UUID)
  using index 
  tablespace t1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table t1.app_applicant
  add constraint app_applicant_CORRESP foreign key (CORRESP_ADDRESS_UID)
  references t1.ADDRESSES (UUID);
-- Create/Recreate indexes 
create index t1.app_applicant_FK on t1.app_applicant (CORRESP_ADDRESS_UID)
  tablespace t1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index t1.app_applicant_IND1 on t1.app_applicant (APP_UID)
  tablespace t1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
Re: ORA-01410: invalid ROWID [message #495635 is a reply to message #495622] Mon, 21 February 2011 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Nothing obvious there - only thing I think is some of the suggestions at the end of this thread. Such as rebuilding the index online.
Re: ORA-01410: invalid ROWID [message #495792 is a reply to message #495635] Tue, 22 February 2011 05:23 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
But i am not rebuilding any index and there was no index rebuild taking place at the time of Ora-01410 error
Re: ORA-01410: invalid ROWID [message #495794 is a reply to message #495792] Tue, 22 February 2011 05:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then you're probably going to have to raise this with oracle support.
1410 errors can only come from code that uses rowid explicitly or implicitly.
There are no explicit uses of rowid in the code you've shown us.
Which leaves the implicit, possibilites:
1) update where current of <cursor> - which you're not using
2) index rebuilds - which you say you don't do
3) corrupt index - should never happen
4) materialized views maybe.

I can't see anything that'd give the error.
Running a sql trace when it happens might help pin down the culprit.
Re: ORA-01410: invalid ROWID [message #495960 is a reply to message #495794] Wed, 23 February 2011 08:50 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
There is a web form which is loaded whenever any user logged in.
It is loading records by using pagination and rownum is being used.
Record is loaded by using view having joins of 5 different tables, which also include t1.app_applicant.

This view stoppped working and whenever we were executing this view it was giving ORA-01410 ERROR.

then i rebuilt primary key of "t1.app" table and try to execute the view again but it gave same error ora-01410.

select * from t1.app was working fine.

Then i try to access t1.app_applicant but still it was giving same error and select * from t1.app_applicant was giving error.

Then i had to recreate the table t1.app_applicant after dropping it.

My question is why t1.app_applicant had a problem and other tables like t1.app which is the mother table, was not afftected

[Updated on: Wed, 23 February 2011 08:53]

Report message to a moderator

Re: ORA-01410: invalid ROWID [message #495962 is a reply to message #495960] Wed, 23 February 2011 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Record is loaded by using view having joins of 5 different tables, which also include t1.app_applicant.
DROP all indexes prior to start of load.
Re: ORA-01410: invalid ROWID [message #495981 is a reply to message #495962] Wed, 23 February 2011 10:59 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Dropping and recreting an index is not a problem

But i want to know the reason that why we were not able to select any record from t1.app_applicant, because this can happen again
Re: ORA-01410: invalid ROWID [message #495988 is a reply to message #495981] Wed, 23 February 2011 11:12 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And there's no chance we can answer that without more information than has been supplied.
Full details of the loading process would be a good start.
Previous Topic: Auto number for primary key
Next Topic: Execute Immediate with dblink question
Goto Forum:
  


Current Time: Thu Mar 28 12:07:20 CDT 2024