Failed to upgrade geodatabase when trying to alter sde_logfiles table in user schema

Error Message

When running the Upgrade Geodatabase tool, the pre-requisite check finishes without any warning/error, but upgrading the geodatabase fails.

In the sde_setup.log file found in %ARCGISHOME%/etc folder, there are the following error messages:

====================
[Fri Jun 13 14:26:10 2014]
ST_Geometry Schema Owner: (SDE) Type Release: 1007
[Fri Jun 13 14:26:10 2014] Instance initialized for ((sde)) . . .

[Fri Jun 13 14:26:12 2014] SQL Stmt: <ALTER TABLE HRSD.sde_logfiles MODIFY LOGFILE_NAME NVARCHAR2(256)>
[Fri Jun 13 14:26:12 2014] Unable to upgrade registered tables, error = -25.
[Fri Jun 13 14:26:12 2014] DBMS error code: 1031
ORA-01031: insufficient privileges
[Fri Jun 13 14:26:12 2014] SDE release upgrade not completed(-25).
[Fri Jun 13 14:26:12 2014] ERROR installing/upgrading ArcSDE, Error = -25
====================

Verified user sde has been granted ALTER ANY TABLE privileges.

Cause

The error is when running the following SQL statement as user sde:

ALTER TABLE HRSD.sde_logfiles MODIFY LOGFILE_NAME NVARCHAR2(256);

Solution or Workaround

1. Connect as the table owner in SQL*Plus (or any Oracle client).
   
   

   SQLPLUS HRSD/test

   
   
2. Truncate the table.
   
   

   HRSD@ORCL>truncate table hrsd.sde_logfiles;

   
   
3. Verify the index and constraint on sde.states table, they may be altered due to the failed upgrade.
   
   Connect as user sde:
   
   

   select index_name from user_indexes where table_name = 'STATES';

   
   If the return is states_cuk, then we need to alter them back:
   connect as user sde:
   
   

   alter index states_cuk rename to states_uk;
   alter table states rename constraint states_cuk to states_uk;

   
   
4. Run the Upgrade Geodatabase tool.