Oracle Reverse Engineering

[jgarlick-adesa]

Reverse Engineering a schema in an Oracle database was throwing a bunch of Oracle errors like this:

Caused by: java.sql.SQLException: ORA-31603: object "SYS_IOT_OVER_91649" of type TABLE not found in schema "xxxxxxxx"

The errors appeared to be caused by Oracle not being able to reconstruct the structure of the object. It turns out there are a lot of them, so we looked at other objects that could cause potential problems. It turns out there are a lot. We fixed it by modifying the query that re-creates the DDL for the objects by Oracle. Our solution explicitly excludes certain things, but a more scalable solution might be to add command line options to add comma-separated values for excluding certain table name patterns and database object types.

Expected Behavior

Obevo should have not tried to create database structures like Database Links

Actual Behavior

Obevo tries to reconstruct system tables that shouldn't be included in our source.

Obevo Version where this issue was observed

7.0.1

Steps to reproduce the behavior

Run the migration in an Oracle 11g database with SYS, MLOG, RUPD tables or database links.

Snippet for fixing the issue

We were able to fix the errors and run the reveng job successfully after we added DATABASE LINKs to the objects not to reconstruct, as well as excluding tables named, 'SYS_%', 'MLOG%', and ' RUPD%'.

com.gs.obevo.db.impl.platforms.oracle.OracleReveng - Around Line 129

            MutableList<Map<String, Object>> maps = jdbc.queryForList(conn,
                    "SELECT CASE WHEN OBJECT_TYPE = 'TABLE' THEN 1 WHEN OBJECT_TYPE = 'INDEX' THEN 2 ELSE 3 END SORT_ORDER,\n" +
                            "    OBJECT_TYPE,\n" +
                            "    dbms_metadata.get_ddl(REPLACE(object_type,' ','_'), object_name, owner) || ';' AS object_ddl\n" +
                            "FROM DBA_OBJECTS WHERE OWNER = '" + args.getDbSchema() + "' AND OBJECT_TYPE NOT IN ('PACKAGE BODY', 'LOB','MATERIALIZED VIEW', 'TABLE PARTITION', 'DATABASE LINK')\n" +  <-- Added DATABASE LINK here
                            "      AND OBJECT_NAME NOT LIKE 'SYS_%' AND OBJECT_NAME NOT LIKE 'MLOG%' AND OBJECT_NAME NOT LIKE 'RUPD%'" +  <-- Added this exclusion line
                            "ORDER BY 1");

[shantstepanian]

Acknowledged. Agree that parameterization would be ideal, but I'd like to have the sensible default if possible.

I haven't used Oracle much - are these objects/object types sensible to exclude? e.g. are they user-created or auto-created by the system?

[shantstepanian]

I've committed the fix you've suggested, with a small tweak to add $ as a suffix to the object names for RUPD$ and MLOG$, as to make it more specific for internal tables. Those were the names I observed in Oracle 12; let me know if that fits for you

Materialized views aren't supported yet; you can track issue #186 for that

The commit also includes some initial testing setup for Oracle leveraging Docker containers. Unfortunately, the Docker container setup for Oracle isn't as easy as other DBMS platforms, so it still needs a couple steps for automation during the build. I've created a followup ticket #214 for that. If you're familiar w/ the Docker container setup for Oracle, it would be good to discuss (on the #214 ticket)