When I run a report against a big table I got the following Oracle error:

*** 2007-03-29 13:56:50.090
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [12700], [4389808], [163632983], [23], [104892995], [25], [], []
Current SQL statement for this session:
DELETE FROM DELTEK.RPT_PROJ_LAB_SUM
WHERE
(PROJ_ID >= :1 AND
PROJ_ID <= :2 ) AND
FY_CD = :3 AND
PD_NO = :4 AND
SUB_PD_NO = :5 AND COMPANY_ID = ‘1′

Search Metalink and Intenet for solution, I have these possibilities:

You may receive an ORA-00600[12700] in one of the following scenarios where an index is used:
1. select, update, delete queries,
2. calling stored procedures.
3. running pro*c or pl/sql application
4. ora 600 [12700][1409438660][32] using a BITMAP index.
5. and other possible scenarios.

Problem Explanation:
====================
Some possible reasons you may receive this error are:

1. Some detected cases are problems when doing a query (doing consistent read) using an index but the corresponding row to be fetch from the data block is inconsistent (most likely does not exist). The more common case is when there is an index split and CR has to roll it back so you may see this [12700] when there are queries against growing indices. Other cases may happen when there are heavy update and query activities against the same set of data.

2. There is a corruption, usually in the index. Doing an analyze table validate structure cascade will catch any corruption. In these cases, dropping and recreating the object will solve the error.

3. If you alter a column that has a BITMAP index, the index is flagged as invalid.

Steps to solve the problem

1. Create two procedures by sys user.

CREATE OR REPLACE PROCEDURE oerr12700( a number , b number, c number) IS
un varchar2(99);tn varchar2(99); trowid varchar2(99);
ind_name varchar2(99); ind_col varchar2(99);
nfile number; nblock number; nrow number;
fname VARCHAR2(513) ;
dbs number ;
dbs_x varchar2(129);
x number;

BEGIN

x:= dbms_utility.get_parameter_value(’db_block_size’,dbs,dbs_x);

nfile:=dbms_utility.data_block_address_file(b);
select FILE_NAME into fname from dba_data_files
where RELATIVE_FNO = nfile ;

nblock:=dbms_utility.data_block_address_block(b);
select NAME,dba_users.username into tn,un from obj$,dba_users where dataobj#=a
and dba_users.user_id=obj$.owner# ;

trowid:= dbms_rowid.rowid_create(1,a,nfile,nblock,c);

dbms_output.put_line(’ ORA-600 [12700] ['||a||'],['||b||'],['||c||']‘);
dbms_output.put_line(’————————————————–’);
dbms_output.put_line(’there is an index pointing to a row in ‘||un||’.'||tn);
dbms_output.put_line(’row is slot ‘||c||’ in file ‘||nfile||’ block ‘||nblock);
dbms_output.put_line(’one index entry is pointing to ROWID=”’|| trowid||””);
dbms_output.put_line(’————————————————–’);
dbms_output.put_line(’You may want to check the integrity of ‘||un||’.'||tn);
dbms_output.put_line(’executing :’);
dbms_output.put_line(’dbv file=’||fname||’
blocksize=’||dbs||’ start=’|| nblock||’ end=’||nblock);
dbms_output.put_line(’————————————————–’);

–
dbms_output.put_line(’IF dbv does not show any corruption, you can try to’);
dbms_output.put_line(’find the corrupted indexes using the queries proposed’);
dbms_output.put_line(’by the procedure oerr12700diag(’||a||’,'||b||’,'||c||’)');
dbms_output.put_line(’——————————————————-’);
END;
/

CREATE OR REPLACE PROCEDURE oerr12700diag( a number , b number, c number) IS
un varchar2(99);tn varchar2(99); trowid varchar2(99);
ind_name varchar2(99); ind_col varchar2(99);
nfile number; nblock number; nrow number;

cursor pindexes(towner varchar2, tname varchar2) is
select C.INDEX_NAME,COLUMN_NAME from dba_ind_columns C, dba_indexes I
where c.INDEX_NAME=i.INDEX_NAME
and I.INDEX_TYPE <> ‘DOMAIN’
and C.TABLE_OWNER=towner and C.TABLE_NAME=tname
and C.COLUMN_POSITION=1 ;

rpindexes pindexes%rowtype;

BEGIN
nfile:=dbms_utility.data_block_address_file(b);
nblock:=dbms_utility.data_block_address_block(b);
select NAME,dba_users.username into tn,un from obj$,dba_users where dataobj#=a
and dba_users.user_id=obj$.owner# ;

trowid:= dbms_rowid.rowid_create(1,a,nfile,nblock,c);

dbms_output.put_line(’————————————————–’);
dbms_output.put_line(’IF dbv did not show any corruption, you can try to’);
dbms_output.put_line(’find the corrupted indexes using following queries:’);
dbms_output.put_line(’——————————————————-’);
dbms_output.put_line(’If a query returns “no rows selected” index is sane’);
dbms_output.put_line(’If a query returns ‘||trowid||’ index is corrupted’);
dbms_output.put_line(’…………………………………………..’);

dbms_output.put_line(’.');
dbms_output.put_line(’To test ‘||un||’.'||tn||’ indexes ‘) ;
dbms_output.put_line(’.');
for rpindexes in pindexes(un,tn) loop
dbms_output.put_line(’.');
dbms_output.put_line(’To test INDEX ‘||rpindexes.INDEX_NAME||’ you run :’ );
dbms_output.put_line(’.');
dbms_output.put_line(’select rowid “‘||rpindexes.INDEX_NAME||’ corrupted!”
from ‘);
dbms_output.put_line(
‘(SELECT /*+ INDEX_FFS(’||tn||’,'||rpindexes.INDEX_NAME||’) */ ‘);
dbms_output.put_line(
rpindexes.COLUMN_NAME||’,rowid from ‘||
un||’.'||tn||’ where ‘||
rpindexes.COLUMN_NAME||’='||rpindexes.COLUMN_NAME||’) ‘ );
dbms_output.put_line( ‘where rowid=”’||trowid||”’;'||’ ‘);
end loop ;
END;
/

2. Run this SQL:

SQL> ANALYZE TABLE DELTEK.RPT_PROJ_LAB_SUM VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE DELTEK.RPT_PROJ_LAB_SUM VALIDATE STRUCTURE CASCADE
*

ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

the Trace file generated:

Dump file c:\oracle\admin92\siit\udump\siit_ora_5292.trc
Thu Mar 29 15:49:09 2007
ORACLE V9.2.0.7.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Instance name: siit

Redo thread mounted by this instance: 1

Oracle process number: 121

Windows thread id: 5292, image: ORACLE.EXE

*** 2007-03-29 15:49:09.910
*** SESSION ID:(73.1621) 2007-03-29 15:49:09.895
Table/Index row count mismatch
table 14477651 : index 14482855, 5203
Index root = tsn: 9 rdba: 0×06401a8b

3. Run stored procedures

SQL> set serveroutput on
SQL> execute oerr12700(4389808,163632983,23);

ORA-600 [12700] [4389808],[163632983],[23]
————————————————–
there is an index pointing to a row in DELTEK.RPT_PROJ_LAB_SUM
row is slot 23 in file 39 block 55127
one index entry is pointing to ROWID=’AAQvuwAAnAAANdXAAX’
————————————————–
You may want to check the integrity of DELTEK.RPT_PROJ_LAB_SUM
executing :
dbv file=K:\ORADATA\SIIT\CP_SIIT_REPORT1_3.DBF
blocksize=8192 start=55127
end=55127
————————————————–
IF dbv does not show any corruption, you can try to
find the corrupted indexes using the queries proposed
by the procedure oerr12700diag(4389808,163632983,23)
——————————————————-

SQL> execute oerr12700diag(4389808,163632983,23);
————————————————–
IF dbv did not show any corruption, you can try to
find the corrupted indexes using following queries:
——————————————————-
If a query returns “no rows selected” index is sane
If a query returns AAQvuwAAnAAANdXAAX index is corrupted
…………………………………………..

To test DELTEK.RPT_PROJ_LAB_SUM indexes

To test INDEX SI_1037 you run :

select rowid “SI_1037 corrupted!”
from
(SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1037) */
PROJ_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where PROJ_ID=PROJ_ID)
where rowid=’AAQvuwAAnAAANdXAAX’;

To test INDEX SI_1038 you run :

select rowid “SI_1038 corrupted!”
from
(SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1038) */
ORG_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where ORG_ID=ORG_ID)
where rowid=’AAQvuwAAnAAANdXAAX’;

To test INDEX SI_1496 you run :

select rowid “SI_1496 corrupted!”
from
(SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1496) */
FY_CD,rowid from DELTEK.RPT_PROJ_LAB_SUM where FY_CD=FY_CD)
where rowid=’AAQvuwAAnAAANdXAAX’;

PL/SQL procedure successfully completed.

====================================

4. Run suggested SQL statements

SQL> select rowid “SI_1037 corrupted!”
2 from
3 (SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1037) */
4 PROJ_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where PROJ_ID=PROJ_ID)
5 where rowid=’AAQvuwAAnAAANdXAAX’;


SI_1037 corrupted!
——————
AAQvuwAAnAAANdXAAX

SQL> select rowid “SI_1496 corrupted!”
2 from
3 (SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1496) */
4 FY_CD,rowid from DELTEK.RPT_PROJ_LAB_SUM where FY_CD=FY_CD)
5 where rowid=’AAQvuwAAnAAANdXAAX’;

SI_1496 corrupted!
——————
AAQvuwAAnAAANdXAAX

SQL> select rowid “SI_1038 corrupted!”
2 from
3 (SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1038) */
4 ORG_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where ORG_ID=ORG_ID)
5 where rowid=’AAQvuwAAnAAANdXAAX’;

SI_1038 corrupted!
——————
AAQvuwAAnAAANdXAAX

5. Drop and recreate indexes:

DROP INDEX deltek.SI_1037;
DROP INDEX deltek.SI_1038;
DROP INDEX deltek.SI_1496;

CREATE INDEX deltek.SI_1037 ON deltek.RPT_PROJ_LAB_SUM
(PROJ_ID)
NOLOGGING
TABLESPACE COSTPOINT_INDEX2
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE INDEX deltek.SI_1038 ON deltek.RPT_PROJ_LAB_SUM
(ORG_ID)
NOLOGGING
TABLESPACE COSTPOINT_INDEX2
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE INDEX deltek.SI_1496 ON deltek.RPT_PROJ_LAB_SUM
(FY_CD, PD_NO, SUB_PD_NO, REC_TYPE)
NOLOGGING
TABLESPACE COSTPOINT_INDEX2
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

6. Run diag script again and I got no error message

SQL> execute oerr12700diag(4389808,163632983,23);
————————————————–
IF dbv did not show any corruption, you can try to
find the corrupted indexes using following queries:
——————————————————-
If a query returns “no rows selected” index is sane
If a query returns AAQvuwAAnAAANdXAAX index is corrupted
…………………………………………..
.
To test DELTEK.RPT_PROJ_LAB_SUM indexes
.
.
To test INDEX SI_1037 you run :
.
select rowid “SI_1037 corrupted!”
from
(SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1037) */
PROJ_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where PROJ_ID=PROJ_ID)
where rowid=’AAQvuwAAnAAANdXAAX’;
.
To test INDEX SI_1038 you run :
.
select rowid “SI_1038 corrupted!”
from
(SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1038) */
ORG_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where ORG_ID=ORG_ID)
where rowid=’AAQvuwAAnAAANdXAAX’;
.
To test INDEX SI_1496 you run :
.
select rowid “SI_1496 corrupted!”
from
(SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1496) */
FY_CD,rowid from DELTEK.RPT_PROJ_LAB_SUM where FY_CD=FY_CD)
where rowid=’AAQvuwAAnAAANdXAAX’;

PL/SQL procedure successfully completed.

SQL> select rowid “SI_1037 corrupted!”
2 from
3 (SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1037) */
4 PROJ_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where PROJ_ID=PROJ_ID)
5 where rowid=’AAQvuwAAnAAANdXAAX’;

no rows selected

SQL> select rowid “SI_1038 corrupted!”
2 from
3 (SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1038) */
4 ORG_ID,rowid from DELTEK.RPT_PROJ_LAB_SUM where ORG_ID=ORG_ID)
5 where rowid=’AAQvuwAAnAAANdXAAX’;

no rows selected

SQL> select rowid “SI_1496 corrupted!”
2 from
3 (SELECT /*+ INDEX_FFS(RPT_PROJ_LAB_SUM,SI_1496) */
4 FY_CD,rowid from DELTEK.RPT_PROJ_LAB_SUM where FY_CD=FY_CD)
5 where rowid=’AAQvuwAAnAAANdXAAX’;

no rows selected

Tables with a long (or LOB) datatype column is tough to be reorganized once it becomes fragmented, you can not use “Alter table move tablespace” command, you can’t use “Create Table As Select” command either.

But now you still have an option: use “Copy from using” command:

connect deltek/xxx@fin;
set heading off;
set feedback off;
set pagesize 1000;
set linesize 120;
set echo off;
spool C:\Oracle\job\reorg\copy_from.sql;
select ‘connect deltek/xxx@fin;’ from dual;
select ’select count(*) FROM ‘|| table_name ||’;'||chr(10)||
‘COPY FROM deltek/xxx@fin create ‘|| table_name ||’_ USING select * from ‘|| table_name ||’;'||chr(10)||
‘drop table ‘ || table_name || ‘;’||chr(10)||
‘rename ‘ || table_name || ‘_ to ‘|| table_name ||’;’
from user_tables
where table_name in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’ and NUM_ROWS>0 ) ;
select ‘exit;’ from dual;
spool off;

Then run copy_from.sql to reorganize the tables.

If there is a primary key-foreign key relationship in a table, constraints must be disabled in order to drop the table.

spool C:\Oracle\job\reorg\const.sql;
SELECT ‘ALTER TABLE ‘ || table_name || ‘
DISABLE CONSTRAINT ‘ || constraint_name || ‘;’
FROM user_constraints where table_name
in (select table_name from user_tables where tablespace_name=’COSTPOINT_DATA’ );

spool off;

Then eable constraints:

SELECT 'ALTER TABLE ' || table_name || '
enable CONSTRAINT ‘ || constraint_name || ‘;’
FROM user_constraints;

You may also try dbms_redefinition to do reorganization.

life is way too long not do well.

Life is too short not to be happy.

Be happy.