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 |
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 #495393 is a reply to message #495390] |
Fri, 18 February 2011 11:29 |
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 #495561 is a reply to message #495396] |
Mon, 21 February 2011 03:30 |
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 #495574 is a reply to message #495571] |
Mon, 21 February 2011 04:16 |
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 #495578 is a reply to message #495577] |
Mon, 21 February 2011 04:29 |
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 |
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 |
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 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
MIFI wrote on Mon, 21 February 2011 11:191 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 |
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 |
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:24Post the definition of t1.app_applicant.
|
|
|
|
|
Re: ORA-01410: invalid ROWID [message #495622 is a reply to message #495617] |
Mon, 21 February 2011 07:10 |
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 #495794 is a reply to message #495792] |
Tue, 22 February 2011 05:30 |
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 |
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 #495981 is a reply to message #495962] |
Wed, 23 February 2011 10:59 |
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
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 12:07:20 CDT 2024
|