IXP DWS Nightly Job Stop At Compression With Error ORA-14097

Asset ID:	1-72-2248690.1
Update Date:	2017-11-12
Keywords:

Solution Type Problem Resolution Sure

Solution 2248690.1 : IXP DWS Nightly Job Stop At Compression With Error ORA-14097

Applies to:

Oracle Communications Performance Intelligence Center (PIC) Software - Version 10.1.0 and later
Information in this document applies to any platform.

Symptoms

Nightly job log is getting ORA-14097 errors during the IXP_COMPRESSION job:

31/01/2017 07:07:17 DEBUG Table TMP_P1485828000 dropped
31/01/2017 07:07:17 DEBUG Table name=MAP partition name=P1485828000
31/01/2017 07:07:17 DEBUG Temporary table name: TMP_P1485828000
31/01/2017 07:07:17 DEBUG DROP TABLE TMP_P1485828000 PURGE
31/01/2017 07:07:17 DEBUG Temporary table TMP_P1485828000 does not exist
31/01/2017 07:07:17 DEBUG Temporary table TMP_P1485828000 created and data copied
31/01/2017 07:07:17 DEBUG Start gather stats for temporary table TMP_P1485828000
31/01/2017 07:07:17 DEBUG Stats gathered for temporary table TMP_P1485828000
31/01/2017 07:07:17 DEBUG Index T_17566_7 created on table TMP_P1485828000 column IMEI_SV_
31/01/2017 07:07:17 DEBUG Index T_17566_6 created on table TMP_P1485828000 column MSISDN_
31/01/2017 07:07:17 DEBUG Index T_TIMETAG_17566 created on table TMP_P1485828000 column TIMETAG
31/01/2017 07:07:17 DEBUG Index T_17566_2 created on table TMP_P1485828000 column STARTDATE_
31/01/2017 07:07:17 DEBUG Index T_17566_3 created on table TMP_P1485828000 column IPSOURCE_
31/01/2017 07:07:17 DEBUG Index T_17566_4 created on table TMP_P1485828000 column IPDESTINATION_
31/01/2017 07:07:17 DEBUG Index T_17566_5 created on table TMP_P1485828000 column IMSI_
31/01/2017 07:07:17 ERROR EXCEPTION (-14097) ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
31/01/2017 07:07:17 DEBUG Table TMP_P1485828000 dropped

Cause

Compression was enabled on session and some columns are marked as unused

SQL> select * from DBA_UNUSED_COL_TABS where TABLE_NAME='MAP';

OWNER TABLE_NAME COUNT
------------------------- ------------------------- -----
IXP MAP 2

actually IXP_DROP_UNUSED_COLS is supposed to drop these column but it failled.

If alter table is run manually, ORA-39726 is raised:

SQL> alter table MAP drop unused columns;
alter table MAP drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

More explanation in ORA-39726: Clarification on error when Drop Unused Column does not help (Doc ID 1574318.1)

Solution

Procedure:

Connect on IXP and stop store process:
pm.set off IXP_XXXXX
Connect on DWS db
Remove table:
drop table;
Start store:
pm.set on IXP_XXXX

If it is required to keep history, it is possible to create a new session and only drop it, when new session has enough historical data.

References

<NOTE:1574318.1> - ORA-39726: Clarification on error when Drop Unused Column does not help

Attachments

This solution has no attachment