0

I am using schema-crawler to crawl an oracle database (Retrieve Table/Synonym metadata including columns details and foreign keys)

INFO: 
-- generated by: SchemaCrawler 16.15.1
-- database: Oracle Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
-- driver: Oracle JDBC driver 19.3.0.0.0
-- JVM system: AdoptOpenJDK OpenJDK 64-Bit Server VM 1.8.0_292-b10

In my POM I have included the oracle plugin

      <groupId>us.fatehi</groupId>
      <artifactId>schemacrawler-oracle</artifactId>
      <version>${schemacrawler.version}</version>
    </dependency>

I have set the following in LimitOptionsBuilder & LoadOptions to crawl Schema:

limitOptionsBuilder.tableTypes("TABLE,VIEW,SYNONYM");
limitOptionsBuilder.includeAllSynonyms();
final SchemaCrawlerOptions options = SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions()
.withLimitOptions(limitOptionsBuilder.toOptions())
.withLoadOptions(loadOptionsBuilder.toOptions());
Catalog cat = SchemaCrawlerUtility.getCatalog(conn, options);

In the Catalog output, I don't see any SYNONYMS. I did some debugging and it seems that the the query that is sent to the database to get the tables is using DBA_TAB_COMMENTS, which unfortunately does not contain SYNONYM information. In oracle synonyms are stored in ALL_SYNONYMS

SELECT  
  NULL AS TABLE_CAT,
  TABLES.OWNER AS TABLE_SCHEM,
  TABLES.TABLE_NAME AS TABLE_NAME,
  TABLES.TABLE_TYPE AS TABLE_TYPE,
  TABLES.COMMENTS AS REMARKS
FROM 
  DBA_TAB_COMMENTS TABLES
WHERE
  TABLES.OWNER NOT IN 
    ('ANONYMOUS', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 
    'EXFSYS', 'FLOWS_30000', 'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'IX', 'LBACSYS', 
    'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORACLE_OCM', 
    'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'OWBSYS', 'PM', 'SCOTT', 'SH', 
    'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 
    'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST', 
    'WMSYS', 'XDB', 'XS$NULL', 'RDSADMIN')  
  AND NOT REGEXP_LIKE(TABLES.OWNER, '^APEX_[0-9]{6}$')
  AND NOT REGEXP_LIKE(TABLES.OWNER, '^FLOWS_[0-9]{5,6}$')
  AND REGEXP_LIKE(TABLES.OWNER, '${schemas}')
  AND TABLES.TABLE_NAME NOT LIKE 'BIN$%'
  AND NOT REGEXP_LIKE(TABLES.TABLE_NAME, '^(SYS_IOT|MDOS|MDRS|MDRT|MDOT|MDXT)_.*$')
UNION ALL
SELECT  
  NULL AS TABLE_CAT,
  MVIEWS.OWNER AS TABLE_SCHEM,
  MVIEWS.MVIEW_NAME AS TABLE_NAME,
  'MATERIALIZED VIEW' AS TABLE_TYPE,
  MVIEWS.COMMENTS AS REMARKS
FROM 
  DBA_MVIEW_COMMENTS MVIEWS
WHERE
  MVIEWS.OWNER NOT IN 
    ('ANONYMOUS', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 
    'EXFSYS', 'FLOWS_30000', 'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'IX', 'LBACSYS', 
    'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORACLE_OCM', 
    'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'OWBSYS', 'PM', 'SCOTT', 'SH', 
    'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 
    'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST', 
    'WMSYS', 'XDB', 'XS$NULL', 'RDSADMIN')  
  AND NOT REGEXP_LIKE(MVIEWS.OWNER, '^APEX_[0-9]{6}$')
  AND NOT REGEXP_LIKE(MVIEWS.OWNER, '^FLOWS_[0-9]{5,6}$')
  AND REGEXP_LIKE(MVIEWS.OWNER, '${schemas}')```
pungaa
  • 1
  • 1
  • 1
    It is not clear what you tried. Please follow guidelines on https://www.schemacrawler.com/consulting.html to provide more information. – Sualeh Fatehi Jul 06 '21 at 18:02
  • @SualehFatehi Sorry about that. I have updated it with details – pungaa Jul 07 '21 at 20:53
  • SchemaCrawler runs tests with Oracle during the CI build. (See https://github.com/schemacrawler/SchemaCrawler/blob/master/schemacrawler-oracle/src/test/resources/testOracleWithConnection.8.txt#L495-L509 and https://github.com/schemacrawler/SchemaCrawler/runs/3004753198?check_suite_focus=true), so this is unlikely to be a bug. Please follow guidelines on schemacrawler.com/consulting.html to provide more information, including complete and detailed logs. – Sualeh Fatehi Jul 08 '21 at 21:45

0 Answers0