Resolving The Problem

After restoring the Oracle database you need to recreate all predefined primary keys that do not use a default index.

First determine primary key columns for tables which do not have a default primary key index with a SQL command like the following:

SQL>select col.table_name, col.position, col.column_name, col.constraint_name, con.index_name from user_cons_columns col, user_constraints con where col.constraint_name like ('SYS_%') and col.constraint_name=con.constraint_name and con.constraint_type='P' and con.index_name not like ('SYS_%') order by col.table_name, col.position;

The result might look like this:

The query result shows the primary key columns along with the corresponding table, and the index which is used to enforce the constraint. In the example above the index TI_TI_TT_KND_ST enforces the constraint on table TASK_INSTANCE_T.

Next check existing indexes for a given table, for example for table TASK_INSTANCE_T, with the following SQL command:

SQL> select index_name, column_position, column_name from user_ind_columns where table_name='TASK_INSTANCE_T' order by index_name, column_position;

The result will show all indexed columns for the specified table, e.g.:

               

You can see that for example the index TI_TI_TT_KND_ST contains four columns in the following order : TKIID - TKTID - KIND - STATE.
Note that there is no index that contains only the column TKIID as that would fit the primary key constraint SYS_C0013920 from above.

Finally, you can modify the primary key constraint by either creating a new index on the primary key column if a dedicated index does not exist, e.g.

SQL> alter table task_instance_t modify primary key using index ( create index ti_pk on task_instance_t (tkiid) );

or by assigning an existing index if the dedicated index exists:

SQL> alter table task_instance_t modify primary key using index ti_tkiid;

The sample queries were run by the owner of the Business Process Choreographer database tables using Oracle SQL*Plus. If you connect as the owner of the Business Process Choreographer database tables, no additional database privileges are required. The results of the queries may be different from the results of the same queries running in another environment.