ORA-01455 error in program SMIGR_CREATE_DD
On oracle database, while running the program SMIGR_CREATE_DD we get an error likes
- Error while creating DDSTORAGE entries for table <TABLE_NAME>
- Failed to create storage parameters for table: <TABLE_NAME>
Also, ORA-01455 errors occurs in SM21:
ORA-01455: converting column overflows integer datatype
We can also check if there are large numbers (>2147483647) in columns INSERTS, UPDATES, DELETES in table DBA_TAB_MODIFICATIONS for these tables.
SQL> select inserts, updates, deletes from DBA_TAB_MODIFICATIONS where table_name = '<TABLE_NAME>';
Regenerating the Issue
The error can be regenerated in BW tables while running SMIGR_CREATE_DDL as part of a migration process.
Solution
This issue is caused mainly because of outdated statistics. The program tries to store very big number which comes from DBA_TAB_MODIFICATIONS.
Therefore to resolve this issue please calculating the statistics of the affected tables in DB20 with 0.1 % sample which will reset the values for DBA_TAB_MODIFICATIONS.
Please follow this Instruction
1) First, go to DB20
2) Now please enter the table name and click on the refresh button.
As shown below in the case of BW table the name of table needs to be started with a slash
3)Now Enter the following values
- New method: E
- New Sample Size: P.1
And click the Create Statistics button:
4) Now click 'Yes' in the popup window click in order to trigger a BRCONNECT Statistic Update
5) Now please click on "DBA Operations" -> BRCONNECT and check if the corresponding job finished.
6) Now at last start over the SMIGR_CREATE_DDL run.