I have been contacted by one of our customers with quite interesting problem.
They have upgraded their oracle database to 22.214.171.124.1 and faced really non-typical problem:
ALTER TABLE OWN.TABLE_LOG MOVE PARTITION p_201202 tablespace LOG_ARCHIVE compress UPDATE INDEXES ( OWN.I_TABLE_LOG_SYSTEMDAY (partition p_201202 tablespace LOG_ARCHIVE) , OWN.I_TABLE_LOG_ID (partition p_201202 tablespace LOG_ARCHIVE) ) ; DBMS_INDEX_UTL: Error executing ALTER INDEX "OWN"."I_TABLE_LOG_SYSTEMDAY" REBUILD PARTITION "p_201202" NOPARALLEL DBMS_INDEX_UTL: Caused by ORA-01422: exact fetch returns more than requested number of rows DBMS_INDEX_UTL: Error executing ALTER INDEX "OWN"."I_TABLE_LOG_ID" REBUILD PARTITION "p_201202" NOPARALLEL DBMS_INDEX_UTL: Caused by ORA-01422: exact fetch returns more than requested number of rows
Customer’s DBA was able to rebuild mentioned indexes by running ALTER INDEX command provided inside error message, so why they don’t work inside ALTER TABLE … MOVE PARTITION … UPDATE INDEXES ?
I have requested a trace file for mentioned statement with binds (but without waits – performance wasn’t an issue).
It was about 2.5MB that is quite huge… – almost all is recursive SQLs.
Searching by ‘err=1422‘ from the end to beginning of provided trace-file found the failing cursor and it was… custom ALTER-trigger with main goal to prohibit unauthorized schema changes, but the most interesting is the failing sql statement inside trigger:
SELECT sid, machine, terminal, OSUser INTO lnSid, lvMachine, lvTerminal, lvOSUser FROM v$session WHERE audsid = userenv('sessionid');
So why this statement returns more than 1 row ?
I’s because ALTER INDEX REBUILD PARTITION are executed by DBMS_INDEX_UTL inside another RECURSIVE session which has AUDSID=0 like sessions for BACKGROUND processes and “internally generated SYS session“. So the main problem is because AUDSID is NOT UNIQUE among current sessions and trigger’s code is not able to cope with this behaviour!
- exclude session with AUDSID=0 from processing inside trigger. You have think additionally about sessions with AUDSID=4294967295(direct SYS session), because AUDSID for them NON UNIQUE too.
- identify sessions by SID like WHERE sid=(SELECT sid FROM v$mystat WHERE rownum=1)
- How Sessions get Their AUDSID Identifier [ID 122230.1]
- How To Identify an RDBMS session using AUDSID [ID 123128.1]
- DBMS_JOB.SUBMIT Causes Login Trigger To Fire [ID 730832.1]