Duplicated synonyms for Oracle

In the "Synonyms" section of the objects tree for my Oracle 11.2 database, some entries seem to appear many (~30-50) times. There's always one (and only one) that works, which has the little grid icon next to it, but all the duplicates have no icon. If I select any of them then I get a pop-up dialogue with the following error: ------ An internal error occurred in: java.lang.IllegalArgumentException: Width (-1) and height (-1) cannot be = 0 The error may have affected the application state. Please notify vendor. Thread: AWT-EventQueue-1 Stack Trace: java.lang.IllegalArgumentException: Width (-1) and height (-1) cannot be

As a further update, they're listed as TABLEPARTITION in the synonyms object view as opposed to TABLE. This seems to cause the interface to fail, as per my previous post.

Hi Conan, > As a further update, they're listed as TABLEPARTITION in the synonyms object view as opposed to TABLE. This seems to cause the interface to fail, as per my previous post. I'm starting a separate thread for this to make it easier to track. Please enable debugging just before you expand the Synonyms node (or reload the Synonyms Objects tab) and post or email the output to us: http://www.dbvis.com/products/dbvis/doc/faq/#4.14 Best Regards, Hans Edited by: Hans Bergsten on May 5, 2011 5:44 PM

Thanks Hans! I turned on the debugger, but I don't actually get any debug output. When I select a synonym of type TABLE in the object view I get debug output saying it's selecting from all_tables, and getting all the columns that make up the Info tab that i'm looking at. When I select a synonym of type TABLEPARTITION however, the error window pops up with the same output as before, but nothing makes it to the debug window. I had both "Debug DbVisualizer" and "Debug JDBC Drivers" checked. It's quite possible that it's trying to retrieve some data that I'm not permissioned to see, as I don't have full access rights to the database, but that's just a guess.

Hi Conan, Thanks for your help with this. If you select the Synonyms node, you should see tow tabs in the Object ViewL Synonyms and Synonymed Objects. Turn on debugging and then click Reload in both of these tabs and you should get the SQL used to get the content for these tabs in the debug log. Best Regards, Hans

OK, I did as you suggested, which produced the debug output below. Just to be clear though, this is not where the error occurs - I get the error when selecting objects in the objects tree under Synonyms, not in the object view of the Synonyms node. ----- Synonyms reload debug output: 10:40:00 [DEBUG AWT-EventQueue-1 B.execute] CommandId: oracle.getSynonyms sql: SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", decode(s.db_link, null, replace(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", decode(s.db_link, null, s.table_owner, s.owner) "RefObjOwner", decode(s.db_link, null, s.table_name, s.synonym_name) "RefObjName" FROM ${#cp.SystemTablePrefix}_synonyms s, ${#cp.SystemTablePrefix}_objects o WHERE (s.owner = '${owner}') and s.table_name = o.object_name(+) and s.table_owner = o.owner(+) and o.object_type(+) not in ('PACKAGE BODY') order by 2 10:40:00 [DEBUG AWT-EventQueue-1 J.execute] Executing... 10:40:00 [DEBUG AWT-EventQueue-1 J.setCurrentCatalog] DbConnection='xxxxxxxxx' Catalog='null' Schema='null' NewCatalog='null' 10:40:00 [DEBUG pool-2-thread-122 C.?] RootConnection: T4CConnection.createStatement() 10:40:00 [DEBUG pool-2-thread-122 B.?] RootConnection: OracleStatementWrapper.execute("SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", decode(s.db_link, null, replace(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", decode(s.db_link, null, s.table_owner, s.owner) "RefObjOwner", decode(s.db_link, null, s.table_name, s.synonym_name) "RefObjName" FROM all_synonyms s, all_objects o WHERE (s.owner = 'xxxxxxxx') and s.table_name = o.object_name(+) and s.table_owner = o.owner(+) and o.object_type(+) not in ('PACKAGE BODY') order by 2") 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 1 (java.lang.String) "Synonym Owner" using getString() 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 2 (java.lang.String) "Synonym Name" using getString() 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 3 (java.lang.String) "Object Owner" using getString() 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 4 (java.lang.String) "Object Name" using getString() 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 5 (java.lang.String) "Type" using getString() 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 6 (java.lang.String) "DB Link" using getString() 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 7 (java.lang.String) "RefObjOwner" using getString() 10:40:06 [DEBUG AWT-EventQueue-1 E.?] getting column 8 (java.lang.String) "RefObjName" using getString() 10:40:43 [DEBUG AWT-EventQueue-1 B.execute] Total rows/columns: 3564/8. Rows after filtering: 3564 10:41:43 [DEBUG Thread-890 J.?] elementName: 'DataNode' label: '${oracle.getSynonyms.Synonym Name} (${oracle.getSynonyms.Object Owner}.${oracle.getSynonyms.Object Name})' type: '${oracle.getSynonyms.Type}' on-empty-output: 'stop' 10:41:43 [DEBUG Thread-890 A.?] Returning cloned generic Command for idref: oracle.getSynonyms 10:41:43 [DEBUG Thread-890 B.execute] CommandId: oracle.getSynonyms sql: SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", decode(s.db_link, null, replace(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", decode(s.db_link, null, s.table_owner, s.owner) "RefObjOwner", decode(s.db_link, null, s.table_name, s.synonym_name) "RefObjName" FROM ${#cp.SystemTablePrefix}_synonyms s, ${#cp.SystemTablePrefix}_objects o WHERE (s.owner = '${owner}') and s.table_name = o.object_name(+) and s.table_owner = o.owner(+) and o.object_type(+) not in ('PACKAGE BODY') order by 2 10:41:43 [DEBUG Thread-890 J.execute] Executing... 10:41:43 [DEBUG Thread-890 J.setCurrentCatalog] DbConnection='xxxxxxxxxxxx' Catalog='null' Schema='null' NewCatalog='null' 10:41:43 [DEBUG pool-2-thread-122 C.?] RootConnection: T4CConnection.createStatement() 10:41:43 [DEBUG pool-2-thread-122 B.?] RootConnection: OracleStatementWrapper.execute("SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", decode(s.db_link, null, replace(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", decode(s.db_link, null, s.table_owner, s.owner) "RefObjOwner", decode(s.db_link, null, s.table_name, s.synonym_name) "RefObjName" FROM all_synonyms s, all_objects o WHERE (s.owner = 'xxxxxxxxx') and s.table_name = o.object_name(+) and s.table_owner = o.owner(+) and o.object_type(+) not in ('PACKAGE BODY') order by 2") 10:41:50 [DEBUG Thread-890 E.?] getting column 1 (java.lang.String) "Synonym Owner" using getString() 10:41:50 [DEBUG Thread-890 E.?] getting column 2 (java.lang.String) "Synonym Name" using getString() 10:41:50 [DEBUG Thread-890 E.?] getting column 3 (java.lang.String) "Object Owner" using getString() 10:41:50 [DEBUG Thread-890 E.?] getting column 4 (java.lang.String) "Object Name" using getString() 10:41:50 [DEBUG Thread-890 E.?] getting column 5 (java.lang.String) "Type" using getString() 10:41:50 [DEBUG Thread-890 E.?] getting column 6 (java.lang.String) "DB Link" using getString() 10:41:50 [DEBUG Thread-890 E.?] getting column 7 (java.lang.String) "RefObjOwner" using getString() 10:41:50 [DEBUG Thread-890 E.?] getting column 8 (java.lang.String) "RefObjName" using getString() 10:42:27 [DEBUG Thread-890 B.execute] Total rows/columns: 3564/8. Rows after filtering: 3564 ----- Synonymed objects debug output (note this page is empty): 10:44:47 [DEBUG AWT-EventQueue-1 B.execute] CommandId: oracle.getSynonymedObjects sql: SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", decode(s.db_link, null, replace(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", decode(s.db_link, null, s.table_owner, s.owner) "RefObjOwner", decode(s.db_link, null, s.table_name, s.synonym_name) "RefObjName" FROM ${#cp.SystemTablePrefix}_synonyms s, ${#cp.SystemTablePrefix}_objects o WHERE (s.table_owner = '${owner}') and s.table_name = o.object_name(+) and s.table_owner = o.owner(+) and o.object_type(+) not in ('PACKAGE BODY') order by 2 10:44:47 [DEBUG AWT-EventQueue-1 J.execute] Executing... 10:44:47 [DEBUG AWT-EventQueue-1 J.setCurrentCatalog] DbConnection='xxxxxxxxxxxxx' Catalog='null' Schema='null' NewCatalog='null' 10:44:47 [DEBUG pool-2-thread-123 C.?] RootConnection: T4CConnection.createStatement() 10:44:47 [DEBUG pool-2-thread-123 B.?] RootConnection: OracleStatementWrapper.execute("SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", decode(s.db_link, null, replace(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", decode(s.db_link, null, s.table_owner, s.owner) "RefObjOwner", decode(s.db_link, null, s.table_name, s.synonym_name) "RefObjName" FROM all_synonyms s, all_objects o WHERE (s.table_owner = 'xxxxxxxxx') and s.table_name = o.object_name(+) and s.table_owner = o.owner(+) and o.object_type(+) not in ('PACKAGE BODY') order by 2") 10:44:50 [DEBUG AWT-EventQueue-1 B.execute] Total rows/columns: 0/8. Rows after filtering: 0

Hi Conan, Sorry about the late reply. I believe I know what's going on here, but I can't verify it because I don't have access to an Oracle Enterprise installation with Partitioning enabled. I hope you can help me verify my hunch, which is that the duplicates you see are in fact TABLE PARTITIONs, probably included in the query result when there is a synonym for partitioned tables. Please run these two queries and tell me what you see. This is the current one. I expect the result to include the duplicates: SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", DECODE(s.db_link, NULL, REPLACE(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", DECODE(s.db_link, NULL, s.table_owner, s.owner) "RefObjOwner", DECODE(s.db_link, NULL, s.table_name, s.synonym_name) "RefObjName" FROM all_synonyms s, all_objects o WHERE ( s.owner = '') AND s.table_name = o.object_name(+) AND s.table_owner = o.owner(+) AND o.object_type(+) NOT IN ('PACKAGE BODY') ORDER BY 2 This is a slightly modified query, and I hope it gets rid of the duplicates: SELECT s.owner "Synonym Owner", s.synonym_name "Synonym Name", s.table_owner "Object Owner", s.table_name "Object Name", DECODE(s.db_link, NULL, REPLACE(o.object_type, ' ', ''), 'TABLE') "Type", s.db_link "DB Link", DECODE(s.db_link, NULL, s.table_owner, s.owner) "RefObjOwner", DECODE(s.db_link, NULL, s.table_name, s.synonym_name) "RefObjName" FROM all_synonyms s, all_objects o WHERE ( s.owner = 'HANS') AND s.table_name = o.object_name(+) AND s.table_owner = o.owner(+) AND o.object_type(+) NOT IN ('PACKAGE BODY', 'TABLE PARTITION') ORDER BY 2 Please confirm my assumptions about the two queries, or tell me what you see instead. Best Regards, Hans

Hi Hans, Correct! The duplicates are TABLE PARTITIONS, and as you suspected the first query shows them (I get ~3500 rows), and adding the TABLE PARTITION exclusion in the second one removes them and cuts it down to ~1000 rows. The difference in the time it takes to execute them (or more accurately for my setup, to ship the results over the wire) would be noticeable when expanding the synonyms object in the objects tree or viewing it in the Object View tab. Thanks for looking into this. Conan

Hi Conan, Thanks for confirming. The same problem exists in the 7.1.5 version, so it will be fixed in both the next maintenance release and the next EA. Best Regards, Hans

That's odd, my current install of 7.1.5 doesn't behave that way - the TABLE PARTITION objects are not displayed in the objects tree. I'm using exactly the same driver file (Oracle Thin, ojdbc5.jar) in both installations. The whole objects tree looks very different in the two versions. I can send screenshots if it will help. Regards, Conan

Hi Conan, > The whole objects tree looks very different in the two versions. I can send screenshots if it will help. It sound as if you have not set Database Type for the connection to "Oracle" in 7.1.5. If that not the problem, please send/post a screenshot. Best Regards, Hans

Hi Hans, I've definitely got the database type set to Oracle in both instances, but my schema in the objects tree in 7.1.5 just contains SYNONYM, TABLE, VIEW and Procedures, whereas in 720.1638 it's got Tables, Views, Synonyms, Indexes, Sequences, Materialized Views, Functions, Procedures, Packages, Package Bodies, Java Sources, Java Classes, Triggers, Object Types, Object Type Bodies, Recycle Bin, Jobs, Scheduler, Database Links and Invalid Objects, and there's a new entry at the same level as my schema called Session Properties, and a new entry at the level above that called DBA Views. Conan

Here you go

Hi Conan, Thanks, but what we need to see is how the connection is configured, so please post/send a screenshot of the whole window with the Object View tab for the connection visible. Best Regards, Hans