Start a new topic

Syonyms with Oracle

[This topic is migrated from our old forums. The original author name has been removed]

Why can't I see synonyms for a schema in dbvisualizer?  I can see them when I log into pl/sql developer, I can select * from them, I just can't see them in the Object view.  It's a huge pain.

Anyone else experience this?

[This reply is migrated from our old forums.]

Re: Syonyms with Oracle
Hi, I recommend that you register for an evaluation license and give it a try. http://www.minq.se/products/dbvis/eval Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Syonyms with Oracle
Hi Roger, I've installed the 6.5 version, however, it uses the generic objects and not the oracle.xml settings, that come with the Personal version. I'm trying to pursuade my manager to purchase the license upgrade.
[This reply is migrated from our old forums.]

Re: Syonyms with Oracle
Hi, I recommend that you give the latest 6.5 version a try since we don't support 4.3.6 anymore. Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Syonyms with Oracle
Roger, I'm having the same issue. However, I'm using version 4.3.6 Personal edition of the tool, and I'm working with a database that uses these DB Links to create synonyms, which I also cannot see. Can you create another version of the Oracle.xml file that is compatible with it? Thanks.
[This reply is migrated from our old forums.]

Re: Syonyms with Oracle
Jamie, The synonyms fix is now included in the 6.0.9 version. http://www.minq.se/products/dbvis Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Syonyms with Oracle
Fixed!!!
Thanks Roger!
[This reply is migrated from our old forums.]
[Attachment has been removed.]

Re: Syonyms with Oracle
Jamie, The problem is accessing objects via DB link synonyms. I have attached a new profile that may solve this. Just save it as: DBVIS-HOME/resources/profiles/oracle.xml Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Syonyms with Oracle
debug output:
[cleared]
07:07:45 [DEBUG Thread-18 L.execute] Executing...
07:07:45 [DEBUG Thread-18 L.setCurrentCatalog] DbConnection='Man Fin Recon' Catalog='null' Schema='null' NewCatalog='null'
07:07:45 [DEBUG Thread-18 C.invoke] RootConnection: OracleConnection.prepareStatement("select * from ZE_DATA.MAN_FINANCIAL_DAILY_SETTLEMENT")
07:07:45 [DEBUG Thread-18 L.prepareStatement] SQL: select * from ZE_DATA.MAN_FINANCIAL_DAILY_SETTLEMENT
07:07:45 [DEBUG Thread-18 C.invoke] RootConnection: OracleConnection.nativeSQL("select * from ZE_DATA.MAN_FINANCIAL_DAILY_SETTLEMENT")
07:07:45 [DEBUG Thread-18 B.invoke] RootConnection: OraclePreparedStatement.execute()
07:07:45 [DEBUG AWT-EventQueue-1 C.?] DbConnection='Man Fin Recon' Catalog='null' Schema='ZE_DATA' Table='MAN_FINANCIAL_DAILY_SETTLEMENT'

[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: Syonyms with Oracle
Here are the files you request
[This reply is migrated from our old forums.]

Re: Syonyms with Oracle
Jamie, Post a screenshot of the Synonyms listing and the one of the Data tab. You may also enable debug mode. This will then reveal what SQL DbVis execute to get the data for the Synonym. Read more in: http://www.minq.se/products/dbvis/faq.html#6.11 Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Syonyms with Oracle
Progress....
I can see the synonyms...but when I try to view the data, on the 'data' tab, I get:

An error occurred while performing the operation:
You probably don't have the required privileges to view this object

thoughts?
[This reply is migrated from our old forums.]
[Attachment has been removed.]

Re: Syonyms with Oracle
Jamie, Thanks! The result is somewhat strange since the synonym show in the all_synonyms system table but not in all_objects (which should keep information about all objects including synonyms). I have attached a modified oracle.xml file that hopefully solves this. Save it as: DBVIS-HOME\resources\profiles\oracle.xml Please let me know the result. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Syonyms with Oracle
First query: ... Physical database connection acquired for: Man Fin Recon 08:06:07 [SELECT - 0 row(s), 0.000 secs] Empty result set fetched ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.100/0.000 sec [0 successful, 1 warnings, 0 errors] Second query: Returns the synonym that I can 'select *' from Is this what you expected?
[This reply is migrated from our old forums.]

Re: Syonyms with Oracle
Jamie, This is the SQL DbVis execute to get information about all synonyms in a schema: 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" FROM all_synonyms s, all_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 You need to change $owner to the actual schema. I assume you get zero hits when executing this? What happen if you execute the following simplified SQL: SELECT * FROM all_synonyms WHERE owner = '$owner' Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: Syonyms with Oracle
you'll note in dbvis_issue.gif...the select is from the synonym and it's returning results but the syn doesn't show in the object view.