Installing ILM in an Oracle database: ALTER TABLE EXCHANGE PARTITION
Article ID: 87600
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
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: 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.