Automic and Oracle schemes created before 11gR2

Document ID : KB000088277
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:

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' 

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;