This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.
Remedy AR System Server
Remedy AR System Server v9.1.x
Attachment data failed to fetch from user tool or from Mid-Tier and after saving attachment on machine it shows 0KB in size.
Convert all LONG RAW data type to BLOB in order to resolve this issue.
1- Run below SQL commands :
select table_name, column_name,data_type from USER_TAB_COLUMNS where DATA_TYPE='LONG RAW' and owner = 'ARADMIN';
select table_name, column_name,data_type from DBA_TAB_COLUMNS where DATA_TYPE='LONG RAW' and owner = 'ARADMIN';
2- The outcome will give a list like below for all those B tables and field id's which you need to convert to BLOB.
B380C711070317 C711070317 LONG RAW
B380C711070318 C711070318 LONG RAW
B380C711070319 C711070319 LONG RAW
3- Then execute below command to convert LONG RAW to BLOB for each entry provided as per the previous step(s).
ALTER TABLE B380C711070317 modify(C711070317 BLOB);
ALTER TABLE B380C711070318 modify(C711070318 BLOB);
ALTER TABLE B380C711070319 modify(C711070319 BLOB);
4- Then re-login to Mid-Tier or from User tool and try to fetch the attachment.
1) If you run alter table on JOIN form it will give you ORA error which you can IGNORE.
2) While attaching new attachment on pool if it shows below error then we need to drop the indexes and we need to rebuild again.
ARERR  The SQL database operation failed. : ORA-01502: index 'ARADMIN.IB380C711070319' or partition of such index is in unusable state
drop index ARADMIN.IB380C711070319;