Getting db error during upgrade

Document ID : KB000005737
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

While running database upgrade using hdbsetup, getting the following error:

2017/02/13 16:23:35 **** UPGRADE TO r12.6 COMPLETE ****
2017/02/13 16:23:35 **** UPGRADE TO v13.0 STARTED ****
2017/02/13 17:00:09 E03020003: Database error [CA Harvest SCM][ODBC 20101 driver][Oracle]ORA-32793: cannot alter a system-generated sequence
SQLSTATE=60 .

Environment:
DB is Oracle 12cUpgrading from SCM v12.5 to SCM v13.0
Cause:

There appears a table in Oracle 12c in Harvest schema which utilizes the system generated sequence.  The "Identity clause" is a new feature in Oracle 12c which automatically creates a sequence for column.

For example,

SQL> CREATE TABLE TEST_TABLE (ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY, TEXT VARCHAR2(100));
Table created.

When we insert into the table the ID column is automatically populated.

SQL> insert into test_table (text) values ('aaa');
1 row created.

SQL>commit;
Commit complete.

SQL> select * from test_table;
     ID TEXT
------- ----------
      1 aaa

When we created the table a sequence was also created.

SQL> select object_name, object_type from dba_objects where owner = 'TEST';
OBJECT_NAME     OBJECT_TYPE
--------------- --------------------------------------------
SYS_C009854     INDEX
ISEQ$$_91602    SEQUENCE
TEST_TABLE      TABLE

When we drop the table the sequence is left behind, and cannot be manually deleted.

SQL> select object_name from dba_objects where owner = 'TEST';
OBJECT_NAME
--------------------------------------------------------------------------------
ISEQ$$_91602

SQL> drop sequence ISEQ$$_91602;
drop sequence ISEQ$$_91602
               *
ERROR at line 1:
ORA-32794: cannot drop a system-generated sequence

SQL> ! oerr ora 32794
32794,0000, "cannot drop a system-generated sequence"
// *Cause:  An attempt was made to drop a system-generated sequence.
// *Action: A system-generated sequence, such as one created for an
//          identity column, cannot be dropped.
Until ... the recyclebin is purged.  So the sequence is kept to allow the dropped table to be restored if needed.
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select object_name from dba_objects where owner = 'TEST';
no rows selected

Resolution:

Let’s first understand what is the table associated with the sequence "ISEQ$$_253150.

You can use the “select * from sys.idnseq$” query to fetch the object id of the table and use the dba_objects table to find the name of the table associated with the index.

Can you check to see if the table associated with this index can be dropped and the recycle bin can be purged before starting the upgrade to v13. Also, if it is not possible for you to drop the table, if you can back up the table and recreate it after the upgrade in the appropriate schema.

It is recommended to do this first on a test database to assure that it fixes the problem.

Additional Information:

As always, please contact CA Technologies support for CA Harvest Software Change Manager if you have further questions.