Installing ILM in an Oracle database: ALTER TABLE EXCHANGE PARTITION


Article ID: 87600


Updated On:


CA Automic Workload Automation - Automation Engine


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


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.


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 type:
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.


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


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.