11 Replies Latest reply on May 14, 2020 10:40 AM by Stefan Hall

    How to find differences in field lengths between database and metadata (oracle)?

    Stefan Hall
      Share This:

      Hello, experts,

      a little puzzle game at the beginning of the week to really get going.I think I'm pretty far along, but I can't think of the last step.

       

      Problem

      It is known that Oracle cannot make changes to field lengths if functional indexes exist at the same time. The BMC installers override this and update the metadata, even though the field length in the DB could not be adjusted. This is not as bad as it sounds, because otherwise every upgrade attempt would have failed

      ITSM upgrade to 19.02 error SQL Execution Failed: ORA-30556: either functional or bitmap join index is defined on the column to be modified.

       

      But how do I find all fields in a system that have not been updated in the meantime?

       

      Solution approach

      I compare the metadata on DB level with the field lengths on DB level. My expectation was that I would find no deviations in a fresh OOB system without functional indexes and only a few, in our real systems. Unfortunately this is not so ...

       

      With the following SQL I find about 2117 differences in a freshly installed OOB system (20.02).

       

      select distinct  MFC.schemaid, ARS.name, MFC.fieldid, MF.fieldname, MFC.maxlength Meta_Length, UC.char_length DB_Length 
      from field_char MFC, arschema ARS, USER_TAB_COLUMNS UC, field MF
      where
          MFC.schemaid = ARS.schemaid
      and MF.schemaid = ARS.schemaid
      
      and UC.table_name = 'T' || MFC.schemaid
      and UC.column_name = 'C' || MFC.fieldid
      and MFC.Maxlength != UC.char_length
      and mfc.fieldid = mf.fieldid
      order by 2,3;
      

       

      Many of them seem explainable, even if I don't understand the many different field lengths for 'Request ID'. But what about the following

      Schemaid     Name                   FieldID             Fieldname         Meta_Length       DB_Length

      2920              HPD:WorkLog      1000000000     Description          100                        400

      4071              MYIT:Questions    303816300       minLabel              255                       1020

      1194              SYS:Menu Items   1000001809     Menu Value 2       30                         120

      and many, many more, I'm not sure if I can append the list here

      Question

      • Is there even a chance to get this information out of one(!) system cleanly?
      • What have I forgotten in SQL?
      • How would you search and find this kind of error?

       

      Let's go, who answers first?

      Stefan