Ora-01410: Invalid Rowid

[yxez]

Hi Friends,

I've created a view of two tables, and I included rowid because I am fetching
its uniqueness thru it. For example:
Tables:
1. LOANS_A (NAME, AMOUNT)
2. LOANS_B (NAME, AMOUNT)

CREATE VIEW LOANS AS SELECT ROWID as R1,NAME,AMOUNT FROM LOANS_A
UNION ALL SELECT ROWID as R1,NAME,AMOUNT FROM LOANS_B;

sql> SELECT R1 FROM LOANS;

R1
---

AAAAAbbbbbbdddd1ee


sql> select * from loans where r1='AAAAAbbbbbbdddd1ee';

ORA-01410: Invalid Rowid


How do I avoid this error pls....


Thanks a lot

[davey23uk]

you cant query rowid's like that, you shoukdnt really be using rowid at all, use your own primary key

[yxez]

thanks davey
sad :( ... this table dont have PK and its partitioned one and got 100+million rows.

Is this the right wat to do the primary key?:

alter table loans_a add(pk_1 number);
alter table loans_b add(pk_2 number);

update loans_a set pk_1=rownum;
update loans_b set pk_2=rownum;

alter table loans_a add primary key (pk_1);
alter table loans_b add primary key (pk_2);

Thanks

[hrishy]

Hi

Whats the table partitioned on ?

You should understand the underlying data model before deciding on the primary key

You can always use a rownum or a sequence to generate the primary key

regards
Hrishy

[PAVB]

... this table dont have PK and its partitioned one and got 100+million rows.

To find your PK candidate ask yourself "how do I know there are no duplicate rows in this table?"