Automic and Oracle schemes created before 11gR2

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

When an Automic (DB schema) was loaded to an Oracle release before 11g2 - all tables with 'INTEGER' and 'SMALLINTEGER' colums were created within Oracle (internal) as 'NUMBER(38, 0)'.

With Oracle 11gR2 this was changed and all new created tables using this types of columns are now created as 'NUMBER(*, 0)'.

 

Cause

ILM can not be turned on or a create/exchange partition is failing with 'ORA-14097' 

Environment

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

Resolution

You have to run this SQL against your Automic DB on 11gR2 and execute the result against the Automic DB.

select 'alter table ' || table_name || ' modify ' || column_name || ' number(*, 0);' as "Run this SQLs on Automic DB!" from user_tab_columns where data_type = 'NUMBER' and DATA_PRECISION = 38 and DATA_SCALE = 0;