ALTER TABLE MOVE PARTITION failes with DBMS_INDEX_UTL ORA-01422

I have been contacted by one of our customers with quite interesting problem.

They have upgraded their oracle database to 11.2.0.3.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 ?

There are not many references to DBMS_INDEX_UTL on My Oracle Support site and this one is definitely not among them, so I have decided that it may be a new BUG or some kind of Dictionary Corruption

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!

SOLUTIONs:

  • 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)

REFERENCEs:

AUDSID=0
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s