Oracle Database Discussions

Hi Gurus,

I´m facing the error ORA-01410: invalid ROWID when I try to create the primary key of a table.

SQL> create index events_uk1 on events
2 (evt_time,evt_id)
3 local tablespace esentx
4 parallel nologging
5 compress 2
6 /
create index events_uk1 on events
*
ERROR at line 1:
ORA-01410: invalid ROWID

This error is shown when I try to select some data from the concerned table also.

I tried to run the dbms_repair.check_object but I faced an error also:

SET SERVEROUTPUT ON
SET TIMING ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'ESECDBA',
OBJECT_NAME => 'EVENTS',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

DECLARE num_corrupt INT;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [16], [0],
[32], [], [], [], []
ORA-06512: at "SYS.DBMS_REPAIR", line 294
ORA-06512: at line 4

Elapsed: 09:15:11.83

According some forumns, I saw which there´s a routine to check what´s the corrupt rowid´s, according code below:


prompt Enter the corrupted table_name
accept table_name char prompt 'Table_name: '

prompt Enter the error to catch (prefix with an '-' sign, example: -1410)
accept error_to_catch number prompt 'Error_to_catch: '

drop table table_corrupt_rowid;
create table table_corrupt_rowid (row_id rowid);

drop table table_corrupt_copy;
create table table_corrupt_copy as select * from &&table_name where 1=2;

create or replace procedure p_table_corrupt(row_id rowid)as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into table_corrupt_rowid values (row_id);
commit;
end;
/
show errors

declare
cursor c is select rowid from &&table_name order by rowid asc;
dummy varchar2(1);
corrupt_row exception;
pragma exception_init(corrupt_row, &error_to_catch);
row_id rowid;
begin
for c_cur in c
loop
begin
row_id := c_cur.rowid;
insert into table_corrupt_copy
select * from &&table_name where rowid=c_cur.rowid;
exception
when corrupt_row then
p_table_corrupt(row_id);
end;
end loop;
end;
/

Prompt Checking the no of rows in original table:
select '&&table_name',count(*) from &&table_name;

Prompt Checking the no of rows in copy table:
select 'table_corrupt_copy', count(*) from table_corrupt_copy;

Prompt Showing the rowid's as catched by the exception handler as set:
select * from table_corrupt_rowid;


The procedure has been executed successfully and the table TABLE_CORRUPT_ROWID has been filled ok.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count (*) from table_corrupt_rowid;

COUNT(*)
----------
66662683

Once my attempts to fix this rowid´s failed, I want to delete these lines from my production table, but I can´t do it:

Production Table: EVENTS


delete from events where rowid in (select row_id from table_corrupt_rowid);

0 rows deleted.

Elapsed: 01:27:29.59

Please help me.

Thanks in advance.