Schema browser | Packages/Procedures/Functions tabs hang when Toad is connected to Oracle 12.1.0.2. (4232442)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

Schema browser | Packages/Procedures/Functions tabs hang when Toad is connected to Oracle 12.1.0.2.
Description

After upgrading the Oracle databases to 12.1.0.2, Toad schema browser hangs when trying to access packages, procedures, and functions. There is also some slowness when accessing other object tabs (i.e. tables) but it will return eventually.
When working with Oracle 12.1.0.1 and previous version this was not a problem. No changes were made to the database setup or user accounts.
Cause

Toad's back ground sql that runs is based off a schema's privilege. If the sys.users$ privilege is not granted, then Toad will default to querying the the USER/DBA/ALL views. (i.e. sys.DBA_OBJECTS). This may have slower performance in some environments leading to the hanging when accessing some objects tabs in the schema browser.
In Oracle 12.1.0.2, the SELECT privilege on SYS.USER$ must be explicitly granted to any user except SYS. Even SYSTEM does not have it by default.
Resolution

RESOLUTION 1:
Grant SELECT privilege on SYS.USER$ to all users execpt for SYS that is experiencing this issue.
RESOLUTION 2:
Remove any inefficient SQL execution plan on the database.

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title