I wonder, why SQL Nav 6.2 takes so much time to open a list of tables or views in other user’s schema? On our production our application runs against one schema and developers use another read-only schema to access data.
SQL Nav 5.5.4 takes 3-5 seconds to open a list of all schemas and about 5-10 seconds to open a list of 300 views in the production schema.
SQL Nav 6.2 takes 5-10 seconds to open a list of all schemas and 5+ minutes to open the same list of 300 views.
It would be much appreciated if we could provide more information so we can diagnose this issue. Were you using DB Explorer or SQL Navigator tree in 6.2 to view this list of tables? Please check if the option View > Preferences > Session > Enable using DBA dictionary views is ticked or unticked in both 5.5 and 6.2. Also check for this option on the Oracle logon page when you log into the DB.
Following with Gwen’s note, You can also use SQL Monitor to capture queries that executed by SQL Nav when expanding the view nodes. SQL Monitor can be found in the same All Programs folder of SQL Navigator.
If the DB Navigator tree can display Views quicker for you, we would suggest that you could use the DB Navigator tree for the time being until we could provide more updates for you. The DB Navigator is still available under View->DB Navigator or you can use the preference ‘Session->Show Code Editor after connection’, uncheck the option would give you the same workflow in 5.5 and will display the DB Navigator after a DB connection is established.
Our team will keep you posted once we have more info from you as per Gwen’s note.
Sorry for confusion, in 6.2 I used DB Explorer and in 5.5.4 I used DB Navigator.
As I see, both 5.5.4 and 6.2 versions had ‘Enable using DBA dictionary views’ turned off.
I tried turning it on in 6.2 and I was able to open the list of views in DB Explorer in under a minute or so. I tried DB Navigator in 6.2 and it is just as fast as in 5.5.4 version.
Thanks for your clarification. We’re planning to do a lot of work in these areas to streamline the workflow and improve the performance of DB Explorer so hopefully it will address the issues that you got. I will let you know when a beta version is ready so you can test it out. For the time being, please use the DB Nav tree to perform your tasks.
Here’s another major issue with DB Navigator in 6.2
I’m unable to open some package bodies in other schemas:
If I click on the package body in Other Schemas/EXPERT2/PKG_TRANSACTION
the focus moves DB Navigator to DB explorer, and an editor window opens with
PACKAGE pkg_transaction IS
END pkg_transaction;
Output log displays:
2:12:03 PM PACKAGE “EXPERT2”.“PKG_TRANSACTION” recreated
2:12:03 PM PACKAGE BODY “EXPERT2”.“PKG_TRANSACTION” not found
2:12:03 PM PACKAGE BODY “EXPERT2”.“PKG_TRANSACTION” not found
Tried to open another package in the same schema, the focus did not move to the explorer and the error log displayed
2:13:21 PM OCI-22303: type “EXPERT2”.“T_PVT” not found
I had to click on the DB Explorer to display it and it was able to show the package body somewhat correctly.
Also, the error and output log keep changing time output format (that’s a very old issue I reported few years ago)
2:15:13 PM OCI-22303: type “EXPERT2”.“T_PVT” not found
14:18:06 OCI-22303: type “EXPERT2”.“T_PVT” not found
Noticed the same behavior in while viewing query results.
I was able to open all packages using 5.5.4 DB Navigator without any issues.
Do you mind providing us with script to create the user that you were using? We need to know it’s permissions and roles to be able to replicate the issue.
CREATE USER expert2 IDENTIFIED BY expert2
DEFAULT TABLESPACE expert2_data
TEMPORARY TABLESPACE expert2_temp
/
CREATE USER expert2_read IDENTIFIED BY expert2_read
/
GRANT DEBUG CONNECT SESSION TO expert2, expert2_read,
/
GRANT SELECT ANY DICTIONARY TO expert2, expert2_read
/
GRANT CREATE SESSION TO expert2, expert2_read
/
expert2 is granted pretty much everything, expert2_read has only select on tables and views in expert2 schema.
I could reproduce it and raised it as an urgent bug. For the time being, a non-complete work around is granting execute and debug on the package to the read user.