search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

Trouble Exporting Table if User Has Access to More Than One Table with Same Name #1734

Closed rvanouter closed 4 months ago

rvanouter commented 4 months ago

It seems one part of the utility gets the columns from the user's table, but another part uses ALL_TABLE_COLUMNS and can find a table owned by another user when the table names are the same. This is definitely a problem when both tables do not have the same columns. When I revoked access to the other user's table the utility runs fine. Not looking forward to revoking the select in production to migrate the data.

FATAL: _extract_data() ORA-00904: "COLUMN_NAME": invalid identifier (DBD ERROR: error possibly near <*>

SELECT A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH, A.NULLABLE, A.DATA_DEFAULT, A.DATA_PRECISION, A.DATA_SCALE, A.CHAR_LENGTH, A.TABLE_NAME, A.OWNER FROM ALL_TAB_COLUMNS A WHERE A.TABLE_NAME NOT LIKE 'BIN$%' AND A.OWNER NOT IN ('SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000','FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT','SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200','DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF','AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS','OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN','AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED','DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER') AND (REGEXP_LIKE(UPPER(A.TABLE_NAME), ?)) ORDER BY A.COLUMN_ID

darold commented 4 months ago

Commits 7412b54 and fc7008c fix this issue.

rvanouter commented 4 months ago

Thanks for the quick turnaround!

Scott

On Sun, Feb 18, 2024 at 1:09 PM Gilles Darold @.***> wrote:

Commits 7412b54 https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-a7ae44270bdee903&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fdarold%2Fora2pg%2Fcommit%2F7412b547526dab7461c5494776413c54326ee682 and fc7008c https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-013b3ca1d731b712&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fdarold%2Fora2pg%2Fcommit%2Ffc7008c7b6e8c8e01c895b394be78492cb5d9da1 fixes this issue.

— Reply to this email directly, view it on GitHub https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-71596d374221c883&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fdarold%2Fora2pg%2Fissues%2F1734%23issuecomment-1951402830, or unsubscribe https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-bf1bcf04ff6c7674&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FBGF34C45BAM64JT7WUQPRUTYUI7T3AVCNFSM6AAAAABDKSXSRGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNJRGQYDEOBTGA . You are receiving this because you authored the thread.Message ID: @.***>

--

Robert Scott van Outer

Oracle DBA IV

9901 Linn Station Road / Louisville, KY 40223 USA

t +1 502-815-3870 · m +1 502-727-1513

@.***

Website https://protect2.fireeye.com/v1/url?k=de90108d-810b2850-de903a5b-8697f3294d21-7ff5ce7cf8335067&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fapprissinsights.com%252F%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757131085%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3DlOgjcfNVEzKTFPi9nLtmnBlP9H26UvMfMbVwHJrOQDk%253D%26reserved%3D0 / LinkedIn https://protect2.fireeye.com/v1/url?k=7c8bd64e-2310ee93-7c8bfc98-8697f3294d21-42b3d4dff8eba322&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fwww.linkedin.com%252Fcompany%252F1313401%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757131085%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3Djjwd3Vt%252B10sUK%252BK0CD1%252FYtCD16vA%252BnjWrGSqNTfE95E%253D%26reserved%3D0 / Twitter https://protect2.fireeye.com/v1/url?k=a1d8b562-fe438dbf-a1d89fb4-8697f3294d21-e8146f613b3a1f4a&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Ftwitter.com%252FApprissInsights%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757141044%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3D91mD9E2vhQBFzet%252FjNdOkhhiwST4%252BAC14UlKV%252FeAEls%253D%26reserved%3D0 / Blog https://protect2.fireeye.com/v1/url?k=94cfa0a7-cb54987a-94cf8a71-8697f3294d21-113b2b6ff235adef&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fblog.apprissinsights.com%252F%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757141044%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3DYZ9U2idYrVHyvArD8kTRh69PRJPB5IJtAB%252BaD5iO%252FN8%253D%26reserved%3D0

-- This message contains proprietary information from Equifax which may be confidential. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e-mail @. @.>.

Equifax® is a registered trademark of Equifax Inc.  All rights reserved.

rvanouter commented 4 months ago

Thank you for trying to fix this bug. However, I am still having the same problem. The error generated is not the same as before, the DEBUG output no longer mentions ALL_TAB_COLUMNS. However the code is still picking up columns from a table owned by another schema/user in the Oracle database, not columns from the one the user owns. It seems the filter for the user is not working. I went to the Ora2Pg site and downloaded the ora2pg_master.zip. I assume that would have included the bug fix, but I'm not certain.

Thanks, Scott

On Sun, Feb 18, 2024 at 1:09 PM Gilles Darold @.***> wrote:

Commits 7412b54 https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-a7ae44270bdee903&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fdarold%2Fora2pg%2Fcommit%2F7412b547526dab7461c5494776413c54326ee682 and fc7008c https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-013b3ca1d731b712&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fdarold%2Fora2pg%2Fcommit%2Ffc7008c7b6e8c8e01c895b394be78492cb5d9da1 fixes this issue.

— Reply to this email directly, view it on GitHub https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-71596d374221c883&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fdarold%2Fora2pg%2Fissues%2F1734%23issuecomment-1951402830, or unsubscribe https://protect2.fireeye.com/v1/url?k=31323334-501cfaeb-313219e2-454455535732-bf1bcf04ff6c7674&q=1&e=550a66dd-7b4d-425f-9c39-e156d2df81e6&u=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FBGF34C45BAM64JT7WUQPRUTYUI7T3AVCNFSM6AAAAABDKSXSRGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNJRGQYDEOBTGA . You are receiving this because you authored the thread.Message ID: @.***>

--

Robert Scott van Outer

Oracle DBA IV

9901 Linn Station Road / Louisville, KY 40223 USA

t +1 502-815-3870 · m +1 502-727-1513

@.***

Website https://protect2.fireeye.com/v1/url?k=de90108d-810b2850-de903a5b-8697f3294d21-7ff5ce7cf8335067&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fapprissinsights.com%252F%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757131085%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3DlOgjcfNVEzKTFPi9nLtmnBlP9H26UvMfMbVwHJrOQDk%253D%26reserved%3D0 / LinkedIn https://protect2.fireeye.com/v1/url?k=7c8bd64e-2310ee93-7c8bfc98-8697f3294d21-42b3d4dff8eba322&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fwww.linkedin.com%252Fcompany%252F1313401%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757131085%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3Djjwd3Vt%252B10sUK%252BK0CD1%252FYtCD16vA%252BnjWrGSqNTfE95E%253D%26reserved%3D0 / Twitter https://protect2.fireeye.com/v1/url?k=a1d8b562-fe438dbf-a1d89fb4-8697f3294d21-e8146f613b3a1f4a&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Ftwitter.com%252FApprissInsights%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757141044%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3D91mD9E2vhQBFzet%252FjNdOkhhiwST4%252BAC14UlKV%252FeAEls%253D%26reserved%3D0 / Blog https://protect2.fireeye.com/v1/url?k=94cfa0a7-cb54987a-94cf8a71-8697f3294d21-113b2b6ff235adef&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fblog.apprissinsights.com%252F%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757141044%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3DYZ9U2idYrVHyvArD8kTRh69PRJPB5IJtAB%252BaD5iO%252FN8%253D%26reserved%3D0

-- This message contains proprietary information from Equifax which may be confidential. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e-mail @. @.>.

Equifax® is a registered trademark of Equifax Inc.  All rights reserved.

darold commented 4 months ago

You have not set the SCHEMA directive in ora2pg.conf, set it to the username will append this filter to all queries.

rvanouter commented 4 months ago

That did the trick. Sorry I didn't catch that myself

Thanks, Scott

On Fri, Feb 23, 2024 at 12:46 PM Gilles Darold @.***> wrote:

You have not set the SCHEMA directive in ora2pg.conf, set it to the username will append this filter to all queries.

— Reply to this email directly, view it on GitHub https://protect2.fireeye.com/v1/url?k=31323334-501d2dca-313219e2-454455534531-23b363ca9c0bfb71&q=1&e=8d5ddc39-d123-4e51-a5c6-a3172857a9b2&u=https%3A%2F%2Fgithub.com%2Fdarold%2Fora2pg%2Fissues%2F1734%23issuecomment-1961744533, or unsubscribe https://protect2.fireeye.com/v1/url?k=31323334-501d2dca-313219e2-454455534531-dfccd1ee780cef13&q=1&e=8d5ddc39-d123-4e51-a5c6-a3172857a9b2&u=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FBGF34C7AVRE52H5JPQ3KWLLYVDIVVAVCNFSM6AAAAABDKSXSRGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNRRG42DINJTGM . You are receiving this because you authored the thread.Message ID: @.***>

--

Robert Scott van Outer

Oracle DBA IV

9901 Linn Station Road / Louisville, KY 40223 USA

t +1 502-815-3870 · m +1 502-727-1513

@.***

Website https://protect2.fireeye.com/v1/url?k=de90108d-810b2850-de903a5b-8697f3294d21-7ff5ce7cf8335067&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fapprissinsights.com%252F%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757131085%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3DlOgjcfNVEzKTFPi9nLtmnBlP9H26UvMfMbVwHJrOQDk%253D%26reserved%3D0 / LinkedIn https://protect2.fireeye.com/v1/url?k=7c8bd64e-2310ee93-7c8bfc98-8697f3294d21-42b3d4dff8eba322&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fwww.linkedin.com%252Fcompany%252F1313401%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757131085%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3Djjwd3Vt%252B10sUK%252BK0CD1%252FYtCD16vA%252BnjWrGSqNTfE95E%253D%26reserved%3D0 / Twitter https://protect2.fireeye.com/v1/url?k=a1d8b562-fe438dbf-a1d89fb4-8697f3294d21-e8146f613b3a1f4a&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Ftwitter.com%252FApprissInsights%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757141044%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3D91mD9E2vhQBFzet%252FjNdOkhhiwST4%252BAC14UlKV%252FeAEls%253D%26reserved%3D0 / Blog https://protect2.fireeye.com/v1/url?k=94cfa0a7-cb54987a-94cf8a71-8697f3294d21-113b2b6ff235adef&q=1&e=1d11cce8-0060-4e26-96b6-dba702bc32ec&u=https%3A%2F%2Fnam12.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fblog.apprissinsights.com%252F%26data%3D04%257C01%257Csvanouter%2540appriss.com%257C1f9d7c69ac8d49ea503008d99efff3a8%257C9516b67944584fde9b673b7e4ecd2d2d%257C0%257C0%257C637715644757141044%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26sdata%3DYZ9U2idYrVHyvArD8kTRh69PRJPB5IJtAB%252BaD5iO%252FN8%253D%26reserved%3D0

-- This message contains proprietary information from Equifax which may be confidential. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e-mail @. @.>.

Equifax® is a registered trademark of Equifax Inc.  All rights reserved.