Issues with SHOW_TABLE not showing all tables in oracle

[mirec84]

hi,

I am using v23.2 and trying to migrate an Oracle DB to PostgreSQL and SHOW_TABLE is missing one table (the last one), all other 90 tables are shown correctly, but the one named 'V_STOFF_ZEUS_NEU' is missing.

Although, when I execute this command on the Oracle, it is part of the list:

Can someone help? (USER_GRANTS = 1)

[darold]

Hi,

Does this table is listed in ALL_OBJECTS and what is its type in this catalog table?

SELECT A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE FROM ALL_OBJECTS A WHERE A.OBJECT_NAME = 'V_STOFF_ZEUS_NEU';

It might not be a real table but related to materialized view, in this case the OBJECT_TYPE will not be TABLE.

[mirec84]

[mirec84]

There is materialized view with the same name. Maybe that is the reason?

[darold]

yes this is the reason

[mirec84]

but the table should be exported despite the existence of a materialized view right?

[darold]

right, give me few minutes.

[darold]

@mirec84 please give a try to latest development code, commit fc5c67c should fix this issue.

[darold]

Well I introduced a regression in the previous commit that is fixed with commit 6f3ffc1, please give it a try.

[mirec84]

I still do not see the V_STOFF_ZEUS_NEU table :( Nothing seem to be changed in the output

[darold]

Ok, I have reverted the lasts commits. What you are seeing is an Oracle internal table related to the materialized view, not a real table.

You can try to create a materialized view and execute query SELECT A.OWNER, A.OBJECT_NAME, A.OBJECT_TYPE FROM ALL_OBJECTS A WHERE A.OBJECT_NAME = '<MVIEW_NAME>'; you will see the two entries. Previous behavior of Ora2Pg was the right one. I should have recall that you can not create a table and a mview with the same name in Oracle.

[mirec84]

You are correct

[mirec84]

this is not an issue