Cannot get reverse engineering to work

Toad Data Modeler
1

Running TDM version 6.3.2.21. Full 32-bit Oracle 12c client. Try to reverse engineer an Oracle 12c database using the wizard (Create New Model from Database), but the moment I reach the last step (- Tables), the wizard does not display anything on the Tables tab, the cursor indicates processing, the Task Manager shows no CPU activity. Wait for 10+ minutes with no change, only choice is to kill TDM. Log shows:

Connecting to Oracle version: 12.2.0.1.0
Connected User: XXX
Missing access to system table V_$IM_COLUMN_LEVEL.

  • Data compression method of specific columns in INMEMORY tables will not be loaded.
2

Hi hharwood,

I ask you

  • What connection method do you use? Can you try another connection method?
  • RE wizard - do you have default settings?
  • Is it the first reverse engineering for this database? Do you have problem for another databases?
  • How approximately size is the databaze (how many objects?)

I can recommend TDM version 6.5.5 or latest beta

Best Regards,
Petr

3

Connection - used Native Client connection. Tried TCP but same result, which isn't surprising, since the Native Client must be getting connection details from tnsnames.ora, where I grabbed them to try TCP.
Settings -

  • Data Source: Oracle 12c Release 2
  • Reverse selection: Entities and Relationships only
  • Options: Everything unchecked except Check Constraints and Unique Constraints

First reverse engineering for this database, tried a different one, same result.

Database is smallish, only 18 tables. I exported a DD from Toad for Oracle and used it as a data source in TDM, loaded up after a few seconds, so don't think size is a factor.

Unfortunately, not sure what our licensing is, and if we can upgrade or not. Don't think I can do anything about that, but I'll check.

Is there any way to gather diagnostic info or some sort of log that might at least show at what point it hangs?

4

Hi hharwood,

thank you for the detailed informations.

I prepared changed system package for you, which add some debug informations to Message Explorer Log.
Click here for download this modified system package:
OREDBOR.txg

Steps to use it:

  1. Please exit Toad Data Modeler.
  2. Go to folder with system packages. System packages are located here by default (for 32bit version TDM 6.3):
    C:\Program Files (x86)\Quest Software\Toad Data Modeler 6.3\Packages\System
  3. Backup system package OREDBOR.txg from this path (to another folder on your computer).
  4. In system package replace original file with attached modified system package of the same name "OREDBOR.txg".
  5. Start TDM.
  6. Right click on Message Explorer Log and in context menu select "View Options".
    Go to "Message Saving" tab in dialog "Message Explorer Properties", check the option "Save Messages to File", and choose path to "MessageExplorer.log" in which log informations will be writed. OK.
  7. Run Reverse Engineering and after problem appeared go to "MessageExplorer.log", and send this file to us, please.
    Email: Radim.Tkacik@quest.com

Regards,

Mario

5

Log captured and emailed.

6

Can you please download another changed system package with debug informations?
Click here for download this modified system package:
OREDBOR12R2.txg

Please perform the same steps as above.

Thank you for your cooperation.

Mario

7

Sorry for the length of this. As you'll see in the log, the process hangs at the first sql statement that is gathering table names from sys.all_tab_privs. I tried running this query against the same schema and killed it after 20 minutes. I then tried a simple select against sys.all_tab_privs and got back all results in 1 second, so this pointed to an issue in the query. Was 99.9% sure it was the huge OR list of table_names, generally a list this long kills performance. To fix it, I altered the query to create a temp table of all of the table names and joined sys.all_tab_privs to this table, along with the other where clause criteria found in the original query. It is shown below, and it returned 46 rows in about 1 second. Can this query be updated without a product update?

SELECT privs.TABLE_NAME FROM SYS.ALL_TAB_PRIVS privs join

(select 'ALL_COL_COMMENTS' as table_name from dual

union

select 'ALL_CONS_COLUMNS' as table_name from dual

union

select 'ALL_CONSTRAINTS' as table_name from dual

union

select 'ALL_DIRECTORIES' as table_name from dual

union

select 'ALL_ENCRYPTED_COLUMNS' as table_name from dual

union

select 'ALL_EXTERNAL_LOCATIONS' as table_name from dual

union

select 'ALL_EXTERNAL_TABLES' as table_name from dual

union

select 'ALL_IND_COLUMNS' as table_name from dual

union

select 'ALL_IND_EXPRESSIONS' as table_name from dual

union

select 'ALL_INDEXES' as table_name from dual

union

select 'ALL_JOIN_IND_COLUMNS' as table_name from dual

union

select 'ALL_LOBS' as table_name from dual

union

select 'ALL_MVIEW_LOGS' as table_name from dual

union

select 'ALL_MVIEWS' as table_name from dual

union

select 'ALL_NESTED_TABLES' as table_name from dual

union

select 'ALL_REFS' as table_name from dual

union

select 'ALL_REGISTERED_MVIEWS' as table_name from dual

union

select 'ALL_SEQUENCES' as table_name from dual

union

select 'ALL_SNAPSHOTS' as table_name from dual

union

select 'ALL_SOURCE' as table_name from dual

union

select 'ALL_SYNONYMS' as table_name from dual

union

select 'ALL_TAB_COLUMNS' as table_name from dual

union

select 'ALL_TAB_COLS' as table_name from dual

union

select 'ALL_TAB_COMMENTS' as table_name from dual

union

select 'ALL_TAB_PRIVS' as table_name from dual

union

select 'ALL_TABLES' as table_name from dual

union

select 'ALL_TRIGGER_COLS' as table_name from dual

union

select 'ALL_TRIGGER_ORDERING' as table_name from dual

union

select 'ALL_TRIGGERS' as table_name from dual

union

select 'ALL_USERS' as table_name from dual

union

select 'ALL_VARRAYS' as table_name from dual

union

select 'ALL_VIEWS' as table_name from dual

union

select 'ALL_XML_TAB_COLS' as table_name from dual

union

select 'ALL_IND_PARTITIONS' as table_name from dual

union

select 'ALL_IND_SUBPARTITIONS' as table_name from dual

union

select 'ALL_LOB_PARTITIONS' as table_name from dual

union

select 'ALL_LOB_SUBPARTITIONS' as table_name from dual

union

select 'ALL_PART_INDEXES' as table_name from dual

union

select 'ALL_PART_KEY_COLUMNS' as table_name from dual

union

select 'ALL_PART_TABLES' as table_name from dual

union

select 'ALL_SUBPART_KEY_COLUMNS' as table_name from dual

union

select 'ALL_SUBPARTITION_TEMPLATES' as table_name from dual

union

select 'ALL_TAB_PARTITIONS' as table_name from dual

union

select 'ALL_TAB_SUBPARTITIONS' as table_name from dual

union

select 'ALL_EDITIONS' as table_name from dual

union

select 'ALL_ZONEMAPS' as table_name from dual

union

select 'V_$IM_COLUMN_LEVEL' as table_name from dual) tlist

on privs.table_name = tlist.table_name

WHERE privs.TABLE_SCHEMA='SYS' AND (privs.PRIVILEGE='SELECT' OR privs.PRIVILEGE='READ');

MessageExplorer.log (6 KB)

8

Thank you very much for cooperation.
Please try this newer package:
OREDBOR12R2.txg

In this query should be better Query. If RE will be OK, then I sent you repaired Package without log.

9

Curious to see what changed, I just compared the OREDBOR12R2.txg you just sent to the one you provided on last Saturday. There were exactly the same. I looked at the new one and found the part that builds the problematic query and it is still creating the very long OR section of the where clause (shown below). Did you accidentally send me the old version? Or do you want me to modify the file to build the query in the fashion I illustrated earlier?

Log.Information("LogCST03 - Before Query");

WhereTableList = "";

Query.SQL = "SELECT TABLE_NAME FROM SYS.ALL_TAB_PRIVS "

  • "WHERE TABLE_SCHEMA='SYS'";

for (i=0; i<arrSystemTables.length; i++)

{

if (WhereTableList != "")

WhereTableList += " OR ";

WhereTableList += "TABLE_NAME='"+arrSystemTables[i]+"'";

}

if (WhereTableList != "")

Query.SQL += " AND ("+WhereTableList+")";

Query.SQL += " AND (PRIVILEGE='SELECT' OR PRIVILEGE='READ')";

10

I appologize, my mistake.
I repair link to package in my last post above. You can try it.

11

Much better, RE ran to completion. Go ahead and finalize the change and send on the replacement file with logging removed. I assume I can share it with others here as a fix, following the same steps you provided?

12

Also, should I swap back in the original OREDBOR.txg file?

13

Swap back in the original system packages OREDBOR.txg and OREDBOR12R2.txg.

We prepared a user package (for use it take different steps - see below). Download file OREDBOR12R2_patch.txg. It is user package, which will modify problem code for Reverse Engineering of Oracle 12c Release 2.

How to install user package:

  • Run TDM and switch on expert mode (Main Menu > Settings > Options > General > Expert Mode checkbox)
  • In Main Menu should be appeared new menu item “Expert Mode”.
  • Go to “Main Menu > Expert Mode > Customization > Import Custom Package” and find and import user package “OREDBOR12R2_patch.txg”.
  • After this restart TDM.

In fact this process only copy package to location defined in TDM. By Default it is “C:\Users\USERNAMEXY\Documents\Toad Data Modeler\Standard Installation\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC}”.

Now you can try Reverse Engineering and share this user package with others.

For uninstall simply delete this user package in Documents path.

14

This second user package use your type of query using UNION - it is faster:
OREDBOR12R2_patch2.txg

If you want use it, then delete previous user package "OREDBOR12R2_patch.txg" from path “C:\Users\USERNAMEXY\Documents\Toad Data Modeler\Standard Installation\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC}”.

15

To verify, in order to implement the newer patch, I remove the GUID named folder from the first patch and then follow the same steps with the second patch file that you had me do with the first patch file? So I should end up with a new, different GUID named folder under C:\Users\USERNAMEXY\Documents\Toad Data Modeler\Standard Installation\Packages?

16

Nevermind, I moved the folder and then turned back on Expert Mode and see that it recreated the same folder (minus the patch file). I then imported the new patch and can see the OREDBOR12R2_patch2 file in the GUID folder. Reran the RE wizard to confirm that all still works. Thanks for the help with this, nice to be able to use this feature.

17

You are welcome.

We thanks for the tip to more efficient query.

Mario