My Oracle Support Banner

While upgrading from 8.0.6 to 8.1.1 java.sql.SQLException: ORA-01408: such column list already indexed (Doc ID 2861507.1)

Last updated on APRIL 12, 2022

Applies to:

Oracle Financial Services Behavior Detection Platform - Version 8.1.1 and later
Information in this document applies to any platform.


 While installing p31284429_80000_Linux-x86-64, faced below error in ofsaaupdate.log.

Log [2022-04-04 15:11:29.728] [OFSAAIUPDATE] [DEBUG] - Executing script atomic_8. for DB User : ofsaafccm
Log [2022-04-04 15:11:30.409] [OFSAAIUPDATE] [ERROR] - Exception while executing the query - declare
index_count number;
is_oracle_infodom number(1);
sql_create_index varchar2(3000) := '';
index_name varchar2(30);
ind_count number(1);
select count(1)
into is_oracle_infodom
from ofsaaconf.dsnmaster DM, ofsaaconf.aai_db_detail AAIDD
where DM.DBNAME = AAIDD.v_db_name
and AAIDD.v_db_type = 'ORACLE'
and upper(AAIDD.v_Schema_Name) = upper(user);
if is_oracle_infodom > 0 then
for ind_name_rec in (select distinct RTI.index_name,
UI.index_name usr_index
from rev_tab_indexes RTI, user_indexes UI
where upper(RTI.Index_Name) = UI.Index_Name(+))
if ind_name_rec.usr_index is null then
sql_create_index := 'create';
if ind_name_rec.type = 'UNIQUE' then
sql_create_index := sql_create_index || ' UNIQUE INDEX ';
sql_create_index := sql_create_index || ' INDEX ';
end if;
sql_create_index := sql_create_index || ind_name_rec.index_name ||
' ON ' || ind_name_rec.table_name || ' (';
for ind_rec in (select RTI.COLUMN_NAME column_name,
from rev_tab_indexes RTI
where index_name = ind_name_rec.index_name
order by RTI.n_Sequence) loop
sql_create_index := sql_create_index || ind_rec.column_name ||
' ASC,';
end loop;
sql_create_index := substr(sql_create_index,
length(sql_create_index) - 1);
sql_create_index := sql_create_index || ')';
execute immediate sql_create_index;
end if;
end loop;
end if;
java.sql.SQLException: ORA-01408: such column list already indexed
ORA-06512: at line 43
ORA-06512: at line 43

at oracle.jdbc.driver.T4CTTIoer11.processError( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4CTTIoer11.processError( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4C8Oall.processError( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4CTTIfun.receive( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4CTTIfun.doRPC( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4C8Oall.doOALL( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4CStatement.doOall8( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4CStatement.doOall8( ~[ojdbc8.jar:]
at oracle.jdbc.driver.T4CStatement.executeForRows( ~[ojdbc8.jar:]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout( ~[ojdbc8.jar:]
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal( ~[ojdbc8.jar:]
at oracle.jdbc.driver.OracleStatement.executeLargeUpdate( ~[ojdbc8.jar:]
at oracle.jdbc.driver.OracleStatement.executeUpdate( ~[ojdbc8.jar:]
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate( ~[ojdbc8.jar:]
at DBUtil.executeQuery( [OFSAAIUpdate.jar:?]
at DBUtil.executeFile( [OFSAAIUpdate.jar:?]
at AtomicUpdate.updateAllSchemas( [OFSAAIUpdate.jar:?]
at AtomicUpdate.updateAtomic( [OFSAAIUpdate.jar:?]
at ApplyPatch.update( [OFSAAIUpdate.jar:?]
at ApplyPatch.main( [OFSAAIUpdate.jar:?]
Caused by: oracle.jdbc.OracleDatabaseException: ORA-01408: such column list already indexed
ORA-06512: at line 43
ORA-06512: at line 43

at oracle.jdbc.driver.T4CTTIoer11.processError( ~[ojdbc8.jar:]
... 19 more
Log [2022-04-04 15:11:30.414] [OFSAAIUPDATE] [DEBUG] - Error while executing - declareindex_count number;is_oracle_infodom number(1);sql_create_index varchar2(3000) := '';index_name varchar2(30);ind_count number(1);BEGINselect count(1)into is_oracle_infodomfrom ofsaaconf.dsnmaster DM, ofsaaconf.aai_db_detail AAIDDwhere DM.DBNAME = AAIDD.v_db_nameand AAIDD.v_db_type = 'ORACLE'and upper(AAIDD.v_Schema_Name) = upper(user);if is_oracle_infodom > 0 thenfor ind_name_rec in (select distinct RTI.index_name,RTI.type,RTI.table_name,UI.index_name usr_indexfrom rev_tab_indexes RTI, user_indexes UIwhere upper(RTI.Index_Name) = UI.Index_Name(+))loopif ind_name_rec.usr_index is null thensql_create_index := 'create';if ind_name_rec.type = 'UNIQUE' thensql_create_index := sql_create_index || ' UNIQUE INDEX ';elsesql_create_index := sql_create_index || ' INDEX ';end if;sql_create_index := sql_create_index || ind_name_rec.index_name ||' ON ' || ind_name_rec.table_name || ' (';for ind_rec in (select RTI.COLUMN_NAME column_name,RTI.N_SEQUENCE sequencefrom rev_tab_indexes RTIwhere index_name = ind_name_rec.index_nameorder by RTI.n_Sequence) loopsql_create_index := sql_create_index || ind_rec.column_name ||' ASC,';end loop;sql_create_index := substr(sql_create_index,0,length(sql_create_index) - 1);sql_create_index := sql_create_index || ')';execute immediate sql_create_index;end if;end loop;end if;end;
Log [2022-04-04 15:11:30.415] [OFSAAIUPDATE] [DEBUG] - ORA-01408: such column list already indexed
ORA-06512: at line 43
ORA-06512: at line 43


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.