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 NOVEMBER 28, 2022

Applies to:

Symptoms

 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.0.6.2.0.sql 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);
BEGIN
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,
RTI.type,
RTI.table_name,
UI.index_name usr_index
from rev_tab_indexes RTI, user_indexes UI
where upper(RTI.Index_Name) = UI.Index_Name(+))
loop
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 ';
else
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,
RTI.N_SEQUENCE sequence
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,
0,
length(sql_create_index) - 1);
sql_create_index := sql_create_index || ')';
execute immediate sql_create_index;
end if;
end loop;
end if;
end;
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(T4CTTIoer11.java:509) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1747) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1712) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1699) ~[ojdbc8.jar:19.3.0.0.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:285) ~[ojdbc8.jar:19.3.0.0.0]
at DBUtil.executeQuery(DBUtil.java:332) [OFSAAIUpdate.jar:?]
at DBUtil.executeFile(DBUtil.java:262) [OFSAAIUpdate.jar:?]
at AtomicUpdate.updateAllSchemas(AtomicUpdate.java:226) [OFSAAIUpdate.jar:?]
at AtomicUpdate.updateAtomic(AtomicUpdate.java:28) [OFSAAIUpdate.jar:?]
at ApplyPatch.update(ApplyPatch.java:1746) [OFSAAIUpdate.jar:?]
at ApplyPatch.main(ApplyPatch.java:189) [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(T4CTTIoer11.java:513) ~[ojdbc8.jar:19.3.0.0.0]
... 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

Cause

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