Installing ILM in an Oracle database: ALTER TABLE EXCHANGE PARTITION

Article Id: 87600
Status: Published
Updated On: 16-06-2020 11:02
Legacy Id: 000008929
Products:
CA Automic Workload Automation - Automation Engine
Issue/Introduction:

Error Message :
U0003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR', ",'ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION'.

Description

Issues with installing ILM in an Oracle database

When you do an upgrade from 1 db to the next, it's not picking up the fact that the datatype is changed. In version 9, it was "integer" and now it's "number".
Database is saying that the datatype is different:
U0003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR', ",'ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION'
The ILM installation is performed using the utility AE.DB Load when loading initial data.

Solution

Many versions of SQL scripts and database files are provided on the delivery directory. You will find them in the subdirectory of IMAGE: DB.
If <vers> is specified, select the appropriate folder of the Automation Engine version that you are using. If there is no separate folder of your version, the database structure has not been changed since the last version. In this case, you can use the previous version.

To fix this issue you need the script DBM_number_ILM.sql. This script is delivered in IMAGE: DB\oracle.
 
1) Copy DBM_number_ILM.sql from IMAGE: DB\oracle to your defined directory (e.g.: c:\Automic\<vers>\Utility\db\oracle)
2) Execute script DBM_number_ILM.sql in SQL*Plus from the command line
 

 
Note: You will find additional information about SQL*Plus under following link SQL*Plus® User's Guide and Reference
 
3) Execute sql script DBM_number_ILM_do_it.sql which is created by the previous script
You will find this script in the same directory where the script DBM_number_ILM_do_it.sql has been executed
4) Do the upgrade
 
This needs to be done before ILM can be used - the column definition must match!

 

Cause:

Cause type:
Other
Root Cause: If you created your AE schema with version 9 SP6 or lower you will run in this issue.
The reason is that Oracle company changed the default value for number column's between oracle releases 9i/10g/11/12.

Environment:

Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager
Component:

Resolution:

This field was added on 30/03/2017. This article has not been updated yet. Refer to the "Description" or "Workaround" sections for solution information.

Additional Information:

Workaround :
You could get the DBA to do a full export and then create the schema new and then import.