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
1.03k stars 341 forks source link

Choose the oracle schema to be exported. #1353

Closed joguess closed 2 years ago

joguess commented 2 years ago

Hello

Actually if we choose EXPORT_SCHEMA 1 and CREATE_SCHEMA 1

ora2pg will run a big sql query where username are "not in" the system users and those we have defined :

SELECT A.TABLE_NAME,A.COMMENTS,A.TABLE_TYPE,A.OWNER 
FROM ALL_TAB_COMMENTS A, ALL_OBJECTS O WHERE A.OWNER=O.OWNER and A.TABLE_NAME=O.OBJECT_NAME and O.OBJECT_TYPE='TABLE' AND A.OWNER **NOT IN** 
('PUBLIC','AUDSYS','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')  AND (A.OWNER, A.TABLE_NAME) **NOT IN** 
(SELECT OWNER, MVIEW_NAME FROM ALL_MVIEWS UNION ALL SELECT LOG_OWNER, LOG_TABLE FROM ALL_MVIEW_LOGS) AND 
(A.OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, TABLE_NAME FROM ALL_OBJECT_TABLES) AND (  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p1)
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p2) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p3) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p4) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p5) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p6) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p7) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p8) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p9) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p10) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p11) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p12) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p13) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p14) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p15) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p16) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p17) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p18) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p19) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p20) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p21) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p22) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p23) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p24) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p25) 
 AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p26) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p27) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p28) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p29) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p30) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p31) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p32) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p33) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p34) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p35) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p36) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p37) 
 AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p38) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p39) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p40) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p41) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p42) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p43) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p44) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p45) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p46) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p47) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p48) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p49) 
 AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p50) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p51) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p52) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p53) ANDNOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p54) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p55) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p56) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p57) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p58) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p59) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p60) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p61) 
 AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p62) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p63) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p64) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p65) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p66) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p67) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p68) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p69) ANDNOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p70) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p71) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p72) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p73) 
 AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p74) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p75) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p76) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p77) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p78) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p79) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p80) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p81) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p82) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p83) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p84) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p85) 
 AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p86) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p87) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p88) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p89) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p90) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p91) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p92) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p93) AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p94) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p95) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p96) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p97) 
 AND NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p98) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p99) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p100) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p101) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p102) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p103) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABL E_NAME), :p104) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p105) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p106) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p107) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p108) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p109) 
 AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p110));

I would prefer that ora2pg would run a "in" query and we would have chosen the user we want to export (and not those that we don't want).

By the way , if we put the name of the oracle schema that we want to export like this :

--Export Oracle schema to PostgreSQL schema EXPORT_SCHEMA 0

--Oracle schema/owner to use SCHEMA toto titi tata

will ora2pg export the schemas toto titi tata ??

Thanks. I think this point is not clear.

J.

joguess commented 2 years ago

OK It won't work because it gives me wrong syntax :

SELECT A.TABLE_NAME, A.PARTITION_NAME, A.SUBPARTITION_NAME, A.SUBPARTITION_POSITION, B.SUBPARTITIONING_TYPE, A.TABLE_OWNER, B.PARTITION_COUNT FROM DBA_TAB_SUBPARTITIONS A, DBA_PART_TABLES B WHERE A.TABLE_NAME = B.TABLE_NAME AND (B.SUBPARTITIONING_TYPE = 'RANGE' OR B.SUBPARTITIONING_TYPE = 'LIST' OR B.SUBPARTITIONING_TYPE = 'HASH') AND (  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p1) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p2) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p3) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p4) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p5) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p6) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p7) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p8) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p9) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p10) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p11) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p12) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p13) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p14) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p15) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p16) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p17) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p18) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p19) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p20) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p21) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p22) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p23) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p24) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p25) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p26) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p27) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p28) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p29) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p30) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p31) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p32) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p33) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p34) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p35) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p36) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p37) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p38) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p39) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p40) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p41) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p42) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p43) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p44) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p45) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p46) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p47) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p48) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p49) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p50) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p51) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p52) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p53) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p54) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p55) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p56) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p57) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p58) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p59) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p60) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p61) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p62) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p63) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p64) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p65) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p66) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p67) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p68) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p69) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p70) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p71) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p72) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p73) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p74) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p75) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p76) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p77) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p78) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p79) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p80) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p81) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p82) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p83) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p84) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p85) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p86) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p87) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p88) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p89) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p90) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p91) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p92) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p93) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p94) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p95) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p96) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p97) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p98) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p99) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p100) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p101) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p102) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p103) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p104) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p105) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p106) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p107) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p108) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p109) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p110))   AND A.TABLE_OWNER ='TOTO TITI TATA' AND B.OWNER=A.TABLE_OWNER
 AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, TABLE_NAME FROM DBA_OBJECT_TABLES) AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, MVIEW_NAME FROM DBA_MVIEWS UNION ALL SELECT LOG_OWNER, LOG_TABLE FROM DBA_MVIEW_LOGS)ORDER BY A.TABLE_OWNER,A.TABLE_NAME,A.PARTITION_NAME,A.SUBPARTITION_POSITION
SELECT A.TABLE_NAME, A.PARTITION_NAME, A.SUBPARTITION_NAME, A.SUBPARTITION_POSITION, B.SUBPARTITIONING_TYPE, A.TABLE_OWNER, B.PARTITION_COUNT FROM DBA_TAB_SUBPARTITIONS A, DBA_PART_TABLES B WHERE A.TABLE_NAME = B.TABLE_NAME AND (B.SUBPARTITIONING_TYPE = 'RANGE' OR B.SUBPARTITIONING_TYPE = 'LIST' OR B.SUBPARTITIONING_TYPE = 'HASH') AND (  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p1) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p2) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p3) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p4) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p5) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p6) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p7) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p8) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p9) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p10) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p11) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p12) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p13) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p14) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p15) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p16) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p17) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p18) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p19) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p20) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p21) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p22) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p23) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p24) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p25) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p26) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p27) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p28) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p29) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p30) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p31) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p32) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p33) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p34) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p35) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p36) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p37) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p38) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p39) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p40) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p41) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p42) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p43) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p44) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p45) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p46) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p47) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p48) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p49) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p50) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p51) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p52) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p53) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p54) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p55) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p56) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p57) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p58) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p59) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p60) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p61) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p62) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p63) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p64) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p65) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p66) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p67) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p68) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p69) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p70) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p71) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p72) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p73) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p74) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p75) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p76) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p77) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p78) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p79) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p80) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p81) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p82) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p83) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p84) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p85) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p86) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p87) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p88) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p89) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p90) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p91) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p92) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p93) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p94) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p95) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p96) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p97) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p98) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p99) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p100) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p101) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p102) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p103) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p104) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p105) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p106) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p107) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p108) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p109) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p110))   AND A.TABLE_OWNER ='TOTO TITI TATA' AND B.OWNER=A.TABLE_OWNER
 AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, TABLE_NAME FROM DBA_OBJECT_TABLES) AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, MVIEW_NAME FROM DBA_MVIEWS UNION ALL SELECT LOG_OWNER, LOG_TABLE FROM DBA_MVIEW_LOGS)ORDER BY A.TABLE_OWNER,A.TABLE_NAME,A.PARTITION_NAME,A.SUBPARTITION_POSITION

How can I modify the code to have what I expect ?

I would like to have TABLE_OWNER IN like this: 

SELECT A.TABLE_NAME, A.PARTITION_NAME, A.SUBPARTITION_NAME, A.SUBPARTITION_POSITION, B.SUBPARTITIONING_TYPE, A.TABLE_OWNER, B.PARTITION_COUNT FROM DBA_TAB_SUBPARTITIONS A, DBA_PART_TABLES B WHERE A.TABLE_NAME = B.TABLE_NAME AND (B.SUBPARTITIONING_TYPE = 'RANGE' OR B.SUBPARTITIONING_TYPE = 'LIST' OR B.SUBPARTITIONING_TYPE = 'HASH') AND (  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p1) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p2) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p3) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p4) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p5) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p6) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p7) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p8) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p9) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p10) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p11) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p12) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p13) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p14) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p15) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p16) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p17) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p18) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p19) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p20) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p21) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p22) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p23) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p24) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p25) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p26) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p27) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p28) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p29) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p30) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p31) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p32) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p33) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p34) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p35) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p36) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p37) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p38) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p39) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p40) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p41) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p42) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p43) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p44) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p45) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p46) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p47) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p48) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p49) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p50) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p51) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p52) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p53) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p54) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p55) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p56) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p57) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p58) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p59) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p60) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p61) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p62) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p63) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p64) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p65) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p66) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p67) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p68) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p69) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p70) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p71) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p72) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p73) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p74) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p75) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p76) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p77) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p78) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p79) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p80) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p81) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p82) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p83) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p84) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p85) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p86) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p87) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p88) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p89) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p90) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p91) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p92) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p93) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p94) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p95) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p96) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p97) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p98) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p99) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p100) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p101) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p102) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p103) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p104) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p105) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p106) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p107) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p108) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p109) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p110))   AND A.TABLE_OWNER IN ('TOTO','TITI','TATA') AND B.OWNER=A.TABLE_OWNER
 AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, TABLE_NAME FROM DBA_OBJECT_TABLES) AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, MVIEW_NAME FROM DBA_MVIEWS UNION ALL SELECT LOG_OWNER, LOG_TABLE FROM DBA_MVIEW_LOGS)ORDER BY A.TABLE_OWNER,A.TABLE_NAME,A.PARTITION_NAME,A.SUBPARTITION_POSITION
SELECT A.TABLE_NAME, A.PARTITION_NAME, A.SUBPARTITION_NAME, A.SUBPARTITION_POSITION, B.SUBPARTITIONING_TYPE, A.TABLE_OWNER, B.PARTITION_COUNT FROM DBA_TAB_SUBPARTITIONS A, DBA_PART_TABLES B WHERE A.TABLE_NAME = B.TABLE_NAME AND (B.SUBPARTITIONING_TYPE = 'RANGE' OR B.SUBPARTITIONING_TYPE = 'LIST' OR B.SUBPARTITIONING_TYPE = 'HASH') AND (  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p1) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p2) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p3) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p4) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p5) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p6) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p7) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p8) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p9) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p10) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p11) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p12) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p13) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p14) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p15) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p16) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p17) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p18) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p19) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p20) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p21) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p22) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p23) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p24) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p25) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p26) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p27) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p28) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p29) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p30) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p31) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p32) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p33) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p34) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p35) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p36) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p37) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p38) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p39) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p40) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p41) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p42) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p43) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p44) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p45) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p46) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p47) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p48) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p49) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p50) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p51) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p52) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p53) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p54) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p55) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p56) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p57) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p58) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p59) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p60) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p61) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p62) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p63) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p64) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p65) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p66) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p67) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p68) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p69) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p70) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p71) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p72) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p73) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p74) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p75) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p76) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p77) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p78) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p79) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p80) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p81) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p82) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p83) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p84) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p85) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p86) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p87) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p88) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p89) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p90) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p91) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p92) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p93) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p94) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p95) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p96) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p97) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p98) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p99) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p100) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p101) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p102) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p103) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p104) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p105) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p106) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p107) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p108) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p109) AND  NOT REGEXP_LIKE(UPPER(A.TABLE_NAME), :p110))   AND A.TABLE_OWNER IN ('TOTO','TITI','TATA') AND B.OWNER=A.TABLE_OWNER
 AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, TABLE_NAME FROM DBA_OBJECT_TABLES) AND (A.TABLE_OWNER, A.TABLE_NAME) NOT IN (SELECT OWNER, MVIEW_NAME FROM DBA_MVIEWS UNION ALL SELECT LOG_OWNER, LOG_TABLE FROM DBA_MVIEW_LOGS)ORDER BY A.TABLE_OWNER,A.TABLE_NAME,A.PARTITION_NAME,A.SUBPARTITION_POSITION
darold commented 2 years ago

Configuration directive SCHEMA only supports a single schema name. If you want to export multiple schema then proceed to multiple ora2pg project, one by schema.

joguess commented 2 years ago

wow ok. I modified the export_schema.ksh and I added a list of schema, I put it inside a second loop for. I can give you the code if you want it. Thanks.

darold commented 2 years ago

you can always post the script here, it could help someone that is looking for the same thing. Thanks.

joguess commented 2 years ago 
#!/bin/sh
#-------------------------------------------------------------------------------
#
# Created by joguess the Oracle database admin, version 2022
#
#-------------------------------------------------------------------------------
SOURCE_TYPE="PACKAGE VIEW TRIGGER FUNCTION PROCEDURE PARTITION TYPE MVIEW"
EXPORT_TYPE="TABLE PACKAGE VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE PARTITION TYPE MVIEW DBLINK SYNONYM DIRECTORY"
SCHEMA="TITI TOTO TATA"

namespace="."
unit_cost=5

ora2pg -t SHOW_TABLE -c $namespace/config/ora2pg.conf > $namespace/reports/tables.txt
ora2pg -t SHOW_COLUMN -c $namespace/config/ora2pg.conf > $namespace/reports/columns.txt
ora2pg -t SHOW_REPORT -c $namespace/config/ora2pg.conf --dump_as_html --estimate_cost > $namespace/reports/report.html

for etype in $(echo $EXPORT_TYPE | tr " " "\n")
do
        for eschema in $(echo $SCHEMA | tr " " "\n")
        do
        ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
        ltype=`echo $ltype | sed 's/y$/ie/'`
        echo "Running: ora2pg -p -t $etype -o $ltype$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf"
        ora2pg -p -t $etype -o $ltype$eschema.sql -n $eschema -b $namespace/schema/${ltype}s -c $namespace/config/ora2pg.conf
        ret=`grep "Nothing found" $namespace/schema/${ltype}s/$ltype.sql 2> /dev/null`
        if [ ! -z "$ret" ]; then
                rm $namespace/schema/${ltype}s/$ltype.sql
        fi
        done
done

for etype in $(echo $SOURCE_TYPE | tr " " "\n")
do
        for eschema in $(echo $SCHEMA | tr " " "\n")
        do
        ltype=`echo $etype | tr '[:upper:]' '[:lower:]'`
        ltype=`echo $ltype | sed 's/y$/ie/'`
        echo "Running: ora2pg -t $etype -o $ltype$eschema.sql -n $eschema -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf"
        ora2pg -t $etype -o $ltype$eschema.sql -n $eschema -b $namespace/sources/${ltype}s -c $namespace/config/ora2pg.conf
        ret=`grep "Nothing found" $namespace/sources/${ltype}s/$ltype.sql 2> /dev/null`
        if [ ! -z "$ret" ]; then
                rm $namespace/sources/${ltype}s/$ltype.sql
        fi
        done
done

echo
echo
echo "To extract data use the following command:"
echo
echo "ora2pg -t COPY -o data.sql -b $namespace/data -c $namespace/config/ora2pg.conf"
echo

exit 0