Error while importing the def file: ERROR *** ORA-01430: column being added already exists in table - Remedy

Knowledge Article

Article Number

000300524

Old Article Number

000130855

Article Type

Solutions to a Product Problem

Title

Error while importing the def file: ERROR *** ORA-01430: column being added already exists in table - Remedy - AR System Server

Summary

Error-while-importing-the-def-file ERROR *** ORA-01430: column being added already exists in table

Product

Remedy AR System Server

Component

AR System

Applies to

Remedy 9.x

Problem

Scenario 1:

Importing a .def file of an overlay the following error is observed:

SQL logging:

*/ALTER TABLE H1377 ADD( T1000 number(15,0) NULL , U1000 varchar(254) NULL)
*/*** ERROR ***  ORA-01430: column being added already exists in table
*/ORA-01430: column being added already exists in table
*/ALTER TABLE H1377 ADD( T2000 number(15,0) NULL , U2000 varchar(254) NULL)
*/*** ERROR ***  ORA-01430: column being added already exists in table

Scenario 2:

The upgrade from 9.1.04 to 2002 completed with warnings and did not import the SRM custom forms
While importing the form via Developer studio, found the import was failing.
Error in SQL logs:

org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [ALTER TABLE T1731 ADD (C303467600USD NUMBER(28, 2))]; SQL state [72000]; error code [1430]; ORA-01430: column being added already exists in table
; nested exception is java.sql.SQLException: ORA-01430: column being added already exists in table

ALTER TABLE T1731 ADD (C303467600USD NUMBER(28, 2))
ALTER TABLE T2809 ADD (C303489300USD NUMBER(28, 2))

Cause

When deleting the overlay from Developer studio, the custom field columns were not deleted from the corresponding fields leading to the error.

Solution

This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.

Scenario 1:

Note the below article if the error is displayed when adding a field to a custom form in DevStudio:
Remedy - Server - Error:"ORA-01430: column being added already exists in table" trying to add a new field to a form

The approach is to delete the overlay completely from the database.

1. Follow the below articles to delete the overlay from the database, however do not execute the below query:
Delete from field where schemaId=<overlayschemaId>;

Depending the version installed select one of the below:
Remedy - Server - ARS 8.1.x How to delete an Overlay Object from the Database
Remedy - Server - ARS 9.x How to delete an Overlay Object from the Database

After following the above, proceed below.

2. Find out the field Id's of custom fields on the form.
SELECT fieldId FROM field WHERE schemaId=<overlayschemaId> AND overlayProp=4;

3. Drop the columns from T, B, H table
ALTER TABLE T<overlayschemaId>
DROP COLUMN <fieldId>;

ALTER TABLE B<overlayschemaId>
DROP COLUMN <fieldId>;

ALTER TABLE H<overlayschemaId>
DROP COLUMN <fieldId>;

4. Delete the attachment table related to the custom field
DROP TABLE B<overlayschemaId>C<fieldId>;

5. Execute the below query:
Delete from field where schemaId=<overlayschemaId>;

6. Restart and server Import the form again.

Scenario 2:

> Investigated further via db checker utility and found the metadata inconsistency.
> Refer document for https://docs.bmc.com/docs/display/ars91/Running+the+database+consistency+checker

Error snippet from checkdb.log:

<RPC ID: 0000000000> <Queue: Admin > <Client-RPC: 390600 > <USER: > <Overlay-Group: 0 > /* Tue Jul 28 2020 11:39:26.6300 */ Found redundant columns in table T1731 {C303467600GBP=Column [name=C303467600GBP; type=NUMERIC], C303489300GBP=Column [name=C303489300GBP; type=NUMERIC], C303489300JPY=Column [name=C303489300JPY; type=NUMERIC], C303467600JPY=Column [name=C303467600JPY; type=INTEGER], C303467600EUR=Column [name=C303467600EUR; type=NUMERIC], C303489300CAD=Column [name=C303489300CAD; type=NUMERIC], C303489300EUR=Column [name=C303489300EUR; type=NUMERIC], C303467600CAD=Column [name=C303467600CAD; type=NUMERIC], C303489300USD=Column [name=C303489300USD; type=NUMERIC], C303467600USD=Column [name=C303467600USD; type=NUMERIC]}


<RPC ID: 0000000000> <Queue: Admin > <Client-RPC: 390600 > <USER: > <Overlay-Group: 0 > /* Tue Jul 28 2020 11:39:56.8680 */ Found redundant columns in table T2809 {C303489300CAD=Column [name=C303489300CAD; type=NUMERIC], C303489300EUR=Column [name=C303489300EUR; type=NUMERIC], C303489300USD=Column [name=C303489300USD; type=NUMERIC], C303489300GBP=Column [name=C303489300GBP; type=NUMERIC], C303489300JPY=Column [name=C303489300JPY; type=NUMERIC]}

> Drop redundant columns from respective database tables and restart AR services.
> Import the form successfully via Developer studio.

Note: Remedy will recreate the dropped columns.

Attachment(s):