Troubleshooting Oracle Data Ingestion and Column Details Retrieval

troubleshoot
1

Original Slack Thread

Hi all.

Hoping to get some help with Oracle ingestion please.
My source is setup like below.

  type: oracle
  config:
    host_port: dhware1:1539
    database: WARE
    username: SRVBDIA
    password: **********
    env: "prod"
    schema_pattern:
      deny: ['.*[0-9]','.*q', 'p01.*', 'srv.*', 'anonymous', 'sys', 'system']```
Oracle DB get ingested fine, but it looks like there is no details wrt the columns in datahub.
Is there suppose to be? I'm looking at the page attached.
So far I haven't been able to find anything useful about it on the oracle source page of the documentation.

Profiling is off by default. I'm hoping we don't need to it just capture column name and types.

Thanks in advance.
Claude![attachment](https://files.slack.com/files-pri/TUMKD5EGJ-F061XMEM8EQ/image.png?t=xoxe-973659184562-6705490291811-6708051934148-dd1595bd5f63266bc09e6166373c7a3c)
2

<@U057MF67S1X> please, can you check the ingestion logs if you see any error there?

3

Sorry, should have thought to upload ingestion logs. Will get it for you

4

I see I get quite a few “missing column information” warning.attachment

5

<@UV14447EU> Have you perhaps seen above?

6

Can you try one of the query above and check if it returns columns?

                col.column_name,
                col.data_type,
                col.char_length,
                col.data_precision,
                col.data_scale,
                col.nullable,
                col.data_default,
                com.comments,
                col.virtual_column,
                                col.default_on_null,
                (
                    SELECT id.generation_type || ',' || id.IDENTITY_OPTIONS
                    FROM ALL_TAB_IDENTITY_COLS id
                    WHERE col.table_name = id.table_name
                    AND col.column_name = id.column_name
                    AND col.owner = id.owner
                ) AS identity_options
            FROM all_tab_cols col
            LEFT JOIN all_col_comments com
            ON col.table_name = com.table_name
            AND col.column_name = com.column_name
            AND col.owner = com.owner
            WHERE col.table_name = CAST(:table_name AS VARCHAR2(128))
            AND col.hidden_column = 'NO'
         AND col.owner = :owner  ORDER BY col.column_id```
7

It seems like sqlalchemy runs that query to get columns

8

Interesting. It isn’t returning any lines. Going to see if it might be something with the user or a permissions issue.I also did some other select queries against the table to see if there are any entries for the SCHEMA and couldn’t find anything. Thanks a lot

9

please, keep me updated

10

OK, so I have found the problem.

https://docs.oracle.com/database/121/REFRN/GUID-85036F42-140A-406B-BE11-0AC49A00DBA3.htm#REFRN20276

If you read the above link, you will find out that…
ALL_TAB_COLS describes the columns of the tables, views, and clusters accessible to the current user.

To read column information for all users, we need to access DBA_TAB_COLS and DBA_COL_COMMENTS

Would you know if there was a reason for why ALL_TAB_COLS is used instead of DBA_TAB_COLS ?

Thanks for your assistance

11

Apologies. I see it is sqlalcamy that is using ALL_*

12

Hello <@U057MF67S1X>, <@UV14447EU> I’m facing exactly the same problem. Were you able to fix this and get columns information ingested as well? Was this just granting the access?